[postgis-tickets] [SCM] PostGIS branch master updated. 3.2.0-697-gb68342617
git at osgeo.org
git at osgeo.org
Wed Apr 6 02:03:18 PDT 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 b68342617e17bcdf800613d43b3a4fd2fe7dced5 (commit)
from 83faf96fc252504ada28bd07ba86c4ba6b31aa5a (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 b68342617e17bcdf800613d43b3a4fd2fe7dced5
Author: Sandro Santilli <strk at kbt.io>
Date: Mon Mar 28 18:42:37 2022 +0200
topology loader/dumper scripts
diff --git a/.editorconfig b/.editorconfig
index 9d91e6ff2..493a499d5 100644
--- a/.editorconfig
+++ b/.editorconfig
@@ -21,6 +21,10 @@ indent_style = tab
[*.pl]
indent_style = tab
+[topology/loader/*.sh]
+indent_style = space
+indent_size = 2
+
# Exception: utils/postgis_proc_upgrade.pl uses 4-spaces indent
[utils/postgis_proc_upgrade.pl]
indent_style = space
diff --git a/NEWS b/NEWS
index 64fdb4664..0b5d355f7 100644
--- a/NEWS
+++ b/NEWS
@@ -23,6 +23,7 @@ PostGIS 3.3.0dev
- GH678, Enable Link-Time Optimizations by default if supported (Sergei Shoulbakov)
* New features *
+ - #5116, Topology export/import scripts (Sandro Santilli)
- ST_Letters creates geometries that look like letters (Paul Ramsey)
- #5037, postgis_sfcgal: ST_3DConvexHull (Loïc Bartoletti)
- postgis_sfcgal: sfcgal_full_version - reports BOOST and CGAL version
diff --git a/topology/loader/pgtopo_export.sh b/topology/loader/pgtopo_export.sh
new file mode 100755
index 000000000..ccec86278
--- /dev/null
+++ b/topology/loader/pgtopo_export.sh
@@ -0,0 +1,209 @@
+#!/bin/sh
+
+#
+# PostGIS - Spatial Types for PostgreSQL
+# http://postgis.net
+#
+# Copyright (C) 2022 Sandro Santilli <strk at kbt.io>
+#
+# This is free software; you can redistribute and/or modify it under
+# the terms of the GNU General Public Licence. See the COPYING file.
+#
+
+set -e
+
+usage() {
+ echo "Usage: $0 [--skip-layers] [ -f <dumpfile> ] <dbname> <toponame>"
+}
+
+SKIP_LAYERS=no
+OUTFILE=-
+
+while test -n "$1"; do
+ if test "$1" = '--skip-layers'; then
+ SKIP_LAYERS=yes
+ shift
+ elif test "$1" = '-f'; then
+ shift
+ OUTFILE=$1
+ shift
+ elif test -z "${DBNAME}"; then
+ DBNAME=$1
+ shift
+ elif test -z "${TOPONAME}"; then
+ TOPONAME=$1
+ shift
+ else
+ echo "Unrecognized option $1" >&2
+ usage >&2
+ exit 1
+ fi
+done
+
+DUMPNAME="pgtopo_export"
+
+if test "${OUTFILE}" = "-"; then
+ exec 4>&2
+else
+ exec 4>&1
+fi
+
+test -n "${TOPONAME}" || {
+ usage >&2
+ exit 1
+}
+
+TMPDIR=${TMPDIR-/tmp}
+WORKDIR=${TMPDIR}/pgtopo_export_$$
+DUMPDIR=${WORKDIR}/${DUMPNAME}
+mkdir -p ${DUMPDIR}
+
+cleanup() {
+ echo "Cleaning up" >&4
+ rm -rf ${WORKDIR}
+}
+
+trap 'cleanup' 0
+
+export PGDATABASE=${DBNAME}
+
+#################################################
+# Write version info (of the output format)
+#################################################
+
+echo "1" > ${DUMPDIR}/pgtopo_dump_version || exit 1
+
+#####################
+# Save topology info
+#####################
+
+echo "Exporting topology config..." >&4
+cat <<EOF | psql --set ON_ERROR_STOP=1 -XtA > ${DUMPDIR}/topology || exit 1
+COPY (
+ SELECT
+ srid,
+ precision,
+ hasz
+ FROM
+ topology.topology
+ WHERE name = '${TOPONAME}'
+)
+TO STDOUT;
+EOF
+
+###################
+# Save layers info
+###################
+
+echo "Exporting layers info..." >&4
+cat <<EOF | psql --set ON_ERROR_STOP=1 -XtA > ${DUMPDIR}/layers || exit 1
+COPY (
+ SELECT
+ layer_id,
+ schema_name,
+ table_name,
+ feature_column,
+ feature_type,
+ level,
+ child_id
+ FROM
+ topology.layer l,
+ topology.topology t
+ WHERE
+ t.name = '${TOPONAME}'
+ AND l.topology_id = t.id
+)
+TO STDOUT;
+EOF
+
+##########################
+# Copy topology primitives
+##########################
+
+# Nodes
+# Workaround for https://trac.osgeo.org/postgis/ticket/5102
+echo "Exporting node..." >&4
+cat <<EOF | psql --set ON_ERROR_STOP=1 -XtA > ${DUMPDIR}/node || exit 1
+COPY ( SELECT
+ node_id,
+ geom,
+ containing_face
+FROM ${TOPONAME}.node ) TO STDOUT;
+EOF
+
+# Edges
+# FIXME: copy edge view https://trac.osgeo.org/postgis/ticket/5119
+echo "Exporting edge..." >&4
+cat <<EOF | psql --set ON_ERROR_STOP=1 -XtA > ${DUMPDIR}/edge_data || exit 1
+COPY ( SELECT
+ edge_id,
+ start_node,
+ end_node,
+ next_left_edge,
+ abs_next_left_edge,
+ next_right_edge,
+ abs_next_right_edge,
+ left_face,
+ right_face,
+ geom
+FROM ${TOPONAME}.edge_data ) TO STDOUT;
+EOF
+
+# Faces
+echo "Exporting face..." >&4
+ cat <<EOF | psql --set ON_ERROR_STOP=1 -XtA > ${DUMPDIR}/face || exit 1
+COPY ( SELECT
+ face_id,
+ mbr
+FROM ${TOPONAME}.face WHERE face_id != 0 ) TO STDOUT;
+EOF
+
+# Relation
+echo "Exporting relation..." >&4
+ cat <<EOF | psql --set ON_ERROR_STOP=1 -XtA > ${DUMPDIR}/relation || exit 1
+COPY ( SELECT * FROM ${TOPONAME}.relation ) TO STDOUT;
+EOF
+
+if test "$SKIP_LAYERS" = "no"; then # {
+ ###################
+ # Dump layer tables
+ ###################
+
+ DUMPFILE="${DUMPDIR}/layers.dump"
+ CMD="pg_dump -O -x -Fc"
+ CMD="${CMD} -f ${DUMPFILE}"
+
+ exec 3< ${DUMPDIR}/layers
+ numlayers=0
+ while read -r line <&3; do
+ id=$(echo "${line}" | cut -f1)
+ schema=$(echo "${line}" | cut -f2)
+ table=$(echo "${line}" | cut -f3)
+ layer="\"${schema}\".\"${table}\""
+ #echo "Layer: ${layer}"
+ CMD="${CMD} -t ${layer}"
+ numlayers=$((numlayers+1))
+ done;
+
+
+ if test ${numlayers} -gt 0; then
+ echo "Dumping ${numlayers} layers..." >&4
+ #echo "Running ${CMD}" >&4
+ ${CMD} || exit 1
+ fi
+fi # }
+
+
+(
+ if test "${OUTFILE}" != "-"; then
+ OFILE=$(cd $(dirname ${OUTFILE}) && pwd)/$(basename ${OUTFILE})
+ else
+ OFILE=-
+ fi
+
+ cd ${DUMPDIR}/.. || exit 1
+ echo "Archiving..." >&4
+ tar czf ${OFILE} ${DUMPNAME} || exit 1
+)
+
+exit 0
diff --git a/topology/loader/pgtopo_import.sh b/topology/loader/pgtopo_import.sh
new file mode 100755
index 000000000..526ccd8c8
--- /dev/null
+++ b/topology/loader/pgtopo_import.sh
@@ -0,0 +1,314 @@
+#!/bin/sh
+
+#
+# PostGIS - Spatial Types for PostgreSQL
+# http://postgis.net
+#
+# Copyright (C) 2022 Sandro Santilli <strk at kbt.io>
+#
+# This is free software; you can redistribute and/or modify it under
+# the terms of the GNU General Public Licence. See the COPYING file.
+#
+
+set -e
+
+usage() {
+ echo "Usage: $0 [ --skip-layers | --only-layers ] [ -f <dumpfile> ] <toponame>"
+}
+
+
+
+SKIP_LAYERS=no
+ONLY_LAYERS=no
+TOPONAME=
+DUMPFILE=-
+
+while test -n "$1"; do
+ if test "$1" = '--skip-layers'; then
+ SKIP_LAYERS=yes
+ shift
+ elif test "$1" = '--only-layers'; then
+ ONLY_LAYERS=yes
+ shift
+ elif test "$1" = '-f'; then
+ shift
+ DUMPFILE="$1"
+ shift
+ elif test -z "${TOPONAME}"; then
+ TOPONAME=$1
+ shift
+ else
+ echo "Unrecognized option $1" >&2
+ usage >&2
+ exit 1
+ fi
+done
+
+test -n "${TOPONAME}" || {
+ usage >&2
+ exit 1
+}
+
+if test "${SKIP_LAYERS}" = "yes" -a "${ONLY_LAYERS}" = "yes"; then
+ echo "--skip-layers conflicts with --only-layers" >&2
+ exit 1
+fi
+
+TMPDIR=${TMPDIR-/tmp}
+WORKDIR=${TMPDIR}/pgtopo_import_$$
+mkdir -p ${WORKDIR}
+
+cleanup() {
+ echo "Cleaning up" >&2
+ rm -rf ${WORKDIR}
+}
+
+trap 'cleanup' 0
+
+#################################################
+# Uncompress the dumpfile
+#################################################
+tar xzf ${DUMPFILE} -C ${WORKDIR} >&2
+DUMPDIR="${WORKDIR}/pgtopo_export"
+test -d "${DUMPDIR}" || {
+ echo "Missing 'pgtopo_export' dir in export file zip" >&2
+ exit 1
+}
+
+#################################################
+# Verify version
+#################################################
+
+DUMPVERSION=$(cat ${DUMPDIR}/pgtopo_dump_version)
+test ${DUMPVERSION} -eq '1' || {
+ echo "Unknown pgtopo dump version ${DUMPVERSION}" >&2
+ exit 1
+}
+
+#################################################
+# Start a transaction
+#################################################
+
+echo "BEGIN;"
+
+if test "$ONLY_LAYERS" = "no"; then # {
+ #################################################
+ # Create topology
+ #################################################
+
+ TOPO_SRID=$(cut -f1 ${DUMPDIR}/topology)
+ TOPO_PREC=$(cut -f2 ${DUMPDIR}/topology)
+ TOPO_HASZ=$(cut -f3 ${DUMPDIR}/topology)
+ cat<<EOF
+ SELECT topology.CreateTopology(
+ '${TOPONAME}',
+ '${TOPO_SRID}',
+ '${TOPO_PREC}',
+ '${TOPO_HASZ}'
+ );
+EOF
+
+ #################################################
+ # Load primitives data
+ #################################################
+
+ # Load faces
+ cat <<EOF
+ COPY "${TOPONAME}".face (
+ face_id,
+ mbr
+ ) FROM STDIN;
+EOF
+ cat ${DUMPDIR}/face
+ echo "\\."
+
+ # Load nodes
+ cat <<EOF
+ COPY "${TOPONAME}".node (
+ node_id,
+ geom,
+ containing_face
+ ) FROM STDIN;
+EOF
+ cat ${DUMPDIR}/node
+ echo "\\."
+
+ # Load edges
+ cat <<EOF
+ COPY "${TOPONAME}".edge_data (
+ edge_id,
+ start_node,
+ end_node,
+ next_left_edge,
+ abs_next_left_edge,
+ next_right_edge,
+ abs_next_right_edge,
+ left_face,
+ right_face,
+ geom
+ ) FROM STDIN;
+EOF
+ cat ${DUMPDIR}/edge_data
+ echo "\\."
+
+ #################################################
+ # Set id sequences of primitive tables
+ #################################################
+ cat <<EOF
+ SELECT pg_catalog.setval(
+ '${TOPONAME}.node_node_id_seq',
+ max(node_id),
+ true
+ )
+ FROM "${TOPONAME}".node;
+
+ SELECT pg_catalog.setval(
+ '${TOPONAME}.edge_data_edge_id_seq',
+ max(edge_id),
+ true
+ )
+ FROM "${TOPONAME}".edge_data;
+
+ SELECT pg_catalog.setval(
+ '${TOPONAME}.face_face_id_seq',
+ NULLIF(max(face_id), 0),
+ true
+ )
+ FROM "${TOPONAME}".face;
+EOF
+
+fi # } ONLY_LAYERS != "no"
+
+if test "$SKIP_LAYERS" = "no"; then # {
+
+ #################################################
+ # Create schemas of layer tables, if any
+ #################################################
+
+ for sch in $(cut -f2 ${DUMPDIR}/layers | sort -u); do
+ echo "CREATE SCHEMA IF NOT EXISTS \"${sch}\";"
+ done
+
+ #################################################
+ # Restore all layer tables
+ #################################################
+
+ if test -f ${DUMPDIR}/layers.dump; then
+ pg_restore -x -O -f - ${DUMPDIR}/layers.dump
+ fi
+
+ #####################################################
+ # Register topological layers
+ #####################################################
+
+ exec 3< ${DUMPDIR}/layers
+ while read -r line <&3; do
+ #echo "X LINE: ${line}" >&2
+ id=$(echo "${line}" | cut -f1)
+ schema=$(echo "${line}" | cut -f2)
+ table=$(echo "${line}" | cut -f3)
+ column=$(echo "${line}" | cut -f4)
+ typ=$(echo "${line}" | cut -f5)
+ level=$(echo "${line}" | cut -f6)
+ child=$(echo "${line}" | cut -f7)
+ #echo "X CHILD: ${child}" >&2
+ if test "${child}" = '\N'; then
+ child="null"
+ fi
+ #echo "CHILD: ${child}" >&2
+
+ cat <<EOF
+
+DO \$BODY\$
+DECLARE
+ t topology.topology;
+BEGIN
+ SELECT * FROM topology.topology
+ WHERE name = '${TOPONAME}'
+ INTO STRICT t;
+
+ -- Register layer in topology.layer table
+ INSERT INTO topology.layer VALUES (
+ t.id,
+ ${id},
+ '${schema}',
+ '${table}',
+ '${column}',
+ ${typ},
+ ${level},
+ ${child}
+ );
+
+ -- Create and initialize layer's topogeometry sequence
+ CREATE SEQUENCE "${TOPONAME}".topogeo_s_${id};
+ PERFORM pg_catalog.setval(
+ '${TOPONAME}.topogeo_s_${id}',
+ max(id("${column}")),
+ true
+ )
+ FROM "${schema}"."${table}";
+
+ -- drop-constraint (should we not restore them at all instead?)
+ ALTER TABLE "${schema}"."${table}"
+ DROP CONSTRAINT IF EXISTS "check_topogeom_${column}";
+
+ -- update topology_id
+ UPDATE "${schema}"."${table}"
+ SET "${column}".topology_id = t.id;
+
+ -- re-create constraint (skip this?)
+ EXECUTE format(
+ \$STATEMENT\$
+ ALTER TABLE "${schema}"."${table}"
+ ADD CONSTRAINT "check_topogeom_${column}"
+ CHECK (
+ topology_id("${column}") = %1\$L
+ AND
+ layer_id("${column}") = ${id}
+ AND
+ type("${column}") = ${typ}
+ )
+ \$STATEMENT\$,
+ t.id
+ );
+END;
+\$BODY\$ LANGUAGE 'plpgsql';
+
+EOF
+ done;
+
+ #################################################
+ # Update layer_id sequence
+ #################################################
+ cat <<EOF
+ SELECT pg_catalog.setval(
+ '${TOPONAME}.layer_id_seq',
+ max(l.layer_id),
+ true
+ )
+ FROM topology.layer l, topology.topology t
+ WHERE l.topology_id = t.id AND
+ t.name = '${TOPONAME}';
+EOF
+
+
+ #################################################
+ # Load relation (after creating layers)
+ #################################################
+
+ for tab in relation; do
+ cat <<EOF
+ COPY "${TOPONAME}".${tab} FROM STDIN;
+EOF
+ cat ${DUMPDIR}/${tab}
+ echo "\\."
+ done
+
+fi # } SKIP_LAYERS != "no"
+
+
+#################################################
+# Commit all
+#################################################
+
+echo "COMMIT;"
-----------------------------------------------------------------------
Summary of changes:
.editorconfig | 4 +
NEWS | 1 +
topology/loader/pgtopo_export.sh | 209 ++++++++++++++++++++++++++
topology/loader/pgtopo_import.sh | 314 +++++++++++++++++++++++++++++++++++++++
4 files changed, 528 insertions(+)
create mode 100755 topology/loader/pgtopo_export.sh
create mode 100755 topology/loader/pgtopo_import.sh
hooks/post-receive
--
PostGIS
More information about the postgis-tickets
mailing list