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

Question

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:

• Returns all points within 5km of the point.

• OR if it is simpler, all points within a 5km-sided square on the map.

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.

Answer 1

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.