I have a table made of merged point datasets in PostGIS. I want to select all of the features where two or more points have identical geom values.
At present I just want to select so I can review the associated field values.
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It only takes a minute to sign up.
Sign up to join this communityI have a table made of merged point datasets in PostGIS. I want to select all of the features where two or more points have identical geom values.
At present I just want to select so I can review the associated field values.
Not all the details are clear, but my general approach would be along these lines:
SELECT *
FROM thetable AS a, thetable AS b
WHERE ST_Equals(a.geom, b.geom) AND a.id <> b.id
Something like this:
SELECT p1.id, p1.name, p1.<other attrib>, p2.id, p2.name, p2.<other attrib>
FROM points p1, points p2
WHERE ST_Intersects(p1.geom, p2.geom) AND
p1.id <> p2.id;
Some notes:
I think this answer can help to find points which overlapped with each other How to find overlapped points in postGIS