I'm also interested in this one. So far I got:
Code:
-- Get all countries
SELECT iso_alpha2 AS id, name
FROM countryinfo;
-- Get all states/province by country. Ex: AR
SELECT g.geonameid AS id, g.name, g.admin1 as code
FROM geoname g
WHERE g.fcode = "ADM1"
AND g.country = ?
ORDER BY g.name ASC;
-- Get all counties by state/province ID. Ex: 3844419
SELECT g.geonameid AS id, g.name
FROM geoname g
JOIN hierarchy h ON h.childId = g.geonameid AND h.parentId = ? AND h.type = "ADM"
ORDER BY g.name ASC;
It seems the hierarchy for geonames is:
Continent -> Country -> State/Province -> County -> City
But it's a common pattern on webapps to just select:
Country->State->City
I've tried many approaches into getting cities by state/province with no success yet.
Any suggestions?
Thanks a lot