[postgis-tickets] r16058 - Make geometry a hashable type, allowing recursive CTEs
Paul Ramsey
pramsey at cleverelephant.ca
Tue Oct 24 14:25:46 PDT 2017
Author: pramsey
Date: 2017-10-24 14:25:45 -0700 (Tue, 24 Oct 2017)
New Revision: 16058
Modified:
trunk/NEWS
trunk/postgis/lwgeom_btree.c
trunk/postgis/postgis.sql.in
trunk/regress/mvt.sql
trunk/regress/mvt_expected
trunk/regress/tickets.sql
trunk/regress/tickets_expected
Log:
Make geometry a hashable type, allowing recursive CTEs
to use geometry in their signatures.
Closes #1014
Modified: trunk/NEWS
===================================================================
--- trunk/NEWS 2017-10-24 19:58:19 UTC (rev 16057)
+++ trunk/NEWS 2017-10-24 21:25:45 UTC (rev 16058)
@@ -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,7 +22,9 @@
- #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
Modified: trunk/postgis/lwgeom_btree.c
===================================================================
--- trunk/postgis/lwgeom_btree.c 2017-10-24 19:58:19 UTC (rev 16057)
+++ trunk/postgis/lwgeom_btree.c 2017-10-24 21:25:45 UTC (rev 16058)
@@ -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 @@
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);
+}
+
+
+
Modified: trunk/postgis/postgis.sql.in
===================================================================
--- trunk/postgis/postgis.sql.in 2017-10-24 19:58:19 UTC (rev 16057)
+++ trunk/postgis/postgis.sql.in 2017-10-24 21:25:45 UTC (rev 16058)
@@ -408,6 +408,23 @@
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
-----------------------------------------------------------------------------
Modified: trunk/regress/mvt.sql
===================================================================
--- trunk/regress/mvt.sql 2017-10-24 19:58:19 UTC (rev 16057)
+++ trunk/regress/mvt.sql 2017-10-24 21:25:45 UTC (rev 16058)
@@ -87,7 +87,7 @@
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 @@
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,
Modified: trunk/regress/mvt_expected
===================================================================
--- trunk/regress/mvt_expected 2017-10-24 19:58:19 UTC (rev 16057)
+++ trunk/regress/mvt_expected 2017-10-24 21:25:45 UTC (rev 16058)
@@ -25,10 +25,10 @@
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==
Modified: trunk/regress/tickets.sql
===================================================================
--- trunk/regress/tickets.sql 2017-10-24 19:58:19 UTC (rev 16057)
+++ trunk/regress/tickets.sql 2017-10-24 21:25:45 UTC (rev 16058)
@@ -1025,5 +1025,31 @@
-- #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;
Modified: trunk/regress/tickets_expected
===================================================================
--- trunk/regress/tickets_expected 2017-10-24 19:58:19 UTC (rev 16057)
+++ trunk/regress/tickets_expected 2017-10-24 21:25:45 UTC (rev 16058)
@@ -306,3 +306,9 @@
#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)
More information about the postgis-tickets
mailing list