Delete duplicate spatial point/points in mssql from a perticular location

Question

I am working with a Spatial data which has a street intersection points.... I get the intersection points from spatial query on street/roads datain MMSQL . but in my case some streets/roads are multi lane.. which gives me duplicate intersection points for same streets/roads for each lane about 3-5 meters away from each other.. now i have the point data with Duplicate intersection points with a geometry differing in about 3-5 meters.. Now i want to delete the duplicate intersection points and keep only one intersection point.. i tried taking buffer of a point n den deleting the points which intersects with this buffer.. the the query is very slow and takes ages to complete.. i tried using spatial indexes but it hardly helps...

is there a algorithm or any method in QGIS ARCGIS OR POSTGIS Database which i can use to delete theses duplicate points.. or any other method?????

Answer 1

Don't take buffers. You can identify all your "near to each other" points like this (if you have a spatial index on geom, it'll run quite fast):

SELECT a.id FROM pts a, pts b
WHERE ST_DWithin(a.geom, b.geom, 3)
AND a.id != b.id;

Be careful that you don't delete legitimate crossings that also happen to be close to each other. Your 3-5m rule might not be unique to duplicate crossings. Maybe only delete crossings that also share a name (assuming that when you generate crossings you attribute them with the names of the crossing streets).

SELECT a.id FROM pts a, pts b
WHERE ST_DWithin(a.geom, b.geom, 3)
AND a.id != b.id
AND (a.name1 = b.name1 OR a.name2 = b.name2);