1

I have a database with 10,000+ addresses that I want to sort by distance from a single lat/lon.

The addresses are sometimes full street addresses, and other times just zipcode, and sometimes city/state.

I don't know if the google geolocation api will work do to the large number of addresses, but if this does work, please let me know.

Another possibility would be to fetch the lat/lon for each address before I add it to the database, and can use the SQL Geography data type. This would allow sorting by distance (I think). Are there are free services that allow fetching lat/lon for addresses and allow storing that information in a database? I know googles terms of service disallow this use (storing of information retrieved from its services).

Are there any other ideas to be able to sort this list?

B.McCarthy
  • 123
  • 1
  • 1
  • 13
  • https://developers.google.com/maps/documentation/geocoding/ but you can only use it if you plan to show stuff on a Google map and there is also a 2500 requests per day limit unless you pay for a business API key. – user1378730 May 07 '13 at 04:00

1 Answers1

0

Here is a good link for how to calculate distance based on Lat and Longitude: Calculating Distance between two Latitude and Longitude GeoCoordinates

This link would return the distance in a double which you could then sort. I think geocoder may be what you are looking for: http://geocoder.us/

EDIT: You can use the above link to to query geocoder to get the latitude and longitude for each address then use the above stackoverflow post to get and idea of how to calculate distant and then sort based on the list of doubles. For instance, if you have already precomputed the lat./lon. of each address in your db then as a new address comes in use the server to get lat/long then write a stored procedure to calculate the distance and returned the rows in descending order.

Community
  • 1
  • 1
Matt Johnson
  • 1,913
  • 16
  • 23
  • Question does not ask how to calculate a distance. It asks how to get a lat lon from an address. – paparazzo May 07 '13 at 02:23
  • sorry I was confused by this comment "I have a database with 10,000+ addresses that I want to sort by distance "from" a single lat/lon" The only questions are: Are there are free services that allow fetching lat/lon for addresses and allow storing that information in a database? Are there any other ideas to be able to sort this list? but he didnt specify what you mean by "this list". – Matt Johnson May 07 '13 at 03:20
  • 1
    Sorry if it was unclear. The list I was referring to was the database table that has addresses in it (Each row represents an address). I think the geocoder.us site you referenced may work, but I am still looking at the site. I think the solution I am going to try and implement is add a geography sql server 2008 datatype column to this table and when I insert those addresses, I'll call the geocoder.us site to get the coordinates and insert that location into my database table. – B.McCarthy May 07 '13 at 03:42