CODEWITHSUNDEEP
sqlsql-serverindexinglatitude-longitudegeo
Luke Vo
Luke Vo

Reputation: 20788

Fast/Simple/Indexable way to extract points on maps within a radius or range from database

Sorry for the ambiguous title. This is not a duplicate from MySQL - Find points​ within radius from database . Here is the detailed problem:

I have a location database with indexed latitude and longitude values. I can freely add index and indexed computed column if needed.

I will be given a coordinates point and a distance (5km for example), I need:

The requirement is for the database lookup operation to be as simple as it can. The precision is not at top priority. The result may be refined later at client-side, so it is preferably to return more result from server if needed.

The database I am using is SQL Server if it matters.

EDIT: Sorry I forgot another problem when posting the 1st post. I have another similar question:

Same rule applied, either circular or squared shape, now each target point has its own radius too. Let's say, point A has Lat, Lng and Radius properties and so on for B and C. Now I need to extract the points that has its shape collides with the source shape.

Upvotes: 1

Views: 161

Answers (1)

Vladimir Baranov
Vladimir Baranov

Reputation: 32703

SQL Server has a feature called spatial indexes.

Geography Methods Supported by Spatial Indexes

Under certain conditions, spatial indexes support the following set-oriented geography methods: STIntersects(),STEquals(), and STDistance(). To be supported by a spatial index, these methods must be used within the WHERE clause of a query, and they must occur within a predicate of the following general form:

geography1.method_name(geography2) comparison_operator valid_number

To return a non-null result, geography1 and geography2 must have the same Spatial Reference Identifier (SRID). Otherwise, the method returns NULL.

Spatial indexes support the following predicate forms:

geography1.STIntersects(geography2) = 1
geography1.STEquals(geography2) = 1
geography1.STDistance(geography2) < number
geography1.STDistance(geography2) <= number

Your geography objects can be simple points and it may be enough, since you said that you don't need high precision, especially if the radius of a point is much smaller than 5km distance.

You can also approximate your circles with few points, for example, octagons may be a good enough approximation.

Have a look at available spatial types and methods, try several approaches and test their performance.

You may also find methods STBuffer and/or BufferWithTolerance useful to build circles around your points.

Upvotes: 1

Related Questions