0

I want to try and do a geocoding application and access data stored in a SQLite database. A postcode, town or city will be supplied, and I want to return the long/lat. I want it to be screaming fast but I'm unsure about how to organise the data in the DB.

Here is the data im working with:

Town, City, Postcode, Country, Longitude, Latitude

This may be simple but im new to this, I would benefit from some advice.

J.Zil
  • 2,397
  • 7
  • 44
  • 78
  • 1
    How many times do you need to ask this? http://stackoverflow.com/questions/11722423/geocode-lookup-in-c http://stackoverflow.com/questions/11728290/searching-a-file-and-returning-value-super-fast – hatchet - done with SOverflow Jul 30 '12 at 20:07

1 Answers1

0

People will mis-spell all of the things that you propose using as your search criteria. So, do not use strings for Town, City, Postcode, and Country in this database table.

A good first step would be to use an Id, with the Id looked up in a separate table (e.g. the Towns table). A user could then lookup Latitude and Longitude using a combination of Id's. If one or more Ids are omitted, multiple results could be returned. For example, if they only supply the Id for the Postcode and Country, that will match multiple Cities/Towns.

A more efficient method of looking up specifically Latitude and Longitude would be to assign a specific place Id to each entry in the table that holds your Latitude and Longitude

PlaceId  Latitude  Longitude

Allow the user to lookup the PlaceId separately (by using a combination of Town, City, Postcode, Country and pick from among multiple return values to get a specific Place). Use that PlaceId to lookup Latitude and Longitude.

Eric J.
  • 147,927
  • 63
  • 340
  • 553