[prev in list] [next in list] [prev in thread] [next in thread]
List: postgis-users
Subject: Re: [postgis-users] Help shaping the future: how do your use of spatial_ref_sys ?
From: Chris Tooley <cetooley () gmail ! com>
Date: 2022-04-12 17:55:39
Message-ID: CAJriHim+EzEJwst=ZmSzjtqUuJLDc1xHvdG2AnP7cfhvnDeH1w () mail ! gmail ! com
[Download RAW message or body]
[Attachment #2 (multipart/alternative)]
Hi Sandro! Thank you for the reply!
On Wed, Mar 16, 2022 at 3:18 AM Sandro Santilli <strk@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
[Attachment #5 (text/html)]
<div dir="ltr"><div class="gmail_quote"><div class="gmail_attr">Hi Sandro! Thank you \
for the reply!<br></div><div dir="ltr" class="gmail_attr"><br></div><div dir="ltr" \
class="gmail_attr">On Wed, Mar 16, 2022 at 3:18 AM Sandro Santilli <<a \
href="mailto:strk@kbt.io" target="_blank">strk@kbt.io</a>> \
wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px \
0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">Thanks Chris for your \
experience, comment below<br> <br>
On Tue, Mar 15, 2022 at 08:27:37PM -0700, Chris Tooley wrote:<br>
<br>
> so I made sure<br>
> I copied the original `proj4text` string and bit the bullet and modified<br>
> the table with the following query:<br>
> <br>
> > update spatial_ref_sys set proj4text = '+proj=aea +lat_1=34 \
+lat_2=40.5<br> > +lat_0=0 +lon_0=-120 +x_0=0 +y_0=-4000000 +ellps=GRS80<br>
> +towgs84=0,0,0,0,0,0,0 +units=m +no_defs' where srid=3310;<br>
<br>
So what you did was modifying what we call a "system entry"<br>
(srid=3310) to fix a (possible) bug you found in PostGIS itself.<br>
<br>
Now I guess you'll want YOUR version of the proj4text value to survive \
upgrades.<br> <br>
At the moment PostGIS soft upgrades are NOT going to revert your change,<br>
but someone considers it a bug:<br>
<br>
<a href="https://trac.osgeo.org/postgis/ticket/5024" rel="noreferrer" \
target="_blank">https://trac.osgeo.org/postgis/ticket/5024</a><br> <br>
Your experience suggests it is NOT necessarely a bug, so a comment in<br>
that ticket might be good to have too.<br></blockquote><div><br></div><div>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.<br></div><div> <br></div><blockquote class="gmail_quote" style="margin:0px 0px \
0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"> Still, PostGIS \
*hard* upgrades (implying dump/restore) will instead<br> get rid of your updates \
because srid=3310 is "BETWEEN 3174 AND 3791"<br> as found in `extcondition` \
column of pg_extension for extname='postgis'<br> and thus the record will NOT \
be included in the dump.<br></blockquote><div><br></div><div>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.<br></div><div> <br></div><blockquote class="gmail_quote" style="margin:0px 0px \
0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"> This discrepancy \
between hard and soft upgrades is what I'd like to<br> see fixed with a new \
simplified method of dealing with upgrades.<br> <br>
The solution I suggested was to keep separate tables for "system \
entries"<br> and "user entries", so that "user entries" \
would always "shadow"<br> "system entries" (allowing overrides) \
and would always be carried between<br> \
upgrades.<br></blockquote><div><br></div><div>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".<br></div><div><br></div><div>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.</div><div><br></div><div>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: </div><div><br> \
apt list --installed | grep installed,local</div><div><br></div><div>Then removing \
the packages that were listed there (likely not complete, I was removing other things \
previously):<br> <br></div><div> \
apt remove gdal-data libgeos-3.9.1 proj-data \
libgeos-c1v5</div><div><br></div><div>This meant I had to reinstall \
postgresql-12-postgis-3 which is fine - and I did so from the pgdg \
sources:</div><div><br></div><div> apt install \
postgresql-12-postgis-3<br></div><div><br>Finally I reverted the proj4text back to \
the original value in psql:<br> <br></div><div> \
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;</div><div><br></div><div>I confirmed that this is working correctly and as \
expected with another "known good" \
server.<br></div><div><br></div><div>Thanks, everyone - hope this helps someone in \
the future!<br></div><div>-Chris<br></div></div></div>
_______________________________________________
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic