finding the definition of missing srid in postgis

Question

I am converting a spatial Oracle database to POSTGIS, and one of the problems I encounter is that we use SRID in Oracle that do not exist in POSTGIS.

I am talking about the following SRID:

• 90112 : dutch RD
• 327680: Belgian Lambert 72
• 8307: WGS84

The problem is that I do not know how to translate these SRID to POSTGIS, since they do not exist in spatial_ref_sys.

If I visit the spatial reference I do not find any reference to the SRID at all. I was able to find

• 31300: Belgium Lambert 1972
• 4326: WGS84

Are these SRID comparable? Where can I find definitions to add SRID if they are not defined on spatialreference.org? Are the Oracle SRID's standard or not?

A lot of questions, I hope you can shed some light here. Thanks :)

Answer 1

Postgis, spatialite and Qgis mainly use GDAL/Proj definitions with EPSG codes as ID for CRS. You find a lot of them at spatialreference.org, but that database is a bit outdated.

For Dutch RD, there is

EPSG:28991 Amersfoort/RD Old
EPSG:28992 Amersfoort/RD New

Oracle used their own SRID up to release 10.1, and switched to EPSG codes as of release 10.2 according to this document in German: http://www.geodbs.de/AlteBezugssysteme.pdf

Postgis uses the table spatial_ref_sys, which you can view with pgAdminIII, < your database>, scheme public, listed under tables.

Answer 2

Following functions exist in Oracle:

SDO_CS.MAP_EPSG_SRID_TO_ORACLE
SDO_CS.MAP_ORACLE_SRID_TO_EPSG

So

SELECT SDO_CS.MAP_ORACLE_SRID_TO_EPSG(xxx) FROM dual;

yields:

90112  -> null
327680 -> 31300
8307   -> 4326

(Weird that 90112 isn't mapped...)

Answer 3

Did you try http://www.epsg-registry.org/? You could get lucky over there.

Answer 4

With some help from a collegue, we ran the following query against the Oracle 10.2 database:

select srid,cs_name from mdsys.cs_srs where srid='90112' or srid='327680' or srid='8307'

Oracle SRIDs

8307

GEOGCS [ "Longitude / Latitude (WGS 84)", DATUM ["WGS 84", SPHEROID ["WGS 84", 6378137, 298.257223563]], PRIMEM [ "Greenwich", 0.000000 ], UNIT ["Decimal Degree", 0.01745329251994330]]

90112

PROJCS["Netherlands National System", GEOGCS [ "Netherlands Bessel", DATUM ["Netherlands Bessel", SPHEROID ["Bessel 1841", 6377397.155, 299.1528128]], PRIMEM [ "Greenwich", 0.000000 ], UNIT ["Decimal Degree", 0.01745329251994330]], PROJECTION ["Stereographic"], PARAMETER ["Scale_Factor", 0.999908], PARAMETER ["Central_Meridian", 5.387639], PARAMETER ["Latitude_Of_Origin", 52.156161], PARAMETER ["False_Easting", 155000.000000], PARAMETER ["False_Northing", 463000.000000], UNIT ["Meter", 1.000000000000]]

327680

PROJCS["Belgian National System (1972)", GEOGCS [ "Belgium Hayford", DATUM ["Belgium Hayford", SPHEROID ["International 1924", 6378388, 297],-99.059, 53.322, -112.486, -0.419, 0.83, -1.885, 0.999999], PRIMEM [ "Greenwich", 0.000000 ], UNIT ["Decimal Degree", 0.01745329251994330]], PROJECTION ["Lambert Conformal Conic (Belgium 1972)"], PARAMETER ["Standard_Parallel_1", 49.833333], PARAMETER ["Standard_Parallel_2", 51.166667], PARAMETER ["Central_Meridian", 4.356940], PARAMETER ["Latitude_Of_Origin", 90.000000], PARAMETER ["False_Easting", 150000.012560], PARAMETER ["False_Northing", 5400088.437800], UNIT ["Meter", 1.000000000000]]

Postgis equivalent SRIDs

And then comparing those to the EPSG WKT stored in spatialreference.org, we were able to find the following corresponding SRID for POSTGIS

4326 (for 8307)

GEOGCS["WGS 84",DATUM["WGS_1984",SPHEROID["WGS 84",6378137,298.257223563,AUTHORITY["EPSG","7030"]],AUTHORITY["EPSG","6326"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.01745329251994328,AUTHORITY["EPSG","9122"]],AUTHORITY["EPSG","4326"]]

28992 (for 90112)

PROJCS["Amersfoort / RD New",GEOGCS["Amersfoort",DATUM["Amersfoort",SPHEROID["Bessel 1841",6377397.155,299.1528128,AUTHORITY["EPSG","7004"]],AUTHORITY["EPSG","6289"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.01745329251994328,AUTHORITY["EPSG","9122"]],AUTHORITY["EPSG","4289"]],UNIT["metre",1,AUTHORITY["EPSG","9001"]],PROJECTION["Oblique_Stereographic"],PARAMETER["latitude_of_origin",52.15616055555555],PARAMETER["central_meridian",5.38763888888889],PARAMETER["scale_factor",0.9999079],PARAMETER["false_easting",155000],PARAMETER["false_northing",463000],AUTHORITY["EPSG","28992"],AXIS["X",EAST],AXIS["Y",NORTH]]

31300 (for 327680) :

PROJCS["Belge 1972 / Belge Lambert 72",GEOGCS["Belge 1972",DATUM["Reseau_National_Belge_1972",SPHEROID["International 1924",6378388,297,AUTHORITY["EPSG","7022"]],TOWGS84[106.869,-52.2978,103.724,-0.33657,0.456955,-1.84218,1],AUTHORITY["EPSG","6313"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.01745329251994328,AUTHORITY["EPSG","9122"]],AUTHORITY["EPSG","4313"]],UNIT["metre",1,AUTHORITY["EPSG","9001"]],PROJECTION["Lambert_Conformal_Conic_2SP_Belgium)"],PARAMETER["standard_parallel_1",49.83333333333334],PARAMETER["standard_parallel_2",51.16666666666666],PARAMETER["latitude_of_origin",90],PARAMETER["central_meridian",4.356939722222222],PARAMETER["false_easting",150000.01256],PARAMETER["false_northing",5400088.4378],AUTHORITY["EPSG","31300"],AXIS["X",EAST],AXIS["Y",NORTH]]

Conclusion

• Oracle SRID's are not generic (as Andre Joost said)
• to find the mapping, you can get the definition from the Oracle SRID from mdsys.cs_srs
• and search for the corresponding definition in spatialreference.org or spatial_ref_sys

Hope this helps somebody else as well.