4

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 – Somanyquestions Sep 24 '13 at 21:30
  • 2
    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 '13 at 21:45
3

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 ) – simplexio Sep 25 '13 at 8:34
  • 1
    I used STDistance in the end, as it did seem a more efficient way to achieve the same process. – Somanyquestions Oct 8 '14 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 '18 at 17:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.