Post reply

optimising table for distance queries

  • April 23, 2014 at 3:21 am

    #396648

    Hi

    I have a table which has a couple of million records in it which relate to locations around the world.

    I want to be able to do a search on the table based on the distance from my current location so I've created a function that takes in my latitude and longitude and the latitude and longitude from the database record and returns a distance. I can then use this figure to perform a distance search.

    I assume though that in order for the query to work it will have to convert every lat, long record in the database to check whether it falls within my distance search.

    My question is, is there a better way to do this so that every record does not have to be converted?

    Let me know if I've not been clear in my question

    Thanks

    Matt

  • April 23, 2014 at 3:52 am

    #1708016

    Matt-1034261 (4/23/2014)

    Hi

    I have a table which has a couple of million records in it which relate to locations around the world.

    I want to be able to do a search on the table based on the distance from my current location so I've created a function that takes in my latitude and longitude and the latitude and longitude from the database record and returns a distance. I can then use this figure to perform a distance search.

    I assume though that in order for the query to work it will have to convert every lat, long record in the database to check whether it falls within my distance search.

    My question is, is there a better way to do this so that every record does not have to be converted?

    Let me know if I've not been clear in my question

    Thanks

    Matt

    If you are using SQL Server 2005, you are pretty much out of luck, on 2008 and later you could use Spatial data type and Spatial functions.

    😎

  • April 23, 2014 at 3:55 am

    #1708020

    I am using SQL 2005 🙁

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply