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 have a very specific problem. I have data crossing two coordinate-systems, more specifically: the Netherlands and Belgium.

Previously the data was stored in one table in Oracle, using the RD coordinate system. When showing the data from Belgium, it was converted using the Oracle command SDO_CS.TRANSFORM back to the Belgian coordinate system (Lambert 72) and all was well.

Now I am moving to geoserver, and rendering using openlayers, on top of openstreetmap or google maps background. I notice the Belgian data (which is stored in RD) is shifted. If I create a table with the original Belgian data (in Lambert 72 without transformation), it is rendered correctly.

So my first guess: the Oracle transformation from LB72->RD is wrong.

But if geoserver does the transformation it is correct (since it renders the original Lambert 72 data correctly on the google maps background).

So I imported the data from one table to Postgis, and do the transformation there (using ST_transform(geom, 4326) and I see it is even more wrong :)

So at this moment I have a problem:

  • I have data in two coordinate systems, which belong in one table since it is border-spanning (it is a pipeline)
  • the data is rendered correctly, by geoserver, if using the original coordinate system
  • it is not if the belgian data is "transformed" using database tools (SDO_CS.TRANSFORM or ST_TRANSFORM)

My question:

  • how can i correctly transform belgian data to a coordinate system that can be rendered correctly on top of google maps or openlayers (I was considering WGS84 or ETRS89, as a shared coordinate system for both my RD and Lambert72 data)
  • or alternatively: how can I store two coordinate-systems in one table? (it is not possible in Oracle, is it possible in Postgis?)
  • or alternatively: how does one generally handle data that spans several coordinate systems?

Any help is greatly appreciated :)

share|improve this question
    
Also, note that Google Maps and OSM use Web Mercator natively (EPSG:3857 / EPSG:900913 / EPSG:102100). –  msayler Aug 26 '13 at 16:07
    
Can you supply sample coordinate pairs in both systems? –  Andre Joost Aug 26 '13 at 17:42
    
@msayler I was aware of this, but I thought it was not the best coordinate system to store the data in. I assumed WGS84 or ETRS89 are better. Not really sure why :) –  nathanvda Aug 26 '13 at 20:31
    
@AndreJoost: sure, please clarify what you expect exactly. A lambert 72 point converted to WGS84? Or ... ? –  nathanvda Aug 26 '13 at 20:33
    
The same point, once in the original RD coordinates, and transformed to Lambert72 once by Oracle and once by Postgis. If you have WGS84 as well, it's welcomed. And the offset in metres please when rendered by Geoserver. –  Andre Joost Aug 27 '13 at 5:38
add comment

2 Answers

up vote 2 down vote accepted

It might be that Oracle uses a grid shift file for transforming the Amersfoort RD data, and this grid shift file will only have entrys for the Netherlands.

In Postgis, it is not possible to use different SRID in one table.

My best choice is to transform ALL the original RD data in postgis or QGIS to ETRS89 or WGS84. It uses fixed +towgs84 parameters, which are valid even outside the target area. Be sure if your data is RD old or RD new.

Please note that Belgian and Dutch CRS have different +towgs84 parametres, which might result in a small shift.

The EPSG database offers 3 different datum shifts for Belgian 72 to WGS84, and 4 for Amersfoort Datum. Proj4 and depending software (Geoserver,Postgis,QGIS) use only one of them, while Oracle might have taken another one.

QGIS lists even two Belgian Lambert 72 (EPSG:31300 and 31370), so you might try both.

share|improve this answer
    
Thanks. The weird thing remains if I render the original Lambert 72 data with geoserver, it is rendered correctly. I can verify this, because the pipeline leaves a very visible shade in the satellite imagery. If I transform the data using Postgis to wgs84 it is no longer rendered correctly. But I will try changing the coordinate systems (31300/31370), and maybe try qgis to transform it (if I get it installed on mac osx). –  nathanvda Aug 26 '13 at 19:02
    
When I did the conversion starting from 31370 it transforms correctly. Thank you for the tip. Confusing though: seemingly similar srid but they are not :) –  nathanvda Aug 27 '13 at 11:23
    
"In Postgis, it is not possible to use different SRID in one table." Yes you can s, You just need to define constraint differently. Also it may give problems with QGIS and other programs which read metadata –  simplexio Aug 27 '13 at 13:21
add comment

So, while the belgian data with coordinate system 31300 did render correctly with geoserver, it did not transform correctly.

When I did the following (thanks to the suggestion of @AndreJoost):

CREATE TABLE belgian_pipes_wgs_2 AS 
  SELECT mslink, ST_Transform(ST_setsrid(geom,31370),4326) AS geom 
  FROM belgian_pipes;

The data is then converted from EPSG:31370 to WGS84, and then renders correctly on top of the satellite imagery.

I looked at the definition of both EPSG:31300 and EPSG:31370 on spatialreference.org and the proj4js field of 31300 is just empty: maybe that explains why the transformation does not work correctly.

So mental note: use EPSG:31370 for Belgian Lambert 72 in the future.

As an aside: who manages these spatial-references, is it open source, like wikipedia? Can we edit it, or suggest improvements? Shouldn't the proj4js field of 31300 be the same as 31370, or is there a special reason to keep both systems side by side?

share|improve this answer
    
To be honest, nobody really cares about the content of spatialreference.org. The EPSG comitee updates its database frequently, and the maintainers of proj4 follow with their update, which finds its way into GDAL, QGIS and Postgis. I'm not sure where geoserver takes its information, but Openlayers looks up at sr.org. If you look at the last revised entry, you see that there is not much work on it. 31300 and 31370 don't have exactly the same parameters. –  Andre Joost Aug 27 '13 at 18:16
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.