3

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.

I need the opposite return to this query: 

SELECT point1.geom, point2.geom
FROM schema.point1 AS fkb2012 , schema.point2
WHERE ST_Equals(point1.geom, point2.geom) ;
Improve this question

2 Answers 2

Reset to default
6

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 ;
Improve this answer
6
  • 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, 2016 at 13:57
  • You are right, I've modified the suggested query.
    – wiltomap
    Jan 19, 2016 at 14:00
  • Saves me posting exactly that code then ;-)
    – mlinth
    Jan 19, 2016 at 14:02
  • 1
    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, 2016 at 14:44
  • 1
    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, 2016 at 11:56
0

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
Improve this answer
2
  • 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, 2016 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, 2016 at 19:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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