3

I've been getting some funny behaviour from UNION vs. UNION ALL when combining two tables in PostGIS, so just wanted to check:

Does UNION include a check for duplicate geometries when determining rows to return, or does it only check other (non-geometric) fields i.e. might UNION (rather than UNION ALL) fail to return a distinct geometry from the second table whose non-geometric fields were identical to a row in the first table?

I checked various bits of PostGIS documentation online but couldn't find the answer. It doesn't help that all searches end up dominated by ST_Union!

1
  • 1
    Please add an example of your SQL query. Is your question actually something about "How does UNION in PostgreSQL deal with BLOB fields?"
    – user30184
    Jun 1 at 9:17
4

UNION does; row equality is checked on all returned columns and their types (using a type resolution sequence).


A thing to consider:

The implicit DISTINCT set operator here can be seen as partially enforced using the = operator defined for the given types, and for PostGIS GEOMETRY/GEOGRAPHY types this means bitwise equality - as opposed to spatial equality, i.e. 'LINESTRING(0 0, 1 1)' and 'LINESTRING(1 1, 0 0)' are spatially equal (as per ST_Equals), but not binary equal (as per =):

SELECT ST_Equals('LINESTRING(0 0, 1 1)'::GEOMETRY, 'LINESTRING(1 1, 0 0)'::GEOMETRY) AS "ST_Equals",
       'LINESTRING(0 0, 1 1)'::GEOMETRY = 'LINESTRING(1 1, 0 0)'::GEOMETRY AS "="
;

 ST_Equals | = 
-----------+---
 t         | f
(1 row)

The important part is that no defined SRID is encoded as 0 in the binary representation of a GEOMETRY/GEOGRAPHY type, so

SELECT 'SRID=4326;POINT(0 0)'::GEOMETRY AS geom
UNION
SELECT 'POINT(0 0)'::GEOMETRY AS geom
;

                        geom                        
----------------------------------------------------
 0101000020E610000000000000000000000000000000000000
 010100000000000000000000000000000000000000
(2 rows)

is considered not binary equal (as seen in the returned sequence).

In contrast, the actual geometry type is always encoded as part of the serialization process, so

SELECT 'POINT(0 0)'::GEOMETRY(POINT) AS geom
UNION
SELECT 'POINT(0 0)'::GEOMETRY(GEOMETRY) AS geom
;

                    geom                    
--------------------------------------------
 010100000000000000000000000000000000000000
(1 row)

is considered binary equal (this example is not 100% correct, as the GEOMETRY typemod does actually nothing to the internal byte sequence, but it gets the idea across).

2
  • Note that you also may find floating point discrepancies for the same computed geometry between different systems/OS.
    – geozelot
    Jun 1 at 9:46
  • Thank you very much @geozelot. If I could give this 10 votes, I would!
    – MDB
    Jun 1 at 11:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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