1

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

| improve this question | |
  • small note: the 'old' Oracle SRID for WGS84 is 8307, not 8037 (wanted to edit it in the post but edits have to be at least 6 chars...) – Tom De Leu Oct 20 '14 at 9:32
  • Good catch, I am sometimes numbers-dyslectic (well at least I got all the digits right, right?), fixed it. In my answer I did type it correctly, in my defense :) – nathanvda Oct 20 '14 at 11:41
  • no problem :) yeah I noticed it in the answer :) – Tom De Leu Oct 20 '14 at 11:48
5

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.

| improve this 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...)

| improve this answer | |
  • I tested on 11g Enterprise Edition – Tom De Leu Oct 20 '14 at 11:51
1

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

| improve this answer | |
  • 1
    Unfortunately, they do not provide ready-to-use CRS definitions, and keep datum shifts in separated SRIDs. Those datum shifts still have to be transformed into the +towgs84-parameters. – AndreJ Mar 16 '13 at 18:34
  • The Oracle SRID's don't exist there either. – nathanvda Mar 17 '13 at 14:37
1

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.