Getting city data from geonames.org dump files

Question

I exported the US.zip, allcountries.zip and the cities1000.zip file from geonames.org with the intention of using it for a list of cities, states and longitude/latitude info in the US. I noticed after looking through the data that there is a whole lot more than just cities and states. Ex. Here is one row

7534318              The San Francisco School              The San Francisco School              NULL     37.73275               -122.41167         S             SCH        US          NULL     CA          075        NULL     NULL     0             10               19           America/Los_Angeles      NULL

This contains other data about some data point in San Francisco, CA for a school. Although this is nice it seems that if all I want is city/state/country/lat/long data then it will be very hard to filter this data for just what I’m looking for.

My question. If all I want is a list of cities in the US with their state and long/lat information is there an easy way to get it from http://download.geonames.org/export/dump/ data export dump files? It doesn't seem like there is one specific column that has city names?

Thank you for any help!

Answer 1

the main allcountries.zip, XX.zip contains all city, county, state, country location in addition to all land features, POI etc.

If you just want city, county (region), state, lat, lng, you can just use the postalcode zips. http://www.geonames.org/export/zip

If you want to use allCountries.zip, you can filter your query to include a specific feature type, listed here: http://www.geonames.org/export/codes.html#P.PPL

SELECT * FROM yourtable WHERE feature_code = "PPL";