How find Nearest points using STBuffer and STContains in SQLServer using given point from point table?

Question

This is the Query I am using now..

 SELECT top 1 coloumname FROM tablename WHERE ROUND
    ((geometry::Point(74.8427116215336,12.852857058953,4326).STDistance(the_geom))*111.325,2) <2
    ORDER BY ROUND((geometry::Point(74.8427116215336,12.852857058953,4326).STDistance(the_geom))*111.325,2)

How can I change this to using STBuffer?

Answer 1

To find the points distance (buffer) of another point there are two methods that could work:

(note my data is stored in Colorado State Plane (feet) EPSG:2877 so I can use 5280 as the buffer distance)

Use STIntersects:

select 
points2.*
from points1 as p1
, points2 as p2
where p2.shape.STIntersects(p1.shape.STBuffer(5280)) = 1

or STWithin:

select 
points2.*
from points1 as p1
, points2 as p2
where p2.shape.STWithin(p1.shape.STBuffer(5280)) = 1

(In my example both methods returned ~2800 points in 4 seconds)

You'll have to create your geometry in sub queries in order to run the SQL above.

And the spatial methods reference can be found here: https://technet.microsoft.com/en-us/library/bb933960.aspx