GeoNames Home | Postal Codes | Download / Webservice | About 

GeoNames Forum
  [Search] Search   [Recent Topics] Recent Topics   [Groups] Back to home page 
[Register] Register / 
[Login] Login 
Correct SQL for associating admin data w/ geonames?  XML
Forum Index -> FAQ - frequently asked questions
Author Message
mhuggins



Joined: 20/06/2008 00:12:34
Messages: 18
Location: Downingtown, PA
Offline

tl;dr: I'm not sure I'm using the right SQL to associate geonames with admin1_codes, admin2_codes, etc. If someone can straighten me out with some working SQL, I'd greatly appreciate it.)

I feel like I'm not correctly understanding how to associate administrative subdivisions with geonames. It seems that each admin code has a geoname_id that correlates to a parent entry (such as a country), but that doesn't mean it's the only geoname that it is associated with.

For example, take Philadelphia, PA, USA. It shows adminCode1 "PA" and adminName1 "Pennsylvania". However, in the admin1_codes table, code "US.PA" is associated with the geoname for Pennsylvania, USA obviously.

Does this mean that when associating admin1_codes with geonames, I should associate them based upon the code itself (e.g. "US.PA")?

Code:
SELECT g.*, a1.* FROM geonames g
 LEFT OUTER JOIN admin1_codes a1 ON CONCAT(g.country_code, ".", SUBSTR(g.admin1_code, 1, 2)) = a1.code
 WHERE g.id = 4560349


This seems rather impractical, as a concatenation must be performed for every record that I want to retrieve. Furthermore, this manner of retrieval doesn't seem to be viable for admin2_codes.

A simple search in MySQL demonstrates that there are multiple results for many of the admin2_codes:

Code:
select code, count(code) from admin2_codes group by code having count(code) > 1;


Considering that there are many records for various admin2_codes (e.g.: GB.ENG.00, RU.73.Goro, BR.27.3556), then how do you know which one of these many codes to associate the geoname with? What should my SQL SELECT statement be to make sure I know I get the right code?

Sorry for this long-winded post, I'm really tired and need sleep. Please let me know if it doesn't make any sense.
[WWW]
marc



Joined: 08/12/2005 07:39:47
Messages: 4412
Offline

Matt

The sql join looks ok. What we do, however, it not to join it at every select. We load the names into a cache and translate it at run time.

As for adm2, I think you have cut some characters. The column is longer than 4 chars.

Best

Marc

[WWW]
mhuggins



Joined: 20/06/2008 00:12:34
Messages: 18
Location: Downingtown, PA
Offline

Thanks for the reply, marc. The local caching of admin codes sounds like it could be a promising solution.

With regards to multiple admin 2 codes, though, even if I look at the admin2 codes dump file, a quick search of 'GB.ENG.00' for example finds 2 results. You are correct, though, that I was truncating many of the admin2 codes to early, thanks for pointing that out!
[WWW]
mhuggins



Joined: 20/06/2008 00:12:34
Messages: 18
Location: Downingtown, PA
Offline

Just wanted to say thanks again for the cache suggestion. I took a couple hours and greatly improved my response time by using memcache to hold admin codes and country codes, which were previously part of LEFT JOIN's in my SELECT statement. I never used memcache before, but it was really easy to implement to take care of this!
[WWW]
 
Forum Index -> FAQ - frequently asked questions
Go to:   
Powered by JForum 2.1.5 © JForum Team