2

I've downloaded the geonames data dump

The main table has admin3 and admin4 columns but there is no data dump which corresponds to the data in these columns.

Does any one know how to map the admin3 and admin4 codes with their respective place names?

LukStorms
  • 28,916
  • 5
  • 31
  • 45
adam78
  • 9,668
  • 24
  • 96
  • 207
  • According what's explained [here](https://www.logilab.org/10074668), it's in the XX files themselves. It's at a lower level than admin1 and admin2, so to keep reference files for those would probably be a bit pointless I'm guessing. – LukStorms Jan 20 '19 at 22:19
  • 1
    @LukStorms no that's incorrect. I think I've figured it out, looks like main table is self referencing - the parent child relationship for each admin code can be identified by using a combination of feature code AND the admin level. I'll update the post with an answer to explain it better. The docs do not explain it well. – adam78 Jan 21 '19 at 14:52

1 Answers1

1

Documentation on the primary website does not seem to explain this but here you go:

select  t4.name as town,
        t3.name as county, 
        t2.name as district, 
        t1.name as admin1, 
        t0.name, 
        t0.feature_class, 
        t0.feature_code,
        t0.country_code,
        t0.admin1,
        t0.admin2,
        t0.admin3,
        t0.admin4,
        t0.population
from geoname t0
left join geoname t1 on t1.admin1 = t0.admin1 and t1.feature_code = 'ADM1'
left join geoname t2 on t2.admin2 = t0.admin2 and t2.feature_code = 'ADM2'
left join geoname t3 on t3.admin3 = t0.admin3 and t3.feature_code = 'ADM3'
left join geoname t4 on t4.admin4 = t0.admin4 and t4.feature_code = 'ADM4'
where t0.name = 'London'
adam78
  • 9,668
  • 24
  • 96
  • 207
  • The answer is helpful, but not entirely correct - when joining, you must also restrict the join ON country_code matching, and also on t2 to t4 you must also join the higher level admin code otherwise you may have multiple results in the join. – Peter Jun 15 '20 at 15:17
  • @Peter please can you provide your SQL as I don't understand your answer. Country code would only be required if you download a data dump for all countries. In my case I only have a dump for United Kingdom. – adam78 Jun 17 '20 at 08:09
  • you are less likely to have clashes in that case if you are limited to a single country - the problem is that the same admin code may be used in multiple countries to mean different areas. It would have been better if the data referenced the geoname ID of the parent rather than the admin codes. – Peter Jun 17 '20 at 14:19