GeoNames Forum

Hi

We are in the process of using the allCountries data (in a SQL database). As part of this process we are going through the process of extracting out the admin names to be displayed against each place that has people living there.

Now as i understand it if i start at a particular location then if the specific adminXcode is populated with a non 00 value then i should be able to lookup the name for that by querying the allCountries table looking for a feature code of admX with the appropriate adminXcode populated.

I started of by trying the location where i live (GeoId 2639925) and this has an admin2Code of C5 - but this gives me a place on a different continent when i query the allcountries table for admin level 2 and a code of C5

I have since tried a simple query against the UK looking for records whereby the admin2Code is populated with a non 00 value but doesn't have a corresponding record in the same table for admn2. THis returns 1000's of records. Query at bottom of post.

Am i doing something wrong or should i be taking a different approach?

Thank in advance

Jamie

select top 1000 * from allCountries ac
where ac.admin2code <> '' AND ac.admin2code <> '00' and NOT EXISTS (SELECT * FROM allCountries AC2 WHERE AC2.featurecode='ADM2' AND AC2.admin2code=ac.admin2code)
AND ac.countrycode='GB'

Hi

The codes are not global you will need to lookup the code the respective country.

C5 refers to the abolished county of Cheshire that has been split. The new code after the split has not yet been updated for all toponyms.

http://en.wikipedia.org/wiki/2009_structural_changes_to_local_government_in_England