4

I have encountered a bit of a tough problem. I need to select all duplicates in a SQL spatial table based on geometry (some has been copy and pasting with out realising they are pasting the same geometry over and over).

I need to select all duplicates except the lowest ID where the geometry is the same.

halfer
  • 19,824
  • 17
  • 99
  • 186
Trent Thompson
  • 75
  • 1
  • 3

5 Answers5

3

You can join your table with itself and check with STEquals function.

SELECT tbl1.OBJECTID, tbl2.OBJECTID
FROM your_table tbl1
JOIN yout_table tbl2 ON tbl1.your_geometry_field.STEquals(tbl2.your_geometry_field) = 1
WHERE tbl1.OBJECTID <> tbl2.OBJECTID
ORDER BY tbl1.OBJECTID
digz6666
  • 1,798
  • 1
  • 27
  • 37
0

This gives you all duplicates.

WITH C AS(
    SELECT  ROW_NUMBER() OVER (PARTITION BY CAST(your_geometry_field AS VARBINARY(MAX)) 
                               ORDER BY CAST(your_geometry_field AS VARBINARY(MAX)) AS Rn
    FROM your_table
)
SELECT * FROM C WHERE Rn != 1
sqluser
  • 5,502
  • 7
  • 36
  • 50
  • If you provide the question with table definition and sample data, I will update the answer with detailes – sqluser Apr 01 '15 at 04:41
  • Great, I will give that ago.. and I will send a sample tomorrow :D – Trent Thompson Apr 01 '15 at 06:07
  • This can't work because geometry is not comparable in sql server. – andyb Mar 02 '16 at 15:21
  • You are right @andyb. How about casting it to `VARBINARY(MAX)` – sqluser Mar 04 '16 at 02:42
  • 1
    That sounds like a pretty good idea @sqluser.When I approach this, things tend to get complicated quite quickly with CTEs and STEquals() all over the place. I've just tested on a couple of points and casting to varbinary seems to work quite nicely. However, STEquals returns true when geometries are equal in the geometric sense (they cover the same area say) which may not be binary equals. But definitely an interesting idea. – andyb Mar 08 '16 at 09:37
0

For part one of your question: On a table named "TableName" with a spatial point column named "CenterPoint" The following groups the centerpoints by location, if the count is greater than 1 then there are duplicates:

    SELECT ID, COUNT(CenterPoint)
    FROM TableName
    GROUP BY CenterPoint
    HAVING COUNT(CenterPoint) > 1
Tipsy
  • 1
  • 1
0

I used these methods recently

SELECT DISTINCT(T1.Id)
FROM table T1, table T2
WHERE T1.Shape.STEquals(T2.Shape) > 0
AND T1.Id <> T2.Id
ORDER BY Id

This way, you can use the spatial relationships to find duplicates.

Another way to do this is with the typical sql GROUP BY using the text of the geometry

SELECT Id, Shape.STAsText(), COUNT(*) Duplicates
FROM table
GROUP BY Id, Shape.STAsText()
HAVING COUNT(*) > 1
Steve
  • 25,806
  • 2
  • 33
  • 43
0

Please try the following snippet

SELECT tbl1.id, tbl2.id
FROM HEXA_GRID_SP tbl1,
HEXA_GRID_SP tbl2 
where SDO_EQUAL(tbl1.geometry,tbl2.geometry) = 'TRUE';
pissall
  • 7,109
  • 2
  • 25
  • 45
Alexandre Barboza
  • 7
  • 1