11

Possible Duplicate:
Database of Countries and their Cities

Ok, this one is a non technical question. I am creating a business directory and I'd like to categorize listings by location. Instead of giving users an option to enter the location, I am going to provide them with a list of locations which follow a Country/State/City format. I tried GeoWorldMap by GeoBytes. It looked promising in the beginning and I felt that is exactly what would get the job done until I realized it provided a list of around 275 countries even though the number of countries that are recognized is between 192 to 195. So I am not sure about the extra 80 countries. When I realized this issue, I went to their website to do some reckon and found out that their list is updated on a regular basis. So this brings a question to my mind. Is it possible to find an accurate database that lists all possible locations? What do most developers here use for something like this?

Community
  • 1
  • 1
Naif Amoodi
  • 121
  • 1
  • 1
  • 3
  • 1
    I think most developers would allow the user to enter the location. Unless you're doing some sort of geocoding (where you're using that location do perform some kind of geographic algebra) and you need to associate metadata with a specific location (and the user can't or shouldn't provide it), you don't need to clutter your database with all of that data. Likewise, people are not going to want to scroll through an **enormous** list of locations when they could just type it in. – Adam Robinson Aug 01 '10 at 13:20
  • 2
    Scrolling won't be an issue because I'll be using AJAX. I won't be doing any geocoding but the listings are going to be categorized by location so association is required. And because proper association matters, the input provided by the user cannot be trusted :( I have in the past seen how users enter values pertaining to locations. Take countries for example. They could enter USA as US, USA, United States, United States of America or just America. Same for states. They would enter Georgia as Georgia or GA. Another issue would be spelling mistakes/typos and improper capitalization. – Naif Amoodi Aug 01 '10 at 13:32
  • Sounds like a question best asked on http://gis.stackexchange.com – fmark Aug 01 '10 at 13:45
  • 1
    http://stackoverflow.com/questions/8346520/database-of-all-countries-with-all-cities-in-respective-country – Somnath Muluk Jun 08 '12 at 09:51
  • Simple SQL for Country database: http://www.infomazing.net/blog/database-country-table-with-sql-insert-statements – Saqib Vaid Oct 26 '15 at 19:58
  • You can get the SQL Dump for country state and cities from http://sajeevsl.blogspot.in/2016/12/sql-database-for-country-state-cities.html – Chinjoo Dec 06 '16 at 07:31
  • For free list of Major Cities see: https://datahub.io/core/world-cities – AnthonyVO Mar 04 '20 at 18:40

1 Answers1

7

Excellent question. I think Yahoo!'s GeoPlanet can be very useful for this: they provide a pretty exhaustive list of places and the hierarchical relationships between them, and you can either download the list of names or use their API.

Other gazetteers (that's the term-of-art you're looking for) include GeoNames, which also has downloadable data free of charge. (I'm sure StackOverflow or even a Google search could get you more options along the same lines.)

Also, if you're planning to create a UI to let users select from your list of places, I like to suggest the Freebase Suggest plugin, a jQuery plugin that will force users to select from a certain category (in your case, countries, say) and provide a nice UI for choosing from what Freebase's semantic database considers correct. It even provides some wiki-like context to help with disambiguation.

npdoty
  • 4,729
  • 26
  • 25
  • Freebase Suggest is cool as all get-out, but it's a little tricky if you want to discern which regions are included in other regions for the purposes of search. E.g., if a user says they are in Boulder, then searches for people in Colorado, the USA, North America, or the world should include that user. I'm guessing Freebase has that data but you need to use their web services APIs to do that, and I suspect you couldn't do it efficiently without owning a local copy of the region hierarchy. – MikeJ Aug 17 '10 at 17:42
  • @MikeJ It definitely gets thornier the more complex your use case is. It is worth noting that the RDF that Freebase provides for locations does include location `containedBy` properties so it should be possible to traverse geographic hierarchies with an RDF parser (for Boulder, Freebase has Boulder County, Colorado and the United States listed as containers). Whether Freebase's location ontology is good enough for your purposes is another question; I think Yahoo! has done a good job with GeoPlanet on addressing these issues. – npdoty Aug 18 '10 at 07:04
  • 1
    Found your answer here (http://gis.stackexchange.com/questions/603/is-a-country-state-city-database-available), but searched this answer @ SO just to upvote! – Tiago Cardoso Apr 01 '11 at 13:08