Author |
Message |
02/05/2006 00:22:58
|
Anonymous
|
Hi again,
just in case you're not aware of this... in the database dump (allCountries.zip), the admin1_code for the cities in the US is the ISO code (I think), but it's the FIPS code for the cities in all other countries.
On the other hand, I've seen in another message in this forum that you have the names of the administrative divisions of the countries (you gave the link to a page with the admin divisions of Norway). I was wondering if you also have a file (similar to allCountries.zip) with the administrative divisions of all countries. Is there such a file? May I download it?
Thanks again,
xavi
|
|
|
02/05/2006 07:23:11
|
marc
Joined: 08/12/2005 07:39:47
Messages: 4416
Offline
|
Hi xavi
It is planned to move from fips to isocodes. The challenge with this is that there is no 1<->1 relationship between fips and iso codes, it has to be sorted out country by country. We therefore don't have something like allCountries.zip with iso codes.
Any help with this is welcome ...
Marc
|
|
|
|
02/05/2006 21:15:15
|
Anonymous
|
Actually, I was asking for a file with all the region names, so that I could easily upload it to my database and then display the corresponding region name for a given city. As the cities file uses FIPS codes, the regions file should also use FIPS codes, so that I could match the cities info with the regions info.
In fact, I already have a file with all the regions, but I wondered if you had one that would probably match better with the geonames cities info.
Right now I don't have time for trying to come up with a solution to the FIPS - ISO migration, so I can't promise anything. Sorry about that.
|
|
|
02/05/2006 21:25:29
|
marc
Joined: 08/12/2005 07:39:47
Messages: 4416
Offline
|
You are right the file really should be part of the download. If you give me your email I can send you the file I am using, though it is not ready to be officially realeased.
Regards,
Marc
|
|
|
|
02/05/2006 22:22:11
|
Thomas
Joined: 29/04/2006 12:27:48
Messages: 3
Offline
|
Hi,
I've checked the administratives regions of US in the database and I've found only 46 states whereas there must be 50 in the union. Is it a bug or you don't have the data yet to add the 4 last states?
|
|
|
04/05/2006 07:11:14
|
marc
Joined: 08/12/2005 07:39:47
Messages: 4416
Offline
|
Hi Thomas
How did you check the administratives regions? All states are in the geoname database, so I wonder what makes you think some states are missing.
Marc
|
|
|
|
04/05/2006 09:53:09
|
Thomas
Joined: 29/04/2006 12:27:48
Messages: 3
Offline
|
I've check the administratibe regions with a query:
SELECT * FROM 'geomap' WHERE country_code='US' AND feature_code='ADM1'
the result give me only 46 rows and some states like Virginia are missed
I've tried to locate the Virginia State by another query:
SELECT * from 'geomap' WHERE country_code='US' AND name='Virginia'
But the result give me only PPL...
|
|
|
04/05/2006 19:18:15
|
marc
Joined: 08/12/2005 07:39:47
Messages: 4416
Offline
|
Now I understand. I was first thinking you wanted to say placenames were missing in these states.
The four states Massachusetts, Pennsylvania, Kentucky and Virginia are really missing in the gnis dataset.
I have added them and also updated the feature code of the four capitals :
http://www.geonames.org/recent-changes.html
|
|
|
|
04/05/2006 22:32:32
|
Anonymous
|
hi Marc,
after reading Thomas messages, now I realize that I can get all the regions by using something like...
SELECT * FROM geonames_places WHERE feature_code='ADM1'
Would this be the content of the file with all the regions that you said you would prepare? (by the way, I sent you a private message through this forum with my email address, not sure if you received it).
On the other hand, I think that now I understand better what you said the lack of 1-to-1 mapping between FIPS codes and ISO codes. For example, in Spain the FIPS codes correspond to the 17 regions of the country
select * from geonames_places where feature_code = 'ADM1' and country_code = 'ES';
But the ISO codes correspond to the 50 provinces of the country
http://www.geonames.org/ES/administrative-division-spain.html
Is this correct?
Thanks.
xavi
|
|
|
05/05/2006 19:22:18
|
marc
Joined: 08/12/2005 07:39:47
Messages: 4416
Offline
|
Hi xavi
And with
SELECT * FROM geonames_places WHERE feature_code='ADM2' ;
you should get all provinces and with 'ADM3' all 8000+ municipalities ...
As you can see there are only 43 provinces and 1 municipalities in the geonames database. Please feel free to add the missing provinces. (I don't think it makes sense to manually add the 8000 missing municipalities
The file I was referrering to is now available in the download section : http://download.geonames.org/export/dump/admin1Codes.txt
It contains the admin1codes and the name for each code.
For Spain the fips to iso code mapping is 1-1 for most regions and provinces with exception of Ceuta and Melilla. I have update the page with the fips codes for the provinces, and fixed a lot of wrong iso codes on this page :
http://www.geonames.org/ES/administrative-division-spain.html
Marc
|
|
|
|
06/05/2006 12:44:01
|
Anonymous
|
marc wrote:
The file I was referrering to is now available in the download section : http://download.geonames.org/export/dump/admin1Codes.txt
It contains the admin1codes and the name for each code.
Could you provide the ANSI name of the regions like you do for the cities?
xavi
|
|
|
06/05/2006 15:34:41
|
marc
Joined: 08/12/2005 07:39:47
Messages: 4416
Offline
|
Hi xavi
I don't have an ansi file ready, but you can easily generate one for you.
SELECT countrycode,admincode1, ansiname FROM geonames WHERE feature_code='ADM1'
Hope this helps
Marc
|
|
|
|
06/05/2006 17:25:03
|
Anonymous
|
well, for some reason the names returned by the SQL don't match the names in the admin1Codes.txt . For example, in Spain, the SQL returns what seem to be like longer versions of the region names
Comunidad Autonoma de Cataluna
while the corresponding entry in admin1Codes.txt is
Cataluña
The same happens in Philippines for example. While SQL returns
Province of Zambales
the entry in admin1Codes.txt is
Zambales
(I prefer the shorter names).
Anyway, thank you.
xavi
|
|
|
|