Opened 11 years ago

Closed 9 years ago

#2737 closed patch (fixed)

[PATCH] Upgrade of spatial_ref_sys.sql to EPSG v8.5

Reported by: rouault Owned by: robe
Priority: high Milestone: PostGIS 2.2.0
Component: postgis Version: master
Keywords: history Cc: warmerdam

Description

Attached an updated version of spatial_ref_sys.sql done with the http://svn.osgeo.org/metacrs/geotiff/trunk/libgeotiff/csv/README process. The update in libgeotiff, GDAL and proj has just been made : http://trac.osgeo.org/gdal/ticket/5462

In addition to the usual changes/additions of PCS/GCS, I've also added in the .sql the SRS of type GEOCCS (Geocentric) and COMPD_CS (Compound horizontal + vertical), consistently with PROJ.4 proj file.

Attachments (4)

spatial_ref_sys.sql (4.3 MB ) - added by rouault 11 years ago.
Updated version to v8.4
spatial_ref_sys_override_datum.sql (4.3 MB ) - added by rouault 10 years ago.
DO NOT USE: just to compare with previous version (+datum expanded as +ellps +towgs84)
spatial_ref_sys_8.5.zip (258.4 KB ) - added by warmerdam 10 years ago.
Slightly fixed, matchines libgeotiff 1.4.1RC1
spatial_ref_sys_8.5_updated.zip (259.1 KB ) - added by rouault 10 years ago.
Updated version that takes into account fix for loss of precision on TOWGS84 (https://trac.osgeo.org/proj/ticket/260)

Change History (24)

by rouault, 11 years ago

Attachment: spatial_ref_sys.sql added

Updated version to v8.4

comment:1 by robe, 11 years ago

Milestone: PostGIS 2.2.0
Owner: changed from pramsey to robe

comment:2 by robe, 10 years ago

Type: enhancementpatch

comment:3 by robe, 10 years ago

roault, There are quite a few differences with this spatial_ref_sys and what we have in 2.1. Yours looks closer to the 1.5 spatial_ref_sys. I'm not sure if its a difference in representation or one is more right than the other

pramsey,

Can you tell me why you took out all the DATUM tags and replaced with +towgs84 settings? Are these two things kinda equivalent?

So to be clear what I am talking about — this is one thing someone complained about in our book on one of our examples.

For srid=4269

roualts has this which matches our 1.5 definition

+proj=longlat +datum=NAD83 +no_defs 

and we currently have in our 2.1,2.1 (I think possibly 2.0 as well don't recall when the last spatial_ref_sys was loaded)

+proj=longlat +ellps=GRS80 +towgs84=0,0,0,0,0,0,0 +no_defs 

There are 713 records such as this that do not match and at a cursory glance it seems because the DATUM has been removed, though not always.

For example srid=4756

In roualt's:

+proj=longlat +ellps=WGS84 +towgs84=-192.873,-39.382,-111.202,-0.00205,-0.0005,0.00335,0.0188 +no_defs 

Our's is (but looking back at 1.5, its the same for 1.5 so I assume roault's is just a correction)

+proj=longlat +ellps=WGS84 +no_defs 

comment:4 by rouault, 10 years ago

Regina,

yes there has been some fluctuency in the method to deal with datum expension or not. Originally (postgis 1.5 time), datum names were passed. Then there was a change in OGR that expanded them, but GRASS folks were not happy with that for various reasons (but for reprojection matter, they are equivalent), so we backed that when generating proj EPSG and postgis spatial_ref_sys.sql by defining "—config OVERRIDE_PROJ_DATUM_WITH_TOWGS84 NO" in the epsg_tr.py script that generates those files from GDAL SRS database.

For EPSG:4756, the definition with the towgs84 is certainly better than the one that doesn't have it. Some time ago FrankW added improvements to get the preferred datum shift parameters.

All in all, I believe the updated definition is better or equivalent than the previous ones.

comment:5 by robe, 10 years ago

Keywords: history added
Resolution: fixed
Status: newclosed
Version: trunk

sounds good to me. pramsey can cleanup if he has issue.

Committed at r12622

comment:6 by robe, 10 years ago

Resolution: fixed
Status: closedreopened

comment:7 by robe, 10 years ago

reopening. Just realized this is missing the defunct 900913 which a lot of people still rely on though its defunct. I guess I should put that back in. That seems to be the only one missing of the original set. I also have to upgrade the extension backup config to exclude these from backup.

comment:8 by robe, 10 years ago

Resolution: fixed
Status: reopenedclosed

amended at r12623

comment:9 by robe, 10 years ago

and r12624 . I'll need to test backup and restore before release.

comment:10 by pramsey, 10 years ago

Resolution: fixed
Status: closedreopened

As you already noted, there are things in our file that don't come through the automatic GDAL process, 900913 is only one of them. The manual changes to srs.sql need to be checked against the incoming replacement. Some have been fixed in upstream, some have not, and unfortunately it's just slogging through about an hour of checking to ensure the update doesn't loose prior patches.

comment:11 by rouault, 10 years ago

Perhaps some of those customizations could be upstreamed (in libgeotiff) to avoid such merging ? Ideally customizations should be common to the libgeotiff/Proj/GDAL/QGIS/PostGIS stack for consistency

comment:12 by pramsey, 10 years ago

Many of them *have* been upstreamed, but it's still contingent on us to check and make sure nothing has been lost in translation. And there's stuff (EPSG:900913) which I think upstream has rejected. ???

comment:13 by rouault, 10 years ago

Regarding EPSG:900913, technically this is not an EPSG code. In GDAL, it is treated as an extra code added to http://svn.osgeo.org/gdal/trunk/gdal/data/cubewerx_extra.wkt. The http://svn.osgeo.org/gdal/trunk/gdal/swig/python/scripts/epsg_tr.py script iterates over gcs.csv and pcs.csv, so doesn't find it. We could still potentially add a hack in it to add it in PostGIS spatial_ref_sys.sql

Hum looking at the WKT for 900913 in spatial_ref_sys.sql and cubewerx_extra.wkt, I can see there are not identical. In particular PostGIS adds a AUTHORITY["EPSG","3785"] node to the WKT… And many other differences such as Mercator_2SP vs Mercator_1SP. The only thing common is the proj.4 expansion.

comment:14 by robe, 10 years ago

Do we really care about anything else but the proj4 expansion and the srid? I suspect ours got inherited a while back from spatialreference.org since that is what it has listed as the authority. But I agree there should definitely be consistency to reduce headaches with people transforming using different products.

by rouault, 10 years ago

DO NOT USE: just to compare with previous version (+datum expanded as +ellps +towgs84)

comment:15 by robe, 10 years ago

pramsey so back to DATUM times? I'm sold by Even't comment

""+datum=foo" is more expressive however and enables GRASS to propose a U.I where users can select a range of possible datum shifts when several can apply"

Though I guess that means we are more reliant on datum shift files which could be different for some reason so harder to debug.

I compared what EvenR just attached and down to 343 differences from 713 before. A lot of the differences seem to be because our file is a mishmush of towgs84 expansion and datum (not sure how that happened) so guess I have to do a 3 way compare and then compare to our logs what's left.

The 1000 someodd additional that are in Even's file, I'm just going to blindly accept :)

comment:16 by warmerdam, 10 years ago

Cc: warmerdam added

I have attached spatial_ref_sys_8.5.zip, which is an EPSG 8.5 derived spatial_ref_sys.sql file. I haven't reviewed it too carefully so it might be worth a quick compare to Even's product as a check.

by warmerdam, 10 years ago

Attachment: spatial_ref_sys_8.5.zip added

Slightly fixed, matchines libgeotiff 1.4.1RC1

comment:17 by robe, 10 years ago

Summary: [PATCH] Upgrade of spatial_ref_sys.sql to EPSG v8.4[PATCH] Upgrade of spatial_ref_sys.sql to EPSG v8.5

by rouault, 10 years ago

Updated version that takes into account fix for loss of precision on TOWGS84 (https://trac.osgeo.org/proj/ticket/260)

comment:18 by rouault, 10 years ago

I've attached spatial_ref_sys_8.5_updated.zip that contains an updated version of spatial_ref_sys.sql regenerated with GDAL after http://trac.osgeo.org/gdal/changeset/28536 that fixes a loss of precision on the values of +towgs84, as reported in https://trac.osgeo.org/proj/ticket/260

comment:19 by robe, 10 years ago

Priority: mediumhigh

comment:20 by pramsey, 9 years ago

Resolution: fixed
Status: reopenedclosed

I have applied all the manual changes from past history back to the spatial_ref_sys Even has provided, r13843 and r13844.

Note: See TracTickets for help on using tickets.