[postgis-tickets] [SCM] PostGIS branch master updated. 3.2.0-482-g6893c5422
git at osgeo.org
git at osgeo.org
Fri Feb 4 06:54:04 PST 2022
This is an automated email from the git hooks/post-receive script. It was
generated because a ref change was pushed to the repository containing
the project "PostGIS".
The branch, master has been updated
via 6893c542220566e29445faf6b2de2b54aca77169 (commit)
from 3878e2497790025ef5c08a6c9c71fefc8f17d687 (commit)
Those revisions listed above that are new to this repository have
not appeared on any other notification email; so we list those
revisions in full, below.
- Log -----------------------------------------------------------------
commit 6893c542220566e29445faf6b2de2b54aca77169
Author: Sandro Santilli <strk at kbt.io>
Date: Fri Feb 4 15:53:39 2022 +0100
Use modern (typmod, format) syntax in CreateTopology
diff --git a/topology/topology.sql.in b/topology/topology.sql.in
index 6382b5b50..eaf6f8b8c 100644
--- a/topology/topology.sql.in
+++ b/topology/topology.sql.in
@@ -1446,11 +1446,12 @@ CREATE CAST (topology.TopoGeometry AS Geometry) WITH FUNCTION topology.Geometry(
CREATE OR REPLACE FUNCTION topology.CreateTopology(atopology varchar, srid integer, prec float8, hasZ boolean)
RETURNS integer
AS
-$$
+$BODY$
DECLARE
rec RECORD;
topology_id integer;
- ndims integer;
+ sql TEXT;
+ zsuffix TEXT := '';
BEGIN
-- FOR rec IN SELECT * FROM pg_namespace WHERE text(nspname) = atopology
@@ -1458,8 +1459,9 @@ BEGIN
-- RAISE EXCEPTION 'SQL/MM Spatial exception - schema already exists';
-- END LOOP;
- ndims = 2;
- IF hasZ THEN ndims = 3; END IF;
+ IF hasZ THEN
+ zsuffix := 'z';
+ END IF;
IF srid < 0 THEN
RAISE NOTICE 'SRID value % converted to the officially unknown SRID value 0', srid;
@@ -1475,232 +1477,295 @@ BEGIN
EXECUTE 'CREATE SCHEMA ' || quote_ident(atopology);
-------------{ face CREATION
- EXECUTE
- 'CREATE TABLE ' || quote_ident(atopology) || '.face ('
- 'face_id SERIAL,'
- ' CONSTRAINT face_primary_key PRIMARY KEY(face_id)'
- ');';
-
- -- Add mbr column to the face table
- EXECUTE
- 'SELECT AddGeometryColumn('||quote_literal(atopology)
- ||',''face'',''mbr'','||quote_literal(srid)
- ||',''POLYGON'',2)'; -- 2d only mbr is good enough
+ sql := format(
+ $$
+ CREATE TABLE %I.face(
+ face_id SERIAL,
+ mbr GEOMETRY(polygon%s, %L),
+ CONSTRAINT face_primary_key
+ PRIMARY KEY(face_id)
+ )
+ $$,
+ atopology,
+ zsuffix,
+ srid
+ );
+#ifdef POSTGIS_TOPOLOGY_DEBUG
+ RAISE DEBUG 'SQL: %', sql;
+#endif
+ EXECUTE sql;
-- Face standard view description
- EXECUTE 'COMMENT ON TABLE ' || quote_ident(atopology)
- || '.face IS '
- '''Contains face topology primitives''';
+ sql := format(
+ $$
+ COMMENT ON TABLE %I.face IS
+ 'Contains face topology primitives'
+ $$,
+ atopology
+ );
+#ifdef POSTGIS_TOPOLOGY_DEBUG
+ RAISE DEBUG 'SQL: %', sql;
+#endif
+ EXECUTE sql;
-------------} END OF face CREATION
--------------{ node CREATION
- EXECUTE
- 'CREATE TABLE ' || quote_ident(atopology) || '.node ('
- 'node_id SERIAL,'
- --|| 'geom GEOMETRY,'
- 'containing_face INTEGER,'
-
- 'CONSTRAINT node_primary_key PRIMARY KEY(node_id),'
-
- --|| 'CONSTRAINT node_geometry_type CHECK '
- --|| '( GeometryType(geom) = ''POINT'' ),'
-
- 'CONSTRAINT face_exists FOREIGN KEY(containing_face) '
- 'REFERENCES ' || quote_ident(atopology) || '.face(face_id)'
-
- ');';
-
- -- Add geometry column to the node table
- EXECUTE
- 'SELECT AddGeometryColumn('||quote_literal(atopology)
- ||',''node'',''geom'','||quote_literal(srid)
- ||',''POINT'',' || ndims || ')';
+ sql := format(
+ $$
+ CREATE TABLE %1$I.node(
+ node_id SERIAL,
+ geom GEOMETRY(point%3$s, %2$L),
+ containing_face INTEGER,
+ CONSTRAINT node_primary_key
+ PRIMARY KEY(node_id),
+ CONSTRAINT face_exists
+ FOREIGN KEY(containing_face)
+ REFERENCES %1$I.face(face_id)
+ )
+ $$,
+ atopology,
+ srid,
+ zsuffix
+ );
+#ifdef POSTGIS_TOPOLOGY_DEBUG
+ RAISE DEBUG 'SQL: %', sql;
+#endif
+ EXECUTE sql;
-- Node standard view description
- EXECUTE 'COMMENT ON TABLE ' || quote_ident(atopology)
- || '.node IS '
- '''Contains node topology primitives''';
+ sql := format(
+ $$
+ COMMENT ON TABLE %I.node IS
+ 'Contains node topology primitives'
+ $$,
+ atopology
+ );
+#ifdef POSTGIS_TOPOLOGY_DEBUG
+ RAISE DEBUG 'SQL: %', sql;
+#endif
+ EXECUTE sql;
--------------} END OF node CREATION
--------------{ edge CREATION
-- edge_data table
- EXECUTE
- 'CREATE TABLE ' || quote_ident(atopology) || '.edge_data ('
- 'edge_id SERIAL NOT NULL PRIMARY KEY,'
- 'start_node INTEGER NOT NULL,'
- 'end_node INTEGER NOT NULL,'
- 'next_left_edge INTEGER NOT NULL,'
- 'abs_next_left_edge INTEGER NOT NULL,'
- 'next_right_edge INTEGER NOT NULL,'
- 'abs_next_right_edge INTEGER NOT NULL,'
- 'left_face INTEGER NOT NULL,'
- 'right_face INTEGER NOT NULL,'
- -- 'geom GEOMETRY NOT NULL,'
-
- -- 'CONSTRAINT edge_geometry_type CHECK '
- -- '( GeometryType(geom) = ''LINESTRING'' ),'
-
- 'CONSTRAINT start_node_exists FOREIGN KEY(start_node)'
- ' REFERENCES ' || quote_ident(atopology) || '.node(node_id),'
-
- 'CONSTRAINT end_node_exists FOREIGN KEY(end_node) '
- ' REFERENCES ' || quote_ident(atopology) || '.node(node_id),'
-
- 'CONSTRAINT left_face_exists FOREIGN KEY(left_face) '
- 'REFERENCES ' || quote_ident(atopology) || '.face(face_id),'
-
- 'CONSTRAINT right_face_exists FOREIGN KEY(right_face) '
- 'REFERENCES ' || quote_ident(atopology) || '.face(face_id),'
-
- 'CONSTRAINT next_left_edge_exists FOREIGN KEY(abs_next_left_edge)'
- ' REFERENCES ' || quote_ident(atopology)
- || '.edge_data(edge_id)'
- ' DEFERRABLE INITIALLY DEFERRED,'
-
- 'CONSTRAINT next_right_edge_exists '
- 'FOREIGN KEY(abs_next_right_edge)'
- ' REFERENCES ' || quote_ident(atopology)
- || '.edge_data(edge_id) '
- ' DEFERRABLE INITIALLY DEFERRED'
- ');';
-
- -- Add geometry column to the edge_data table
- EXECUTE
- 'SELECT AddGeometryColumn('||quote_literal(atopology)
- ||',''edge_data'',''geom'','||quote_literal(srid)
- ||',''LINESTRING'',' || ndims || ')';
+ sql := format(
+ $$
+ CREATE TABLE %1$I.edge_data (
+ edge_id SERIAL NOT NULL PRIMARY KEY,
+ start_node INTEGER NOT NULL,
+ end_node INTEGER NOT NULL,
+ next_left_edge INTEGER NOT NULL,
+ abs_next_left_edge INTEGER NOT NULL,
+ next_right_edge INTEGER NOT NULL,
+ abs_next_right_edge INTEGER NOT NULL,
+ left_face INTEGER NOT NULL,
+ right_face INTEGER NOT NULL,
+ geom GEOMETRY(linestring%3$s, %2$L) NOT NULL,
+
+ CONSTRAINT start_node_exists
+ FOREIGN KEY(start_node)
+ REFERENCES %1$I.node(node_id),
+
+ CONSTRAINT end_node_exists
+ FOREIGN KEY(end_node)
+ REFERENCES %1$I.node(node_id),
+
+ CONSTRAINT left_face_exists
+ FOREIGN KEY(left_face)
+ REFERENCES %1$I.face(face_id),
+
+ CONSTRAINT right_face_exists
+ FOREIGN KEY(right_face)
+ REFERENCES %1$I.face(face_id),
+
+ CONSTRAINT next_left_edge_exists
+ FOREIGN KEY(abs_next_left_edge)
+ REFERENCES %1$I.edge_data(edge_id)
+ DEFERRABLE INITIALLY DEFERRED,
+
+ CONSTRAINT next_right_edge_exists
+ FOREIGN KEY(abs_next_right_edge)
+ REFERENCES %1$I .edge_data(edge_id)
+ DEFERRABLE INITIALLY DEFERRED
+ )
+ $$,
+ atopology,
+ srid,
+ zsuffix
+ );
+#ifdef POSTGIS_TOPOLOGY_DEBUG
+ RAISE DEBUG 'SQL: %', sql;
+#endif
+ EXECUTE sql;
-- edge standard view (select rule)
- EXECUTE 'CREATE VIEW ' || quote_ident(atopology)
- || '.edge AS SELECT '
- ' edge_id, start_node, end_node, next_left_edge, '
- ' next_right_edge, '
- ' left_face, right_face, geom FROM '
- || quote_ident(atopology) || '.edge_data';
+ sql := format(
+ $$
+ CREATE VIEW %1$I.edge AS
+ SELECT
+ edge_id, start_node, end_node, next_left_edge,
+ next_right_edge, left_face, right_face, geom
+ FROM %1$I.edge_data
+ $$,
+ atopology
+ );
+#ifdef POSTGIS_TOPOLOGY_DEBUG
+ RAISE DEBUG 'SQL: %', sql;
+#endif
+ EXECUTE sql;
-- Edge standard view description
- EXECUTE 'COMMENT ON VIEW ' || quote_ident(atopology)
- || '.edge IS '
- '''Contains edge topology primitives''';
- EXECUTE 'COMMENT ON COLUMN ' || quote_ident(atopology)
- || '.edge.edge_id IS '
- '''Unique identifier of the edge''';
- EXECUTE 'COMMENT ON COLUMN ' || quote_ident(atopology)
- || '.edge.start_node IS '
- '''Unique identifier of the node at the start of the edge''';
- EXECUTE 'COMMENT ON COLUMN ' || quote_ident(atopology)
- || '.edge.end_node IS '
- '''Unique identifier of the node at the end of the edge''';
- EXECUTE 'COMMENT ON COLUMN ' || quote_ident(atopology)
- || '.edge.next_left_edge IS '
- '''Unique identifier of the next edge of the face on the left (when looking in the direction from START_NODE to END_NODE), moving counterclockwise around the face boundary''';
- EXECUTE 'COMMENT ON COLUMN ' || quote_ident(atopology)
- || '.edge.next_right_edge IS '
- '''Unique identifier of the next edge of the face on the right (when looking in the direction from START_NODE to END_NODE), moving counterclockwise around the face boundary''';
- EXECUTE 'COMMENT ON COLUMN ' || quote_ident(atopology)
- || '.edge.left_face IS '
- '''Unique identifier of the face on the left side of the edge when looking in the direction from START_NODE to END_NODE''';
- EXECUTE 'COMMENT ON COLUMN ' || quote_ident(atopology)
- || '.edge.right_face IS '
- '''Unique identifier of the face on the right side of the edge when looking in the direction from START_NODE to END_NODE''';
- EXECUTE 'COMMENT ON COLUMN ' || quote_ident(atopology)
- || '.edge.geom IS '
- '''The geometry of the edge''';
+ sql := format(
+ $$
+ COMMENT ON VIEW %1$I.edge IS
+ 'Contains edge topology primitives';
+ COMMENT ON COLUMN %1$I.edge.edge_id IS
+ 'Unique identifier of the edge';
+ COMMENT ON COLUMN %1$I.edge.start_node IS
+ 'Unique identifier of the node at the start of the edge';
+ COMMENT ON COLUMN %1$I.edge.end_node IS
+ 'Unique identifier of the node at the end of the edge';
+ COMMENT ON COLUMN %1$I.edge.next_left_edge IS
+ 'Unique identifier of the next edge of the face on the left (when looking in the direction from START_NODE to END_NODE), moving counterclockwise around the face boundary';
+ COMMENT ON COLUMN %1$I.edge.next_right_edge IS
+ 'Unique identifier of the next edge of the face on the right (when looking in the direction from START_NODE to END_NODE), moving counterclockwise around the face boundary';
+ COMMENT ON COLUMN %1$I.edge.left_face IS
+ 'Unique identifier of the face on the left side of the edge when looking in the direction from START_NODE to END_NODE';
+ COMMENT ON COLUMN %1$I.edge.right_face IS
+ 'Unique identifier of the face on the right side of the edge when looking in the direction from START_NODE to END_NODE';
+ COMMENT ON COLUMN %1$I.edge.geom IS
+ 'The geometry of the edge';
+ $$,
+ atopology
+ );
+#ifdef POSTGIS_TOPOLOGY_DEBUG
+ RAISE DEBUG 'SQL: %', sql;
+#endif
+ EXECUTE sql;
-- edge standard view (insert rule)
- EXECUTE 'CREATE RULE edge_insert_rule AS ON INSERT '
- 'TO ' || quote_ident(atopology)
- || '.edge DO INSTEAD '
- ' INSERT into ' || quote_ident(atopology)
- || '.edge_data '
- ' VALUES (NEW.edge_id, NEW.start_node, NEW.end_node, '
- ' NEW.next_left_edge, abs(NEW.next_left_edge), '
- ' NEW.next_right_edge, abs(NEW.next_right_edge), '
- ' NEW.left_face, NEW.right_face, NEW.geom);';
+ sql := format(
+ $$
+ CREATE RULE edge_insert_rule AS
+ ON INSERT TO %1$I.edge
+ DO INSTEAD INSERT into %1$I.edge_data
+ VALUES (
+ NEW.edge_id, NEW.start_node, NEW.end_node,
+ NEW.next_left_edge, abs(NEW.next_left_edge),
+ NEW.next_right_edge, abs(NEW.next_right_edge),
+ NEW.left_face, NEW.right_face, NEW.geom
+ )
+ $$,
+ atopology
+ );
+#ifdef POSTGIS_TOPOLOGY_DEBUG
+ RAISE DEBUG 'SQL: %', sql;
+#endif
+ EXECUTE sql;
--------------} END OF edge CREATION
--------------{ layer sequence
- EXECUTE 'CREATE SEQUENCE '
- || quote_ident(atopology) || '.layer_id_seq;';
+ sql := format(
+ 'CREATE SEQUENCE %I.layer_id_seq',
+ atopology
+ );
+#ifdef POSTGIS_TOPOLOGY_DEBUG
+ RAISE DEBUG 'SQL: %', sql;
+#endif
+ EXECUTE sql;
--------------} layer sequence
--------------{ relation CREATION
- --
- EXECUTE
- 'CREATE TABLE ' || quote_ident(atopology) || '.relation ('
- ' topogeo_id integer NOT NULL, '
- ' layer_id integer NOT NULL, '
- ' element_id integer NOT NULL, '
- ' element_type integer NOT NULL, '
- ' UNIQUE(layer_id,topogeo_id,element_id,element_type));';
-
- EXECUTE
- 'CREATE TRIGGER relation_integrity_checks '
- 'BEFORE UPDATE OR INSERT ON '
- || quote_ident(atopology) || '.relation FOR EACH ROW '
- ' EXECUTE PROCEDURE topology.RelationTrigger('
- ||topology_id||','||quote_literal(atopology)||')';
+ sql := format(
+ $$
+ CREATE TABLE %1$I.relation (
+ topogeo_id integer NOT NULL,
+ layer_id integer NOT NULL,
+ element_id integer NOT NULL,
+ element_type integer NOT NULL,
+ UNIQUE(layer_id,topogeo_id,element_id,element_type)
+ )
+ $$,
+ atopology
+ );
+#ifdef POSTGIS_TOPOLOGY_DEBUG
+ RAISE DEBUG 'SQL: %', sql;
+#endif
+ EXECUTE sql;
+
+ sql := format(
+ $$
+ CREATE TRIGGER relation_integrity_checks
+ BEFORE UPDATE OR INSERT ON %1$I.relation
+ FOR EACH ROW EXECUTE PROCEDURE
+ topology.RelationTrigger(%2$L, %1$L)
+ $$,
+ atopology,
+ topology_id
+ );
+#ifdef POSTGIS_TOPOLOGY_DEBUG
+ RAISE DEBUG 'SQL: %', sql;
+#endif
+ EXECUTE sql;
--------------} END OF relation CREATION
------- Default (world) face
- EXECUTE 'INSERT INTO ' || quote_ident(atopology) || '.face(face_id) VALUES(0);';
+ EXECUTE format('INSERT INTO %I.face(face_id) VALUES(0)', atopology);
------- GiST index on face
- EXECUTE 'CREATE INDEX face_gist ON '
- || quote_ident(atopology)
- || '.face using gist (mbr);';
+ EXECUTE format($$
+ CREATE INDEX face_gist ON %1$I.face
+ USING gist (mbr)
+ $$, atopology);
------- GiST index on node
- EXECUTE 'CREATE INDEX node_gist ON '
- || quote_ident(atopology)
- || '.node using gist (geom);';
+ EXECUTE format($$
+ CREATE INDEX node_gist ON %1$I.node
+ USING gist (geom)
+ $$, atopology);
------- GiST index on edge
- EXECUTE 'CREATE INDEX edge_gist ON '
- || quote_ident(atopology)
- || '.edge_data using gist (geom);';
+ EXECUTE format($$
+ CREATE INDEX edge_gist ON %1$I.edge_data
+ USING gist (geom)
+ $$, atopology);
------- Indexes on left_face and right_face of edge_data
------- NOTE: these indexes speed up GetFaceGeometry (and thus
------- TopoGeometry::Geometry) by a factor of 10 !
------- See http://trac.osgeo.org/postgis/ticket/806
- EXECUTE 'CREATE INDEX edge_left_face_idx ON '
- || quote_ident(atopology)
- || '.edge_data (left_face);';
- EXECUTE 'CREATE INDEX edge_right_face_idx ON '
- || quote_ident(atopology)
- || '.edge_data (right_face);';
+ EXECUTE format($$
+ CREATE INDEX edge_left_face_idx
+ ON %1$I.edge_data (left_face);
+ CREATE INDEX edge_right_face_idx
+ ON %1$I.edge_data (right_face);
+ $$, atopology);
------- Indexes on start_node and end_node of edge_data
------- NOTE: this indexes speed up node deletion
------- by a factor of 1000 !
------- See http://trac.osgeo.org/postgis/ticket/2082
- EXECUTE 'CREATE INDEX edge_start_node_idx ON '
- || quote_ident(atopology)
- || '.edge_data (start_node);';
- EXECUTE 'CREATE INDEX edge_end_node_idx ON '
- || quote_ident(atopology)
- || '.edge_data (end_node);';
+ EXECUTE format($$
+ CREATE INDEX edge_start_node_idx
+ ON %1$I.edge_data (start_node);
+ CREATE INDEX edge_end_node_idx
+ ON %1$I.edge_data (end_node);
+ $$, atopology);
-- TODO: consider also adding an index on node.containing_face
------- Add record to the "topology" metadata table
- EXECUTE 'INSERT INTO topology.topology '
- || '(id, name, srid, precision, hasZ) VALUES ('
- || quote_literal(topology_id) || ','
- || quote_literal(atopology) || ','
- || quote_literal(srid) || ',' || quote_literal(prec)
- || ',' || hasZ
- || ')';
+ INSERT INTO topology.topology (id, name, srid, precision, hasZ)
+ VALUES (topology_id, atopology, srid, prec, hasZ);
RETURN topology_id;
END
-$$
+$BODY$
LANGUAGE 'plpgsql' VOLATILE STRICT;
--} CreateTopology
-----------------------------------------------------------------------
Summary of changes:
topology/topology.sql.in | 417 +++++++++++++++++++++++++++--------------------
1 file changed, 241 insertions(+), 176 deletions(-)
hooks/post-receive
--
PostGIS
More information about the postgis-tickets
mailing list