GeoNames Forum

Hello,
I'm trying to create a database table which has a countryid column, a parentcountryid column, and a countryname column. The counties will all have their parent Id set to null (or set to a single entry for 'UK'), and all the cities have their parent Ids pointing to their respective counties.

Can anyone help in using the geonames lists to make this table? The web service looks like it almost works, as you can drill down from country into county:
http://ws.geonames.org/children?geonameId=6269131
However, trying to get to cities just leaves odd regions instead, e.g. drilling down into staffordshire gives:
http://ws.geonames.org/children?geonameId=2637141
Which doesn't include the cities, and the granularity seems to stop there.

I note the GB download list does have all the cities listed, but does not seem to link them to a county. Can anyone help?

Crundy

The county information is in the adm2 column of the dump. For missing county links you are welcome to help add the information.

Best

Marc

OK, where does the adm2 column map to? Is there a specific list of counties with this column?

It maps to ADM2 records with the same adm2 value. The table is self referencing.

OK, I'm having trouble creating the table. I want to first import the counties and then import the cities. I'm getting primary key contraint violations on the city import. I have imported the GB list into a table called GB and this is the script I'm using "sql srver 2005":

-- counties:
insert into
dbo.tblLocation
select
geonameid,
NULL,
[name]
FROM
GB
WHERE
featureclass='A' and featurecode='ADM2' and cc2 is not null order by [name]

--cities:
insert into
dbo.tblLocation
SELECT
ci.geonameid,
co.geonameid,
ci.[name]
FROM
GB AS co
INNER JOIN
GB AS ci
ON co.admin2=ci.admin2
AND co.featureclass='A' and co.featurecode='ADM2'
AND ci.featureclass <> 'A' and ci.featurecode <> 'ADM2' and ci.cc2 is not null
ORDER BY
ci.[name]

Any ideas? I must be missing a column restriction somewhere.

OK, got it now. I've got a SQL script to create and populate the table. Is it OK to redistribute it? If so then I'll post it here for anyone else who wants it.