19

I wonder what the precision of the SELECT DISTINCT operator is on a PostGIS geometry. On my system, the following query gives me a count of 5, which means that the inserted points are considered equal if they differ by less than 1e-5 and I am not sure if that is a feature of PostGIS, a problem of my installation or a bug.

Does anyone know if that is the expected behavior?

CREATE TEMP TABLE test (geom geometry);
INSERT INTO test
    VALUES 
        (St_GeomFromText('POINT (0.1 0.1)')),
        (St_GeomFromText('POINT (0.001 0.001)')),
        (St_GeomFromText('POINT (0.0001 0.0001)')),
        (St_GeomFromText('POINT (0.00001 0.00001)')),
        (St_GeomFromText('POINT (0.000001 0.000001)')),
        (St_GeomFromText('POINT (0.0000001 0.0000001)')),
        (St_GeomFromText('POINT (0.00000001 0.00000001)')),
        (St_GeomFromText('POINT (0.000000001 0.000000001)'));

SELECT COUNT(*) FROM (SELECT DISTINCT geom FROM test) AS test;

 count 
-------
     5
(1 row)

I am using:

$ psql --version
psql (PostgreSQL) 9.3.1

and

SELECT PostGIS_full_version();
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
POSTGIS="2.1.1 r12113" GEOS="3.4.2-CAPI-1.8.2 r3921" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.10.1, released 2013/08/26" LIBXML="2.7.3" LIBJSON="UNKNOWN" RASTER

on OSX 10.9

|improve this question
18

I'm surprised it's quite so coarse, but there it is. It's not DISTINCT, per se, it's the '=' operator, which is defined for geometry as 'equality of the index keys' which means practically 'equality of the 32-bit bounding boxes'.

You can see the same effect just using '=' directly,

select 'POINT (0.000000001 0.000000001)'::geometry = 'POINT (0.000000001 0.000001)'::geometry;

select 'POINT (0.000000001 0.000000001)'::geometry = 'POINT (0.000000001 0.00001)'::geometry;

Making '=' behave "intuitively" would unfortunately involve either a huge computational loss (doing explicit ST_Equals() evaluation for the operator call) or some substantial new complicated code (storing hash values for larger geometries, doing exact tests on the fly for smaller ones, choosing the right code path on the fly, etc)

And of course now lots of applications / users have internalized the existing behaviour, such as it is, so "improving" it would be a downgrade for many folks. You can do an "exact" distinct by calculating your set on ST_AsBinary(geom) instead, which will do exact equality testing on the bytea outputs.

|improve this answer
  • And can we assume ST_AsBinary(geom) is a relatively very fast operation? – Martin F Dec 20 '13 at 18:46
  • Thanks for your answer, this explains the behavior well. I am actually working on a geodjango project, so I will use the __equals filter there, which translates into the ST_Equals function I think. – yellowcap Dec 20 '13 at 18:52
  • 1
    Yes ST_AsBinary is fast. Equality tests on bytea probably involve memcmp, which is a very fast op, so should not be too terrible. – Paul Ramsey Dec 20 '13 at 19:04
  • What are you proposing here, @PaulRamsey? SELECT DISTINCT ST_AsBinary(geom)? That gives a binary representation of geom as the result. You could do SELECT MAX(geom) FROM the_table GROUP BY ST_AsBinary(geom); (I think an aggregate function like MAX() is required in the SELECT because the GROUP BY clause is using the ST_AsBinary() function return, not the field itself.) Does that look good? – Martin Burch Dec 16 '14 at 0:00
7

Given Paul Ramsey's excellent explanation of why the next question is what can be done about it. How do you SELECT DISTINCT on geometry fields and have it perform as expected?

In Paul's answer, I proposed using SELECT MAX(geom) FROM the_table GROUP BY ST_AsBinary(geom); but MAX() is slow, apparently requiring a table scan.

Instead, I found this to be faster:

SELECT DISTINCT ON (ST_AsBinary(geom)) geom FROM the_table;
|improve this answer
4

Just an update, for PostGIS 2.4, SELECT DISTINCT works correctly for the points data in the OP:

CREATE TEMP TABLE test (geom geometry);
CREATE TABLE
user=> INSERT INTO test
user->     VALUES 
user->         (St_GeomFromText('POINT (0.1 0.1)')),
user->         (St_GeomFromText('POINT (0.001 0.001)')),
user->         (St_GeomFromText('POINT (0.0001 0.0001)')),
user->         (St_GeomFromText('POINT (0.00001 0.00001)')),
user->         (St_GeomFromText('POINT (0.000001 0.000001)')),
user->         (St_GeomFromText('POINT (0.0000001 0.0000001)')),
user->         (St_GeomFromText('POINT (0.00000001 0.00000001)')),
user->         (St_GeomFromText('POINT (0.000000001 0.000000001)'));
INSERT 0 8
user=> 
user=> SELECT COUNT(*) FROM (SELECT DISTINCT geom FROM test) AS test;
 count 
-------
     8
(1 row)

And

user=> select 'POINT (0.000000001 0.000000001)'::geometry = 'POINT (0.000000001 0.000001)'::geometry;
 ?column? 
----------
 f
(1 row)

user=> 
user=> select 'POINT (0.000000001 0.000000001)'::geometry = 'POINT (0.000000001 0.00001)'::geometry;
 ?column? 
----------
 f
(1 row)
|improve this answer

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.