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


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);
  • very elegant query – Barrett Jan 9 '14 at 19:56
  • Thanks Paul Ramsey, I will try this approach as of nw i was using Point1.STBuffer(2).STIntersects(Point2) and it was taking long time to execute – Yogendra Jan 14 '14 at 5:16
  • HI Paul Ramsey i think the query in which you use the function ST_DWithin(a.geom, b.geom, 3) works with PostGIS... i tried this query but there is no such method (ST_DWithin) available for geography type in MSSQL. – Yogendra Jan 14 '14 at 5:41
  • the equivalent way to get a similar output to what the ST_DWithin(point1, point2, 3) would give would be point1.STDistance(point2) < d where d is the distance in this case d=3 – Yogendra Jan 14 '14 at 6:04

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.