[postgis-users] Help shaping the future: how do your use of spatial_ref_sys ?

[Chris Tooley]

Hi Sandro! Thank you for the reply!

On Wed, Mar 16, 2022 at 3:18 AM Sandro Santilli <strk at kbt.io> wrote:

> Thanks Chris for your experience, comment below
>
> On Tue, Mar 15, 2022 at 08:27:37PM -0700, Chris Tooley wrote:
>
> > so I made sure
> > I copied the original `proj4text` string and bit the bullet and modified
> > the table with the following query:
> >
> > > update spatial_ref_sys set proj4text = '+proj=aea +lat_1=34 +lat_2=40.5
> > +lat_0=0 +lon_0=-120 +x_0=0 +y_0=-4000000 +ellps=GRS80
> > +towgs84=0,0,0,0,0,0,0 +units=m +no_defs' where srid=3310;
>
> So what you did was modifying what we call a "system entry"
> (srid=3310) to fix a (possible) bug you found in PostGIS itself.
>
> Now I guess you'll want YOUR version of the proj4text value to survive
> upgrades.
>
> At the moment PostGIS soft upgrades are NOT going to revert your change,
> but someone considers it a bug:
>
>     https://trac.osgeo.org/postgis/ticket/5024
>
> Your experience suggests it is NOT necessarely a bug, so a comment in
> that ticket might be good to have too.
>

True! As an aside, I have verified that ST_Transform works with the
original spatial_ref_sys proj4text with a fresh install in a different
machine, so it is indeed something localized I did with my install. I
notice that someone already commented about this in the ticket so I figure
I don't need to but I can if that helps in some way too.


> Still, PostGIS *hard* upgrades (implying dump/restore) will instead
> get rid of your updates because srid=3310 is "BETWEEN 3174 AND 3791"
> as found in `extcondition` column of pg_extension for extname='postgis'
> and thus the record will NOT be included in the dump.
>

I wouldn't be super bent out of shape with that, as long as the transform
can perform its duties properly! My use case is primarily in trying to
solve why ST_Transform wouldn't work for 3310 to 4236. I'm now certain it
was an issue with my own mucking about with the system, mostly likely not
much to do with postgis nor with gdal nor proj.


> This discrepancy between hard and soft upgrades is what I'd like to
> see fixed with a new simplified method of dealing with upgrades.
>
> The solution I suggested was to keep separate tables for "system entries"
> and "user entries", so that "user entries" would always "shadow"
> "system entries" (allowing overrides) and would always be carried between
> upgrades.
>

This would work for my use case, for sure, especially if I could just wipe
out my user settings and start fresh from a "known good".

As an addendum, which may be useful for someone in the future: I had
originally installed postgis from ubuntugis repo, with postgresql 12 from
the pgdg repo - I suspect this was the totality of my issue.

I have just had some time to spend on it to solve this issue and I fixed it
by removing all extraneous and non-pgdg apt repos in /etc/apt (sources.list
and also in sources.list.d). Of course I also left the standard official
ubuntu sources. After removing those apt sources I found the "offending"
apt packages by running:

               apt list --installed | grep installed,local

Then removing the packages that were listed there (likely not complete, I
was removing other things previously):

              apt remove gdal-data libgeos-3.9.1 proj-data libgeos-c1v5

This meant I had to reinstall postgresql-12-postgis-3 which is fine - and I
did so from the pgdg sources:

             apt install postgresql-12-postgis-3

Finally I reverted the proj4text back to the original value in psql:

              update spatial_ref_sys set proj4text = '+proj=aea +lat_1=34
+lat_2=40.5 +lat_0=0 +lon_0=-120 +x_0=0 +y_0=-4000000 +datum=NAD83 +units=m
+no_defs' where srid=3310;

I confirmed that this is working correctly and as expected with another
"known good" server.

Thanks, everyone - hope this helps someone in the future!
-Chris
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20220412/c7e6bcc2/attachment.html>