Author |
Message |
19/07/2008 09:28:14
|
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.
|
|
|
20/07/2008 08:41:45
|
marc
Joined: 08/12/2005 07:39:47
Messages: 4416
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
|
|
|
|
20/07/2008 18:23:25
|
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!
|
|
|
20/07/2008 20:10:14
|
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!
|
|
|
|
|
|