All geek questions in one place

SQL Server Distance Finder

I have a Microsoft SQL Server database with a location table. Each location has its own address, latitude and longitude coordinates.

In my application, the user can enter zipcode, and we will return the list of contacts by location. This is my approach. a) Using the zipcode DB database, I look through lat, lon for zipcode (this is the center point). b) I run a search like this

SELECT Position_ID, distance(pos_lon,pos_lat,zip_lon,zip_lat) dist FROM Positions ORDER BY dist

"distance" is a function that calculates the distance between two points.

The problem is that as my database grows, the launch time for these searches starts to grow.

Is there a better approach?

+4
source share
3 answers

If you are using SQL Server 2008, you probably want to examine the geography column type, the STDistance function STDistance and spatial indexes.

+5
source

I would do the calculation of the field surrounding your zip code at a specified distance to get the lat / lon values ​​for the four corners. Then make a simple comparison of your position values ​​to select those that fall inside the square. This way you do not need to calculate the distance from your zip code to every point of your db for each search.

+1
source

What version of SQL server? If in 2008 look at the spatial type.

http://www.microsoft.com/sqlserver/2008/en/us/spatial-data.aspx

EDIT: Also limiting this query to where, is likely to help, since you probably won't want to go far in any particular direction.

0
source

Source: https://habr.com/ru/post/1301616/

More articles:


All Articles