How to import data from geonames.org?

Question

PHP

Hello, there is geodata distilled into mysql from here download.geonames.org/export/dump/
The task is to build a hierarchy of the form Country->Region->Locality
I googled, read the FAQ, and on the geonames itself, however, I can’t find anything.
For example, there is such a village:
api.geonames.org/hierarchyJSON?geonameId=553915&username=user1&lang=ru

I really want to have the desired hierarchy, however, the hierarchy table simply does not have the necessary geoname_id (553915) and understand which administrative district this settlement belongs to - I can't.

Can, who faced? Can you tell me what to do?

Answer 1

With that base, it’s impossible to make a hierarchy for the whole world normally, I spent a lot of time on this. As a result, it turned out more or less normal for the USA, and since the whole world was needed, I was guided in the hierarchy by the feature_code field (geonames provides a list of possible options). It turned out, but not exactly what was expected
1. The choice of countries is clear
2. The choice of regions using hierarchy $country_geoname_id
SELECT * FROM geonames AS g INNER JOIN hierarchy AS gh ON h.childid = g.geonameid AND h.parentid ='".$ country_geoname_id."'
3. Selecting cities - look at cities and towns, navigate by state
3.1 select a region in $state
3.2 SELECT * FROM geonames WHERE country_code='".$state['country_code']."' AND admin1_code='".$state['admin1_code']."' AND feature_code IN ('ADM1', 'ADM2', ' ADM3', 'ADMD', 'PPLA', 'PPLC', 'PPL')
4. Selection of landmarks and districts of the city (works fine for the USA, where it is doubtful in the rest of the world)
4.1 select the city in $city
SELECT * FROM geonames WHERE country_code ='".$city['country_code']."' AND admin1_code='".$city['admin1_code']."' AND admin2_code='".$city['admin2_code']."' AND feature_code NOT IN ('ADM1', 'ADM2', 'ADM3') AND feature_code IN ('ADM4', 'ADM5', 'PPLA', ' PPLC', 'PPL')