[postgis-devel] PSC Vote: Make spatial_ref_sys as a view

Mon Feb 7 14:35:27 PST 2022

> On Feb 7, 2022, at 2:27 PM, Sandro Santilli <strk at kbt.io> wrote:
> 
> On Mon, Feb 07, 2022 at 02:05:20PM -0800, Paul Ramsey wrote:
>> I still feel like we're doing heart surgery here for a very thinly populated user population. I would still just rather not do this. I am not seeing the outcry and user demand for this, and it's not a "use it if you want it, ignore it if you don't" kind of a thing. 
>> 
>> Anyways, I wanted to at least try it, so I got the branch and did an upgrade. 
>> 
>>  createdb srs  
>>  psql srs
>>  -- old version
>>  create extension postgis version '3.2.1dev';
>>  -- user-generated srs
>>  insert into spatial_ref_sys select 200000 as srid, auth_name, auth_srid, srtext, proj4text from spatial_ref_sys where srid = 900913;
>> 
>>  -- master built from spatial-ref-sys-view branch
>>  alter extension postgis update to '3.3.0dev';
>> 
>>  select srid from spatial_ref_sys_user;
>> 
>>     4893
>>     6319
>>     6782
>>   200000
>> 
>> The upgrade proceeded silently and did drop my srs into the right place, very nice. I'm a bit baffled why it picked up those others though, and they are also in the system table.
> 
> I'm afraid we need to be more specific about which version of 3.2.1dev
> you got there, please include output of postgis_full_version().
> 
> Here's me running your own test:
> 
>  =# create extension postgis version '3.2.1dev';
>  CREATE EXTENSION
> 
>  =# select postgis_scripts_installed();
>    postgis_scripts_installed
>  ------------------------------
>   3.2.1dev 3.2.0-28-g4443d6989
>  (1 row)
> 
>  =# insert into spatial_ref_sys select 200000 as srid, auth_name, auth_srid, srtext, proj4text from spatial_ref_sys where srid = 900913;
>  INSERT 0 1
> 
>  =# alter extension postgis update to '3.3.0dev';
>  ALTER EXTENSION
> 
>  =# select postgis_scripts_installed();
>  postgis_perf=# select postgis_scripts_installed();
>     postgis_scripts_installed
>  -------------------------------
>   3.3.0dev 3.2.0-506-g3fe3da966
>  (1 row)
> 
>  =# select srid from spatial_ref_sys_user;
>    srid
>  --------
>   200000
>  (1 row)
> 
> The upgrade script will keep records that are not fully equal between
> the system and the user installs, so could it be your 3.2.1dev version
> had 4893, 6319 and 6782 records having different values ?
> 
> Check out the output of postgis_full_version(), it should tell you
> that you have 3 SRID overrides, as a warning.

 POSTGIS="3.2.1dev 3.2.0-339-gafaebf921" [EXTENSION] PGSQL="140" GEOS="3.11.0dev-CAPI-1.16.0" PROJ="8.1.0" LIBXML="2.9.4" LIBJSON="0.15" LIBPROTOBUF="1.4.0" WAGYU="0.5.0 (Internal)"

The system and user versions of those srs's *seem* to be the same?

srs=# select * from spatial_ref_sys_user where srid = 4893;

srid      | 4893
auth_name | EPSG
auth_srid | 4893
srtext    | BOUNDCRS[SOURCECRS[GEOGCRS["NAD83(NSRS2007)",DATUM["NAD83 (National Spatial Reference System 2007)",ELLIPSOID["GRS 1980",6378137,298.257222101,LENGTHUNIT["metre",1]]],PRIMEM["Greenwich",0,ANGLEUNIT["degree",0.0174532925199433]],CS[ellipsoidal,3],AXIS["geodetic latitude (Lat)",north,ORDER[1],ANGLEUNIT["degree",0.0174532925199433]],AXIS["geodetic longitude (Lon)",east,ORDER[2],ANGLEUNIT["degree",0.0174532925199433]],AXIS["ellipsoidal height (h)",up,ORDER[3],LENGTHUNIT["metre",1]],USAGE[SCOPE["unknown"],AREA["USA - CONUS and Alaska; PRVI"],BBOX[14.92,167.65,74.71,-63.88]],ID["EPSG",4893]]],TARGETCRS[GEOGCRS["WGS 84",DATUM["World Geodetic System 1984",ELLIPSOID["WGS 84",6378137,298.257223563,LENGTHUNIT["metre",1]]],PRIMEM["Greenwich",0,ANGLEUNIT["degree",0.0174532925199433]],CS[ellipsoidal,2],AXIS["latitude",north,ORDER[1],ANGLEUNIT["degree",0.0174532925199433]],AXIS["longitude",east,ORDER[2],ANGLEUNIT["degree",0.0174532925199433]],ID["EPSG",4326]]],ABRIDGEDTRANSFORMATION["NAD83(NSRS2007) to WGS 84 (1)",VERSION["OGP-USA conus AK"],METHOD["Geocentric translations (geog2D domain)",ID["EPSG",9603]],PARAMETER["X-axis translation",0,ID["EPSG",8605]],PARAMETER["Y-axis translation",0,ID["EPSG",8606]],PARAMETER["Z-axis translation",0,ID["EPSG",8607]],USAGE[SCOPE["unknown"],AREA["USA - CONUS and Alaska; PRVI"],BBOX[14.92,167.65,74.71,-63.88]],ID["DERIVED_FROM(EPSG)",15931]]]
proj4text | +proj=longlat +ellps=GRS80 +towgs84=0,0,0,0,0,0,0 +no_defs

srs=# select * from spatial_ref_sys_postgis where srid = 4893;
srid      | 4893
auth_name | EPSG
auth_srid | 4893
srtext    | BOUNDCRS[SOURCECRS[GEOGCRS["NAD83(NSRS2007)",DATUM["NAD83 (National Spatial Reference System 2007)",ELLIPSOID["GRS 1980",6378137,298.257222101,LENGTHUNIT["metre",1]]],PRIMEM["Greenwich",0,ANGLEUNIT["degree",0.0174532925199433]],CS[ellipsoidal,3],AXIS["geodetic latitude (Lat)",north,ORDER[1],ANGLEUNIT["degree",0.0174532925199433]],AXIS["geodetic longitude (Lon)",east,ORDER[2],ANGLEUNIT["degree",0.0174532925199433]],AXIS["ellipsoidal height (h)",up,ORDER[3],LENGTHUNIT["metre",1]],USAGE[SCOPE["unknown"],AREA["USA - CONUS and Alaska, PRVI"],BBOX[14.92,167.65,74.71,-63.88]],ID["EPSG",4893]]],TARGETCRS[GEOGCRS["WGS 84",DATUM["World Geodetic System 1984",ELLIPSOID["WGS 84",6378137,298.257223563,LENGTHUNIT["metre",1]]],PRIMEM["Greenwich",0,ANGLEUNIT["degree",0.0174532925199433]],CS[ellipsoidal,2],AXIS["latitude",north,ORDER[1],ANGLEUNIT["degree",0.0174532925199433]],AXIS["longitude",east,ORDER[2],ANGLEUNIT["degree",0.0174532925199433]],ID["EPSG",4326]]],ABRIDGEDTRANSFORMATION["NAD83(NSRS2007) to WGS 84 (1)",VERSION["OGP-USA conus AK"],METHOD["Geocentric translations (geog2D domain)",ID["EPSG",9603]],PARAMETER["X-axis translation",0,ID["EPSG",8605]],PARAMETER["Y-axis translation",0,ID["EPSG",8606]],PARAMETER["Z-axis translation",0,ID["EPSG",8607]],USAGE[SCOPE["unknown"],AREA["USA - CONUS and Alaska, PRVI"],BBOX[14.92,167.65,74.71,-63.88]],ID["DERIVED_FROM(EPSG)",15931]]]
proj4text | +proj=longlat +ellps=GRS80 +towgs84=0,0,0,0,0,0,0 +no_defs

But I "see" now they are in fact different. There's two one-character differences in the SRTEXT.

So mystery solved.

P

> 
> --strk;
> 
>  Libre GIS consultant/developer
>  https://strk.kbt.io/services.html
> _______________________________________________
> postgis-devel mailing list
> postgis-devel at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-devel