GeoNames Home | Postal Codes | Download / Webservice | About 

GeoNames Forum
  [Search] Search   [Recent Topics] Recent Topics   [Groups] Back to home page 
[Register] Register / 
[Login] Login 
Simple listing needed of country, state, county, and city  XML
Forum Index -> General
Author Message
Styler001



Joined: 20/01/2008 20:44:10
Messages: 8
Location: WA State, USA
Offline

Well, I've looked through the first half of the topics here before my time is running short (and it figures my answer would probably be in the last half of topics if I actually had time to get through them all), so I apologize if this has already been asked and answered.

I need a file that I can import into MySQL, and the only information I need for each record is country, state/province, county/municipality, and city. I've looked at the files available for download here and none of them seem to fit the bill (at least not that I recognized).

Is there a file here that includes that information? Tab or CSV delimited would be preferable. If there is more info in the file, I can always import it into Excel or some other program and weed out what I don't need. Or, can someone tell me the steps to go through to generate this file? At the least (at this time), a file for the United States is what I really need. But other countries will eventually follow.

Thanks in advance for your help.
marc



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

I think the cities file could be what you are after :

cities1000.zip : all cities with a population > 1000 (ca 80.000)
cities5000.zip : all cities with a population > 5000 (ca 40.000)
cities15000.zip : all cities with a population > 15000 (ca 20.000)

You find them in the dump directory :
http://download.geonames.org/export/dump/

Cheers

Marc

[WWW]
Styler001



Joined: 20/01/2008 20:44:10
Messages: 8
Location: WA State, USA
Offline

Thanks, marc.

Looking at one of those files and finding a city in my area that I know what to look for, those files have everything I need except the counties. I'm sure I could find a counties file somewhere, but I have no way of knowing what cities and states belong to what county (outside of my own area).
marc



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

Hi Styler

The county information is in the admin2Code column. And the names of the counties for each code are in the admin2Code.txt files.

Cheers

Marc

[WWW]
Styler001



Joined: 20/01/2008 20:44:10
Messages: 8
Location: WA State, USA
Offline

Thanks again, marc.

I hadn't thought of looking for a county as a code. In that case, it looks like everything is there in those four files.

Just curious now. Is there some way of pulling out just the data I need from some main database? The way my MySQL tables are that I'm using, I'm going to have to come up with a way to convert the county code numbers into the actual county names, because I use the county names in my tables.
marc



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

there is a thread about how to load all tables in a mysql db :
http://forum.geonames.org/gforum/posts/list/732.page

In the same thread there is also a link to an access db dump.

Cheers

Marc

[WWW]
Styler001



Joined: 20/01/2008 20:44:10
Messages: 8
Location: WA State, USA
Offline

Downloading the Access database now. I think that'll be easier for me to work with and pare down. Thanks again for your help.
Styler001



Joined: 20/01/2008 20:44:10
Messages: 8
Location: WA State, USA
Offline

Well, I'm not so sure if these listings are going to work for what I need them for. When I use a query to pare this down to just what should be a city listing, I notice that it is including alot more results than it should.

In the county I live in, there are only about 15-20 towns/cities. However, it's pulling out ALMOST 90 results for the county. And I'm selecting only records equalling "P" in the fclass field and only equalling "PPL" in the fcode field.

How can I go about getting only "real" cities and towns, or is that even possible with the databases here?
marc



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

Hi Styler

What do you consider a 'real' city? The data for the US is from the US Geological Survey.

Cheers

Marc

[WWW]
Styler001



Joined: 20/01/2008 20:44:10
Messages: 8
Location: WA State, USA
Offline

Well, as an example, one of the cities in my county is Bellingham. But, GeoNames has included Edgemoor, Chuckanut, Chuckanut Village, South Bellingham, Sudden Valley, Silver Beach, Marietta (and probably others that I didn't recognize) that are "areas" of Bellingham. They are not separate cities.

There's another GeoNames "city" that is nothing more than a gas station/laudromat with some houses nearby. It is definitely not a city or town, and it is not listed in Whatcom County's records as such.

Another is Nooksack Salmon Hatchery, which is exactly that. A hatchery. Nothing more.
Flash



Joined: 28/01/2008 01:08:31
Messages: 1
Location: Abbotsford, BC, Canada
Offline

Unfortunately Marc is right, it seems the US federal government considers each of these a "populated place". You can see the hatchery's entry at http://geonames.usgs.gov/pls/gnispublic/f?p=115:2:4847083669296934174::NO:::. A google serch for "Nooksack Salmon Hatchery" will also find many websites that link to it as a town, each of these sites obviously are just pulling from a similar database. Nooksack Salmon Hatchery also appears as a town on mapping tools such as Google Earth.

I haven't download all the data to check, but could you eliminate this issue by not including cities1000.zip? I'm not sure if it would work, as when I looked at the US Cenus Bureau, they do not recognize the hatchery (of course!), so the question becomes do these "populated places" that do not appear in a census end up with an accurate population?
[WWW]
Styler001



Joined: 20/01/2008 20:44:10
Messages: 8
Location: WA State, USA
Offline

Flash, I'm not sure that elminating the cities1000 file would work. I need to include all cities and towns, no matter what their population, providing they really are cities and towns as the counties recognize them. I'm just wondering if something like Yahoo's listings might be more accurate. Although, if I couldn't find a compiled listing like I can find here, I'd need to put in the time myself to put it together (which is not a problem).

I guess it just goes to show that the U.S. gov't doesn't really know all that it thinks it does (like that's a surprise to anyone).
Styler001



Joined: 20/01/2008 20:44:10
Messages: 8
Location: WA State, USA
Offline

Well, looking around for a listing that seemed legitimate turned up no results that I was comfortable with. GeoNames seemed to include far too many locations that weren't even towns or cities.

So, I've gone through Yahoo and Wikipedia to put together something I feel better about using. I'm sure it's not as accurate as it could be, since both of those sites also include extra listings, but they don't seem to include as many as GeoNames.

If anyone is interested in the file, I've saved it to a CSV-delimited file you can download here:

http://rapidshare.com/files/92792252/United_States.zip

If you notice a county that needs some "fixing up", please e-mail me, and be sure to include the source of your findings. You can e-mail me at styler001@yahoo.com.
 
Forum Index -> General
Go to:   
Powered by JForum 2.1.5 © JForum Team