I have a parcel layer that in some cases has multiple stacked coincident parcels on top of each other. I'm trying to identify the parcels that are duplicated like that using Postgre 9.5.1/PostGIS 2.2.

What PostGIS functions would one use to determine this?

My first thought is to create a CTE and compare the two using ST_Equals, but that is returning a very large result.

There are about 2.6 million parcels in the DB.

Here's kind of what I was thinking:

with first as (select gid, geom from testing.parcel_selection)

select st_equals(a.geom,b.geom) from first a, 
testing.parcel_selection b;
  • Please edit the question to contain your query. Exact row counts on the query source and resilt will help. – Vince Jun 8 '17 at 13:17
  • 2
    Yes, of course this query returns many rows -- you have not eliminated identities or duplicates (e.g. WHERE b.idcol > a.idcol) – Vince Jun 8 '17 at 13:34
  • I believe that if you do it this way, the spatial index will not be used. You would be better off just doing a straight join on ST_Equals(a.geom, b.geom) and as Vince has said, add b.idcol>a.idcol also, as this will halve the comparisons. It always helps to should the EXPLAIN statament too. – John Powell Jun 8 '17 at 14:16
  • This may not help but to find overlapping coincident BUP (building unit parcels) in ArcGis I did polygon to point (centroid) then intersect the centroids and summary statistics... anywhere the count of parcel FID was greater than 1 indicated a duplicate. – Michael Stimson Jun 8 '17 at 20:57
  • 1
    I don't see how this question can be off-topic after providing code in the first edit. It's clearly not general programming as spatial relationship is key. – tinlyx Jun 8 '17 at 23:40

The solution was just to group by the geom column

  • You may have to test for false positives, since I think that may be a simple envelope test. – Vince Jun 9 '17 at 4:05

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