From 6d44001804476d91af89ad33f73175d9255a8bff Mon Sep 17 00:00:00 2001 From: Paul Ramsey Date: Tue, 24 Oct 2017 21:25:45 +0000 Subject: [PATCH] Make geometry a hashable type, allowing recursive CTEs to use geometry in their signatures. Closes #1014 git-svn-id: http://svn.osgeo.org/postgis/trunk@16058 b70326c6-7e19-0410-871a-916f4a2858ee --- NEWS | 3 +++ postgis/lwgeom_btree.c | 16 ++++++++++++++++ postgis/postgis.sql.in | 17 +++++++++++++++++ regress/mvt.sql | 18 +++++++++--------- regress/mvt_expected | 8 ++++---- regress/tickets.sql | 26 ++++++++++++++++++++++++++ regress/tickets_expected | 6 ++++++ 7 files changed, 81 insertions(+), 13 deletions(-) diff --git a/NEWS b/NEWS index adb5d82a8..4b7304710 100644 --- a/NEWS +++ b/NEWS @@ -1,5 +1,6 @@ PostGIS 2.5.0 2018/xx/xx + * New Features * - #3876, ST_Angle function (Rémi Cura) - #3564, ST_LineInterpolatePoints (Dan Baston) @@ -21,6 +22,8 @@ PostGIS 2.5.0 - #3234, Do not accept EMPTY points as topology nodes (Sandro Santilli) - #3892, Prevent version mixup when creating extension from unpackaged (Sandro Santilli) + - #1014, Hashable geometry, allowing direct use in CTE signatures (Paul Ramsey) + PostGIS 2.4.0 2017/09/30 diff --git a/postgis/lwgeom_btree.c b/postgis/lwgeom_btree.c index cfca3be4a..b937bfa5a 100644 --- a/postgis/lwgeom_btree.c +++ b/postgis/lwgeom_btree.c @@ -27,6 +27,7 @@ #include "postgres.h" #include "fmgr.h" +#include "access/hash.h" #include "utils/geo_decls.h" #include "../postgis_config.h" @@ -127,3 +128,18 @@ Datum lwgeom_cmp(PG_FUNCTION_ARGS) PG_RETURN_INT32(ret); } +PG_FUNCTION_INFO_V1(lwgeom_hash); +Datum lwgeom_hash(PG_FUNCTION_ARGS) +{ + GSERIALIZED *g1 = PG_GETARG_GSERIALIZED_P(0); + size_t sz1 = VARSIZE(g1); + size_t hsz1 = gserialized_header_size(g1); + uint8_t *b1 = (uint8_t*)g1 + hsz1; + size_t bsz1 = sz1 - hsz1; + Datum hval = hash_any(b1, bsz1); + PG_FREE_IF_COPY(g1, 0); + PG_RETURN_DATUM(hval); +} + + + diff --git a/postgis/postgis.sql.in b/postgis/postgis.sql.in index 3f9cb018c..58d094661 100644 --- a/postgis/postgis.sql.in +++ b/postgis/postgis.sql.in @@ -408,6 +408,23 @@ CREATE OPERATOR CLASS btree_geometry_ops FUNCTION 1 geometry_cmp (geom1 geometry, geom2 geometry); +-- +-- Sorting operators for Btree +-- + +-- Availability: 2.5.0 +CREATE FUNCTION geometry_hash(geometry) + RETURNS integer + AS 'MODULE_PATHNAME','lwgeom_hash' + LANGUAGE 'c' STRICT IMMUTABLE _PARALLEL; + +-- Availability: 2.5.0 +CREATE OPERATOR CLASS hash_geometry_ops + DEFAULT FOR TYPE geometry USING hash AS + OPERATOR 1 = , + FUNCTION 1 geometry_hash(geometry); + + ----------------------------------------------------------------------------- -- GiST 2D GEOMETRY-over-GSERIALIZED INDEX ----------------------------------------------------------------------------- diff --git a/regress/mvt.sql b/regress/mvt.sql index dd8239db2..297d55ab5 100644 --- a/regress/mvt.sql +++ b/regress/mvt.sql @@ -87,7 +87,7 @@ SELECT 'TA4', encode(ST_AsMVT(q, 'test', 4096, 'geom'), 'base64') FROM ( ST_MakeBox2D(ST_Point(0, 0), ST_Point(4096, 4096)), 4096, 0, false)) AS geom UNION SELECT 2 AS c1, ST_Normalize(ST_AsMVTGeom(ST_GeomFromText('POINT(25 17)'), - ST_MakeBox2D(ST_Point(0, 0), ST_Point(4096, 4096)), 4096, 0, false)) AS geom) AS q; + ST_MakeBox2D(ST_Point(0, 0), ST_Point(4096, 4096)), 4096, 0, false)) AS geom ORDER BY c1) AS q; SELECT 'TA5', encode(ST_AsMVT(q, 'test', 4096, 'geom'), 'base64') FROM (SELECT ST_Normalize(ST_AsMVTGeom(ST_GeomFromText('POINT(25 17)'), ST_MakeBox2D(ST_Point(0, 0), ST_Point(4096, 4096)), 4096, 0, false)) AS geom, 1 AS c1, 'abcd'::text AS c2) AS q; @@ -95,23 +95,23 @@ SELECT 'TA6', encode(ST_AsMVT(q, 'test', 4096, 'geom'), 'base64') FROM (SELECT 1 ST_Normalize(ST_AsMVTGeom(ST_GeomFromText('POINT(25 17)'), ST_MakeBox2D(ST_Point(0, 0), ST_Point(4096, 4096)), 4096, 0, false)) AS geom) AS q; SELECT 'TA7', encode(ST_AsMVT(q, 'test', 4096, 'geom'), 'base64') FROM ( - SELECT 'test' AS c1, ST_Normalize(ST_AsMVTGeom(ST_GeomFromText('POINT(25 17)'), + SELECT 'test' AS c1, 1 AS c2, ST_Normalize(ST_AsMVTGeom(ST_GeomFromText('POINT(25 17)'), ST_MakeBox2D(ST_Point(0, 0), ST_Point(4096, 4096)), 4096, 0, false)) AS geom UNION - SELECT 'test' AS c1, ST_Normalize(ST_AsMVTGeom(ST_GeomFromText('POINT(26 18)'), + SELECT 'test' AS c1, 2 AS c2, ST_Normalize(ST_AsMVTGeom(ST_GeomFromText('POINT(26 18)'), ST_MakeBox2D(ST_Point(0, 0), ST_Point(4096, 4096)), 4096, 0, false)) AS geom UNION - SELECT 'othertest' AS c1, ST_Normalize(ST_AsMVTGeom(ST_GeomFromText('POINT(26 18)'), - ST_MakeBox2D(ST_Point(0, 0), ST_Point(4096, 4096)), 4096, 0, false)) AS geom) AS q; + SELECT 'othertest' AS c1, 3 AS c2, ST_Normalize(ST_AsMVTGeom(ST_GeomFromText('POINT(26 18)'), + ST_MakeBox2D(ST_Point(0, 0), ST_Point(4096, 4096)), 4096, 0, false)) AS geom ORDER BY c2) AS q; SELECT 'TA8', encode(ST_AsMVT(q, 'test', 4096, 'geom'), 'base64') FROM ( - SELECT 1::int AS c1, ST_Normalize(ST_AsMVTGeom(ST_GeomFromText('POINT(25 17)'), + SELECT 1::int AS c1, 1 AS c2, ST_Normalize(ST_AsMVTGeom(ST_GeomFromText('POINT(25 17)'), ST_MakeBox2D(ST_Point(0, 0), ST_Point(4096, 4096)), 4096, 0, false)) AS geom UNION - SELECT 1::int AS c1, ST_Normalize(ST_AsMVTGeom(ST_GeomFromText('POINT(26 18)'), + SELECT 1::int AS c1, 2 AS c2, ST_Normalize(ST_AsMVTGeom(ST_GeomFromText('POINT(26 18)'), ST_MakeBox2D(ST_Point(0, 0), ST_Point(4096, 4096)), 4096, 0, false)) AS geom UNION - SELECT 2::int AS c1, ST_Normalize(ST_AsMVTGeom(ST_GeomFromText('POINT(26 18)'), - ST_MakeBox2D(ST_Point(0, 0), ST_Point(4096, 4096)), 4096, 0, false)) AS geom) AS q; + SELECT 2::int AS c1, 3 AS c2, ST_Normalize(ST_AsMVTGeom(ST_GeomFromText('POINT(26 18)'), + ST_MakeBox2D(ST_Point(0, 0), ST_Point(4096, 4096)), 4096, 0, false)) AS geom ORDER BY c2) AS q; SELECT 'TA9', length(ST_AsMVT(q)) FROM ( SELECT 1 AS c1, -1 AS c2, diff --git a/regress/mvt_expected b/regress/mvt_expected index 4b699cc7c..5fa915d1a 100644 --- a/regress/mvt_expected +++ b/regress/mvt_expected @@ -25,10 +25,10 @@ TA3|GhkKBHRlc3QSCBgBIgQJMt4/GgJjMSiAIHgC TA4|GjMKBHRlc3QSDBICAAAYASIECTLePxIMEgIAARgBIgQJMt4/GgJjMSICKAEiAigCKIAgeAI= TA5|Gi8KBHRlc3QSDhIEAAABARgBIgQJMt4/GgJjMRoCYzIiAigBIgYKBGFiY2QogCB4Ag== TA6|GisKBHRlc3QSDhIEAAABARgBIgQJMt4/GgJjMRoCYzIiAigBIgIwASiAIHgC -TA7|Gk4KBHRlc3QSDBICAAAYASIECTTcPxIMEgIAARgBIgQJMt4/EgwSAgABGAEiBAk03D8aAmMxIgsK -CW90aGVydGVzdCIGCgR0ZXN0KIAgeAI= -TA8|GkEKBHRlc3QSDBICAAAYASIECTLePxIMEgIAABgBIgQJNNw/EgwSAgABGAEiBAk03D8aAmMxIgIo -ASICKAIogCB4Ag== +TA7|GmQKBHRlc3QSDhIEAAABARgBIgQJMt4/Eg4SBAAAAQIYASIECTTcPxIOEgQAAwEEGAEiBAk03D8a +AmMxGgJjMiIGCgR0ZXN0IgIoASICKAIiCwoJb3RoZXJ0ZXN0IgIoAyiAIHgC +TA8|Gk8KBHRlc3QSDhIEAAABABgBIgQJMt4/Eg4SBAAAAQEYASIECTTcPxIOEgQAAQECGAEiBAk03D8a +AmMxGgJjMiICKAEiAigCIgIoAyiAIHgC TA9|0 TA10|49 D1|Gi8KBHRlc3QSDhIEAAABARgBIgQJMt4/GgJjMRoCYzIiAigBIgYKBGFiY2QogCB4Ag== diff --git a/regress/tickets.sql b/regress/tickets.sql index c5745e882..fdaa8e171 100644 --- a/regress/tickets.sql +++ b/regress/tickets.sql @@ -1025,5 +1025,31 @@ select '#3709', ST_SnapToGrid(ST_Project('SRID=4326;POINT(1 1)'::geography, 1000 -- #3774 select '#3774', abs(pi() + 2 - st_length('COMPOUNDCURVE(CIRCULARSTRING(0 0, 1 1, 2 0), (2 0, 4 0))'::geometry)) < 0.000000001; +-- #1014 +SELECT '#1014a', ST_AsText(g) FROM ( + SELECT 'POINT(-0 0)'::geometry AS g + UNION + SELECT 'POINT(0 0)'::geometry AS g +) a; +SELECT '#1014b', ST_AsText(g) FROM ( + SELECT 'POINT(0 1)'::geometry AS g + UNION + SELECT 'POINT(0 1)'::geometry AS g +) a; +CREATE TABLE rec (id integer, g geometry); +INSERT INTO rec VALUES (1, 'POINT(0 1)'); +INSERT INTO rec VALUES (2, 'POINT(1 2)'); +INSERT INTO rec VALUES (3, 'POINT(2 3)'); +WITH RECURSIVE path (id, g) AS ( + SELECT id, g FROM rec WHERE id = 1 + UNION + SELECT rec.id, rec.g + FROM path, rec + WHERE ST_Y(path.g) = ST_X(rec.g) +) +SELECT '#1014c', id, st_astext(g) FROM path; +DROP TABLE IF EXISTS rec; + + -- Clean up DELETE FROM spatial_ref_sys; diff --git a/regress/tickets_expected b/regress/tickets_expected index 7ae44fc62..f1fd73e4e 100644 --- a/regress/tickets_expected +++ b/regress/tickets_expected @@ -306,3 +306,9 @@ ERROR: invalid KML representation #3704|t #3709|t #3774|t +#1014a|POINT(0 0) +#1014a|POINT(-0 0) +#1014b|POINT(0 1) +#1014c|1|POINT(0 1) +#1014c|2|POINT(1 2) +#1014c|3|POINT(2 3) -- 2.40.0