GeoNames Home | Postal Codes | Download / Webservice | About 

GeoNames Forum
  [Search] Search   [Recent Topics] Recent Topics   [Groups] Back to home page 
[Register] Register / 
[Login] Login 
How to list all cities in a state  XML
Forum Index -> FAQ - frequently asked questions
Author Message
samirn



Joined: 27/08/2013 20:09:35
Messages: 1
Offline

Dear Marc,

I imported the GeoNames into SQL Server and i need to list all countries, all states in a country and all cities in a state:

List all countries:
select * from GeoNames2 where feature_code = 'PCLI'

List all states in US
select * from GeoNames2 where feature_code = 'ADM1' and country_code = 'US'

How can I list all cities in a state? please I need it as an SQL statement

Thank you,
fedeisas



Joined: 01/09/2013 02:11:49
Messages: 1
Offline

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
daliaessam



Joined: 27/01/2014 00:20:00
Messages: 2
Offline

Why this question is never answered?

I've been searching for days on how to get cities for a specific state with no success.

Can one please answer this question.
 
Forum Index -> FAQ - frequently asked questions
Go to:   
Powered by JForum 2.1.5 © JForum Team