geolocation - How to get areas/regions for a country and cities for this area/region? -

geolocation - How to get areas/regions for a country and cities for this area/region? -

we made simple query gets cities:

select * `allcountries` name='moscow' , `country_code` = 'ru'

here result of query - http://i.stack.imgur.com/vjvrq.png (we not have 10 reputation post image).

for example, city result 4-7 rows.

how areas/regions country , cities area/region?

p.s.: please careful. not interested in api site , database fetch. thanks!

background

in geonames have feature_classes , feature_codes discriminate location type. can find detailed description of code in geonames website. in snapshot, p.pplc means "city (populated place) capital of political entity" , s.hlc means "building (spot) hotel".

also, every geoname have properties identify location in "hierarchy" within country; properties country_code, admin1_code, admin2_code, admin3_code, admin4_code. note not properties used every given geoname, since depends on political organization of country.

find city within administrative level

to find city within area (i.e. administrative level), must first search geoname admin level, in order have admin codes useful filter city query.

to find admin level, must first execute query like:

select * `allcountries` `country_code` = 'ru' , `feature_class`='a' , `feature_code`='adm1'

note query filter out first admin levels (feature_code='adm1'), can find admin level of depth changing to:

select * `geonames` `country_code` = 'ru' , `feature_class`='a' , `feature_code` 'adm_'

now, select 1 record result set , search cities, using "hierarchy" codes of level. should utilize (mutatis mutandis):

select * `geonames` `country_code` = "ru" , `feature_class`='p' , `feature_code` 'ppl%' , `admin1_code`="<admin1>" , `admin2_code`="<admin2>" , `admin3_code`="<admin3>" , `admin4_code`="<admin4>"

beware of null admin codes, need strip out sql (the whole "and ..." clause).

of course, can original "moscow" search within filtered set.

geolocation geocoding geo geonames