# Find points that are unique between two tables

I have been trying to work out a query that returns the geometry of points that are unique (eg. not supliacte). Is there a simple way to perform it in just one query? `geom` is the only common attribute between the tables. My tables are rather big, about 50000 rows in each.

``````SELECT point1.geom, point2.geom
FROM schema.point1 AS fkb2012 , schema.point2
WHERE ST_Equals(point1.geom, point2.geom) ;
``````

I would join both tables in one and then keep only unique rows. If executing your query within PostGIS:

``````WITH temptable AS (
SELECT geom FROM schema.point1
UNION ALL
SELECT geom FROM schema.point2
)

SELECT
geom,
count(*)
FROM temptable
GROUP BY geom
HAVING count(*) = 1 ;
``````
• Um, won't your CTE throw out any unique points? I.e. if a point is in point1 but not point2, the ST_equals will get rid of it... – mlinth Jan 19 '16 at 13:57
• You are right, I've modified the suggested query. – wiltomap Jan 19 '16 at 14:00
• Saves me posting exactly that code then ;-) – mlinth Jan 19 '16 at 14:02
• It's important to realize that any use of `UNION ALL`, `GROUP BY`, or the `=` operator works by bounding box equality only. PostGIS bounding boxes are single-precision floating point, so nearby points will be considered equal. For example: `WITH test AS (SELECT unnest(ARRAY[ST_MakePoint(0,1.000001), ST_MakePoint(0,1.000002)]) AS geom) SELECT geom, count(*) FROM test GROUP BY geom` returns only a single row. – dbaston Jan 19 '16 at 14:44
• I have quite few points that are close (down to cm) sometimes these points return only as a single row as you said correctly. It results in a discrepancy of about 600 points which is OK in my case, but might be something to look up for to others. Cheers – geogrow Jan 20 '16 at 11:56

I think this will work, it might (or might not) be quicker than the union query way. Make sure you have indexes on your geom columns!

``````SELECT COALESCE(a.geom,b.geom) AS geom
FROM
schema.point1 a
FULL OUTER JOIN
schema.point2 b
ON a.geom = b.geom
WHERE a.geom IS NULL or b.geom IS NULL
``````
• I tried the query, it gave me an error COALESCE could not convert type point1 to point 2. Might have something to do with the lack of Z value perhaps as mentioned in my other comment. Will have a look and try sort it out. Thanks! – geogrow Jan 19 '16 at 14:30
• I'd guess that that would be the reason. I don't know if you can cast one to another. – mlinth Jan 19 '16 at 19:10