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

[Jim Klassen]

On 2/4/22 11:04, Sandro Santilli wrote:
>    - How many ?
Mainly one, but could potentially be up to every county in MN (87) as the country coordinate systems generally don't have EPSG codes. Also OGR tends to add entries automatically if it doesn't find a perfect match.  This is mostly a nuisance as generally there was a code that should have matched (i.e. it is the same, but didn't match based on how some other piece of software, probably incorrectly, encoded the WKT).

We've been starting at 200000 and using EPSG (misusing the "EPSG" auth kept Proj 4/GDAL/MapServer happier, this likely isn't necessary with Proj 8).  We maintain matching record(s) in our proj4 epsg database file (again this will change as we transition to Proj 8 which supports PROJ_AUX_DB).

INSERT INTO public.spatial_ref_sys VALUES (200068, 'EPSG', 200068, 'PROJCS["MN Ramsey County", GEOGCS["NAD83 (1986)", DATUM["GRS 1980 + MN Ramsey Elevation", SPHEROID["Unknown",6378418.941,298.2572242549219]], PRIMEM["Greenwich",0], UNIT["Degree",0.017453292519943295]], PROJECTION["Lambert_Conformal_Conic_2SP"], PARAMETER["standard_parallel_1",45.13333333333333], PARAMETER["standard_parallel_2",44.88333333333333], PARAMETER["latitude_of_origin",44.79111111111111], PARAMETER["central_meridian",-93.38333333333334], PARAMETER["false_easting",500000], PARAMETER["false_northing",100000], UNIT["Foot_US",0.30480060960121924]]', '+proj=lcc +lat_2=44.88333333333333 +lat_1=45.13333333333333 +lat_0=44.79111111111111 +lon_0=-93.38333333333334 +x_0=152400.3048 +y_0=30480.0610 +unit=sft +to_meter=0.30480060960122 +a=6378418.941 +b=6357033.310 +no_defs');

Note: MN county coordinate systems are a bit tricky because the +a and +b parameters are non-standard and were chosen to lessen the distortion over that county.  They aren't just the numbers from GRS80.  This is often overlooked.

Note: There are supposed to be EPSG codes for the new coordinate systems when the new datum goes into effect, but we will be dealing with old data for a long time.

>    - Do you also keep the entries installed by spatial_ref_sys.sql ?
Yes.
>    - Do you ever update the table with entries from newer
>      spatial_ref_sys.sql ?
No.  But, I also didn't realize that the system entries weren't getting updated automatically.