Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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) ;
share|improve this question
up vote 6 down vote accepted

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 ;
share|improve this answer
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 at 13:57
    
You are right, I've modified the suggested query. – wiltomap Jan 19 at 14:00
    
Saves me posting exactly that code then ;-) – mlinth Jan 19 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 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 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
share|improve this answer
    
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 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 at 19:10

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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