[postgis-tickets] [SCM] PostGIS branch spatial-ref-sys-view updated. 3.2.0-484-g7306f765a
git at osgeo.org
git at osgeo.org
Fri Feb 4 08:16:29 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, spatial-ref-sys-view has been updated
via 7306f765ac14f1d185afb2815708c0ccc7436764 (commit)
from b1e20a73d0d01b07e46a39f26ba8a52347191204 (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 7306f765ac14f1d185afb2815708c0ccc7436764
Author: Sandro Santilli <strk at kbt.io>
Date: Fri Feb 4 17:15:56 2022 +0100
Have spatial_ref_sys.sql drop user entries matching system ones
Reduces amount of overrides upon upgrade
diff --git a/regress/hooks/hook-after-upgrade.sql b/regress/hooks/hook-after-upgrade.sql
index b69bf44d5..e0be27b27 100644
--- a/regress/hooks/hook-after-upgrade.sql
+++ b/regress/hooks/hook-after-upgrade.sql
@@ -24,8 +24,8 @@ DROP FUNCTION IF EXISTS st_dwithin_deprecated_by_postgis_300(geography,geography
DROP FUNCTION IF EXISTS st_dwithin_deprecated_by_postgis_300(text,text,float8);
DROP FUNCTION IF EXISTS st_clusterkmeans_deprecated_by_postgis_302(geometry,integer);
--- Delete all custom SRIDs
+-- Delete ONLY the custom SRIDs
-- TODO: do something smarter like making a copy of
-- them somewhere for tests to run some tests
-- on them...
-DELETE FROM spatial_ref_sys_user;
+DELETE FROM spatial_ref_sys_user WHERE SRID = 909090;
diff --git a/regress/hooks/hook-before-upgrade.sql b/regress/hooks/hook-before-upgrade.sql
index 046d5c730..7b3a7c3a3 100644
--- a/regress/hooks/hook-before-upgrade.sql
+++ b/regress/hooks/hook-before-upgrade.sql
@@ -88,5 +88,15 @@ UPDATE pg_proc SET probin = probin || '-uninstalled'
WHERE probin like '%postgis%';
-- Insert a custom SRID to spatial_ref_sys
+-- NOTE: we're assuming 909090 never existed as a system srid
INSERT INTO spatial_ref_sys(srid,auth_name,auth_srid,srtext,proj4text)
-VALUES (900100, 'pgis_test', 2, 'srtext', 'prjtext');
+VALUES (909090, 'pgis_test', 2, 'srtext', 'prjtext');
+-- Insert a SRID which is known to be in current postgis version
+-- (and ideally not available in older version)
+WITH recent_entry ("srid","auth_name","auth_srid","srtext","proj4text")
+AS (
+ VALUES (103971,'ESRI',103971,'PROJCS["NAD_1983_HARN_Adj_WI_Wood_Feet (deprecated)",GEOGCS["GCS_NAD_1983_HARN_Adj_WI_Wood (deprecated)",DATUM["D_NAD_1983_HARN_Adj_WI_WD",SPHEROID["GRS_1980_Adj_WI_WD",6378437.651,298.271281273316,AUTHORITY["ESRI","107850"]],AUTHORITY["ESRI","106850"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.0174532925199433,AUTHORITY["EPSG","9122"]],AUTHORITY["ESRI","104871"]],PROJECTION["Lambert_Conformal_Conic_2SP"],PARAMETER["latitude_of_origin",43.1513888888889],PARAMETER["central_meridian",-90],PARAMETER["standard_parallel_1",44.1805555555556],PARAMETER["standard_parallel_2",44.5444444444444],PARAMETER["false_easting",684000],PARAMETER["false_northing",0],UNIT["US survey foot",0.304800609601219,AUTHORITY["EPSG","9003"]],AXIS["Easting",EAST],AXIS["Northing",NORTH],AUTHORITY["ESRI","103971"]]','+proj=lcc +lat_0=43.1513888888889 +lon_0=-90 +lat_1=44.1805555555556 +lat_2=44.5444444444444 +x_0=208483.616967234 +y_0=0 +a=6378437.651 +rf=298.2712
81273316 +units=us-ft +no_defs')
+)
+INSERT INTO "spatial_ref_sys" ("srid","auth_name","auth_srid","srtext","proj4text")
+SELECT * FROM recent_entry r
+WHERE NOT EXISTS (SELECT 1 FROM spatial_ref_sys WHERE srid = r.srid );
diff --git a/spatial_ref_sys.sql b/spatial_ref_sys.sql
index a35a533b9..0bf1b1ae0 100644
--- a/spatial_ref_sys.sql
+++ b/spatial_ref_sys.sql
@@ -34299,5 +34299,24 @@ INSERT INTO "spatial_ref_sys_postgis" ("srid","auth_name","auth_srid","srtext","
---
INSERT INTO "spatial_ref_sys_postgis" ("srid","auth_name","auth_srid","srtext","proj4text") VALUES (103971,'ESRI',103971,'PROJCS["NAD_1983_HARN_Adj_WI_Wood_Feet (deprecated)",GEOGCS["GCS_NAD_1983_HARN_Adj_WI_Wood (deprecated)",DATUM["D_NAD_1983_HARN_Adj_WI_WD",SPHEROID["GRS_1980_Adj_WI_WD",6378437.651,298.271281273316,AUTHORITY["ESRI","107850"]],AUTHORITY["ESRI","106850"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.0174532925199433,AUTHORITY["EPSG","9122"]],AUTHORITY["ESRI","104871"]],PROJECTION["Lambert_Conformal_Conic_2SP"],PARAMETER["latitude_of_origin",43.1513888888889],PARAMETER["central_meridian",-90],PARAMETER["standard_parallel_1",44.1805555555556],PARAMETER["standard_parallel_2",44.5444444444444],PARAMETER["false_easting",684000],PARAMETER["false_northing",0],UNIT["US survey foot",0.304800609601219,AUTHORITY["EPSG","9003"]],AXIS["Easting",EAST],AXIS["Northing",NORTH],AUTHORITY["ESRI","103971"]]','+proj=lcc +lat_0=43.1513888888889 +lon_0=-90 +lat_1=44.180
5555555556 +lat_2=44.5444444444444 +x_0=208483.616967234 +y_0=0 +a=6378437.651 +rf=298.271281273316 +units=us-ft +no_defs');
+-- Drop entries from user table not marked
+-- as "force_keep_on_upgrade" if an identical
+-- entry is found in the system table
+WITH equal_entries AS (
+ SELECT s.srid
+ FROM spatial_ref_sys_postgis s
+ JOIN spatial_ref_sys_user u
+ ON (
+ u.srid = s.srid AND
+ u.auth_name = s.auth_name AND
+ u.auth_srid = s.auth_srid AND
+ u.srtext = s.srtext AND
+ u.proj4text = s.proj4text
+ )
+)
+DELETE FROM spatial_ref_sys_user
+WHERE SRID IN ( SELECT srid FROM equal_entries )
+;
+
COMMIT;
ANALYZE spatial_ref_sys_postgis;
-----------------------------------------------------------------------
Summary of changes:
regress/hooks/hook-after-upgrade.sql | 4 ++--
regress/hooks/hook-before-upgrade.sql | 12 +++++++++++-
spatial_ref_sys.sql | 19 +++++++++++++++++++
3 files changed, 32 insertions(+), 3 deletions(-)
hooks/post-receive
--
PostGIS
More information about the postgis-tickets
mailing list