Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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

share|improve this question
add comment

3 Answers

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.

share|improve this answer
add comment

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

share|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. –  Andre Joost Mar 16 '13 at 18:34
    
The Oracle SRID's don't exist there either. –  nathanvda Mar 17 '13 at 14:37
add comment
up vote 1 down vote accepted

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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