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

[Chris Tooley]

Howdy!

Pardon any mailing list _faux pas_ here, it's been a while.

It was suggested on IRC that I detail my experience on the mailing list
with the `spatial_sys_ref` table, specifically with this thread. Please
forgive me if there is any incorrect terminology, I am not a GIS expert, I
am but a humble software developer.

We are using postgresql12 with postgis 3.2.1
I notice this is requested:
> SELECT version() || ' ' || postgis_full_version();
> PostgreSQL 12.10 (Ubuntu 12.10-1.pgdg20.04+1) on x86_64-pc-linux-gnu,
compiled by gcc (Ubuntu 9.3.0-17ubuntu1~20.04) 9.3.0, 64-bit POSTGIS="3.2.1
5fae8e5" [EXTENSION] PGSQL="120" GEOS="3.9.1-CAPI-1.14.2" PROJ="6.3.1"
GDAL="GDAL 3.0.4, released 2020/01/28 GDAL_DATA not found" LIBXML="2.9.10"
LIBJSON="0.13.1" LIBPROTOBUF="1.3.3" WAGYU="0.5.0 (Internal)" RASTER"

I had to initially install a specific version of postgis (3.1.2) because it
was requested by a company we are working with. We ended up installing 3.2
anyway because we encountered some other unrelated issues (maybe they're
related! I'm not sure). This may be the reason we were having issues too.

In any case, we were trying to convert from EPSG 3310 to 4326 using the
following query (slightly edited)

> select ST_Transform(geom, 4326) from data.table limit 1;

and were encountering the following error:

> ERROR:  could not form projection (LWPROJ) from 'srid=3310' to 'srid=4326'

This baffled me and results from searching on google weren't immediately
helpful. I looked through many different pages and even looked at the
epsg.io 3310 page (https://epsg.io/3310) to see if there was something I
could glean from EPSG 3310 that might yield some results. As I said
previously, I am not a GIS expert so that was semi-fruitless. I did notice
at the bottom of epsg.io that there are a number of queries/etc for proj4
and other gis stuff, including a postGIS sql query I could run, which gave
me some indication of how the transforms are handled, however, I didn't
want to overwrite anything in the table because I figured the default stuff
knows how to handle itself better than I.

I ended up finding a comment in a gis stackexchange which talked about
modifying the proj4text in the spatial_sys_ref table to solve their
particular issue which sounded like what I was encountering, 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;

Lo and behold that seemed to allow the original failing query to work. I'm
not entirely sure if it's actually transforming correctly, but at least the
query isn't failing now! This is currently sufficient for our purposes
regardless.

Hopefully this helps someone else in the future! Future person who may be
reading this, I salute your efforts!

Sincerely,
Chris
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20220315/2e81cc41/attachment.html>