<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>