# SQL to get points within a certain distance

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

• I am using sql server 2008 r2 Sep 24, 2013 at 21:30
• Welcome to GIS SE! Please use the edit button to revise your question with this additional information rather than requiring potential answerers to also read all the comments which are there more for seeking clarifications from the asker.
– PolyGeo
Sep 24, 2013 at 21:45

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

• Also for some reason my MS SQL was very slow when i tested above queries with STBuffer. Does anyone else have same problem ? STDistance was faster with 6 seconds time vs 1 minute and counting (and got out of memory few times. I used lines table with 12 lines in it (longest line was something like 1 km ) Sep 25, 2013 at 8:34
• I used STDistance in the end, as it did seem a more efficient way to achieve the same process. Oct 8, 2014 at 11:51
• The last method does not seem to produce expected results. I have taken is as shown above 'SELECT count(polys.FEATURE_ID) AS cntd , homes.FEATURE_ID from housesample as homes, GB_NHSCCG as polys where homes.geogtab.STDistance(polys.geogtab) < 1 GROUP BY homes.FEATURE_ID' but the result produces the same count wether the distance is 1, 1000 or 0.00000001. Is this expected?
– AK9
Jan 2, 2018 at 17:28

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...