SQL to get points within a certain distance

Question

I am new to using spatial data types in sql server. I have managed to create a column of Approx 50,0000 geometry points (from x,y data) and would like to find out which (count number of) points are within a radius of 50m from another point.

I have read a ton of blog posts and advice pages and think that I need to do this by creating a polygon layer of circles of radius 50m from each point, create a spatial index on the polygon layer and then do a count of all points in each polygon. The problem is that I can't work out how to this using my point data to create the new polygon layer.

The columns of data I have are 'ref','x', 'y', 'geompointloc'

I would also also be interested to know if this is the most efficient way to complete this task Thanks

Answer 1

See fastest solution at end.

This should work like you have thought it, but it is very slow at my MS SQL server

SELECT count(p.gid) as num, l.gid from lines as l, lines as p  WHERE l.geom.STBuffer(50).STIntersects(p.geom) = 1 group by l.gid

Not 100% how you should write "GROUP BY" to get correct answer , but that could do it, replace lines with your own point table and geom with your geometry column name. I could not test code because my SQL server kills query with "Out Of Memory" exception.

SELECT p.ref as ref,  p.geom.STBuffer(10) as geom INTO polygon_table FROM geomtable as p

Above query works in my MS SQL instance and creates polygon table with 10 meters from lines ( i don't have points in my test db at moment)

SELECT * FROM x ,y where x.geom.STIntersects(y.geom) = 1

Works too. There may be smarter way to do it . Which could using STDistance instead of STBuffer. Something like

SELECT count(l.gid) , l.gid from lines as l, lines as p where l.geom.STDistance(p.geom) < 50 GROUP BY l.gid

Managed to test it and it work a lot faster than first STBuffer query

Answer 2

MS SQL Server is desperately missing efficient way to do such basic spatial task (and many others) even though it is slowly getting better. As mentioned in other answer you can use:

1. STDistance
2. STBuffer with STIntersects (this will never give you exact results)

If you are checking points vs points, definitely first option. If you are checking anything else (lines, polygons) both methods are terribly inefficient - STBuffer is actually really bad, as it is far from exact (see MS SQL BufferWithTolerance to learn more). With millions features and up totally unusable.

For serious work use something else like PostgreSQL (PostGIS) with ST_DWithin function. It is extremely faster because it implements bounding box comparison etc., huge benefit especially for more complex geometries. You also need to have build spatial indexes on your geometries when doing such tasks.

If you have other than points features and need to stay in MS SQL you can try to replicate the PostGIS process at least the way you first check only bounding boxes, and only if the distance is smaller...