Jaka jest dokładność SELECT DISTINCT w kolumnie geometrii PostGIS?

19

Zastanawiam się, jaka jest precyzja SELECT DISTINCToperatora w geometrii PostGIS. W moim systemie następujące zapytanie daje mi liczbę 5, co oznacza, że wstawione punkty są uważane za równe, jeśli różnią się o mniej niż 1e-5 i nie jestem pewien, czy jest to cecha PostGIS, problem z moją instalacją lub błąd.

Czy ktoś wie, czy takie jest oczekiwane zachowanie?

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)

Ja używam:

$ psql --version
psql (PostgreSQL) 9.3.1

i

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

w OSX 10.9

— żółta czapka
źródło

18

Dziwię się, że jest tak grubiański, ale tak jest. Nie jest to DISTINCT, samo w sobie, to operator „=”, który jest zdefiniowany dla geometrii jako „równość klawiszy indeksu”, co oznacza praktycznie „równość 32-bitowych ramek ograniczających”.

Możesz zobaczyć ten sam efekt, używając bezpośrednio „=”,

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;

Sprawienie, aby „=” zachowywał się „intuicyjnie” wymagałoby niestety ogromnej straty obliczeniowej (wykonanie jawnej oceny ST_Equals () dla wywołania operatora) lub jakiegoś istotnego nowego skomplikowanego kodu (przechowywanie wartości skrótu dla większych geometrii, wykonywanie dokładnych testów w locie dla mniejszych te, wybierając odpowiednią ścieżkę kodu w locie itp.)

I oczywiście teraz wiele aplikacji / użytkowników zinternalizowało istniejące zachowanie, takie jak jest, więc „ulepszenie” byłoby obniżeniem poziomu dla wielu ludzi. Możesz zrobić różnicę „dokładną”, obliczając zamiast tego swój zestaw na ST_AsBinary (geom), która przeprowadzi dokładne testowanie równości na wyjściach bajtów.

— Paul Ramsey
źródło

I czy możemy założyć, że ST_AsBinary (geom) jest stosunkowo bardzo szybką operacją?

— Martin F

Dzięki za odpowiedź, to dobrze wyjaśnia zachowanie. Właściwie pracuję nad projektem geodjango, więc __equalsużyję tam filtru, który , jak sądzę , przekłada się na funkcję ST_Equals.

— yellowcap

1

Tak ST_AsBinary jest szybki. Testy równości na bytea prawdopodobnie obejmują memcmp, co jest bardzo szybką operacją, więc nie powinno być zbyt straszne.

— Paul Ramsey,

Co tu proponujesz, @PaulRamsey? SELECT DISTINCT ST_AsBinary(geom)? To daje binarną reprezentację geomjako wynik. Mógłbyś zrobić SELECT MAX(geom) FROM the_table GROUP BY ST_AsBinary(geom);(myślę, że funkcja agregująca taka MAX()jest wymagana w, SELECTponieważ GROUP BYklauzula używa ST_AsBinary()funkcji return, a nie samego pola.) Czy to wygląda dobrze?

— Martin Burch,

7

Biorąc pod uwagę doskonałe wyjaśnienie Paula Ramseya, dlaczego kolejnym pytaniem jest, co można z tym zrobić. Jak radzisz sobie SELECT DISTINCTna polach geometrii i czy działa ona zgodnie z oczekiwaniami?

W odpowiedzi Paula zaproponowałem użycie, SELECT MAX(geom) FROM the_table GROUP BY ST_AsBinary(geom);ale MAX()jest powolne, najwyraźniej wymaga skanowania tabeli.

Zamiast tego stwierdziłem, że jest to szybsze:

SELECT DISTINCT ON (ST_AsBinary(geom)) geom FROM the_table;

— Martin Burch
źródło

4

Tylko aktualizacja dla PostGIS 2.4 SELECT DISTINCTdziała poprawnie dla danych punktów w PO:

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)

I

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)

— tinlyx
źródło