I have a table with a geospatial column with srid 28992 but noticed that if I try to show it in QGIS (v1.9) on any OpenLayers map (through the OpenLayers plugin) it is off by a few hundred meters.

Then I converted it to epsg:900913 (remembering something that with an old plugin this projection was required) and then it is shown on the correct spot.

However when I look in the metadata of this layer in QGIS (right-click -> Properties -> Metadata) everything is blank and when I do Zoom to Layer Extent the map jumps to water near Africa (suggesting it doesn't understand the projection).

Somehow the metadata gets lost to QGIS during my manual conversion. The script I use for the manual conversion is:

alter table <table_name> drop constraint "enforce_srid_<column_name>";
update <table_name> set <column_name> = ST_Transform(<column_name>,900913);
alter table <table_name> add constraint "enforce_srid_<column_name>" CHECK (st_srid(<column_name>) = 900913);
update geometry_columns set srid=900913 where f_geometry_column = '<column_name>';

The result of this seems no different (except that now I have an extra column) than if I use:

SELECT AddGeometryColumn('<table_name>','<new_column_name>',900913, 'LINESTRING', 2);
UPDATE <table_name> SET <new_column_name> = ST_Transform(<column_name>,900913);

Does any of you know what god particle I am looking for?


I finally found an answer to this problem and hope this solution will help others as well.

The reason why my layer in EPSG 28992 (Amersfoort/ RD New) was like a hundred meters off is because reprojecting data to EPSG 3857 (which happens automatically when opening a project with openlayer) doesn't take into account a datum shift in the RD projection. This can be approximated by using the parameter towgs84.

What you need to do is add a custom CRS with a name of your chosing and the following parameter:

+proj=sterea +lat_0=52.15616055555555 +lon_0=5.38763888888889 +k=0.999908 +x_0=155000 +y_0=463000 +ellps=bessel +units=m +towgs84=565.2369,50.0087,465.658,-0.406857330322398,0.350732676542563,-1.8703473836068,4.0812 +no_defs <>

I noticed the difference in the projection parameter used by Postgis/postgresql and QGIS was the towgs84 parameter. And that led me to these 2 (Dutch) sites writing about the same problem: epsg28992-of-rijksdriehoekstelsel-verschuiving and note-to-self-the-one-and-only-rd-projection-string

|improve this answer|||||
  • If you are sure that the projection string in qgis is wrong, you can open a ticket for that on hub.qgis.org. – AndreJ Aug 28 '12 at 4:13
  • It is more subtle than that. Check the last remark from Joris in blog.openstreetmap.nl/index.php/2012/01/21/rd . I presume you can read Dutch. – mrg Aug 28 '12 at 9:45
  • 1
    I notice a difference in +towgs84 values. See also trac.osgeo.org/proj/ticket/96. Proj.4 values are used by Postgis and Qgis. Frank Warmerdam should be the best to know and solve the problem... – AndreJ Aug 28 '12 at 10:27

With current OpenLayers plugin, you should use EPSG:3857, not 900913.

Assuming your layer is a vector layer, you can simply load it, and rightclick->Save As in a new Shapefile, with any target CRS you want.

|improve this answer|||||
  • With my conversion to 900913 the map layer seems spot on with my own layer. Is that accidentally? – mrg Aug 21 '12 at 11:53
  • EPSG:3857 and 900913 should theoretically be the same, but I got some misplacements in the past after georeferencing to 900913. Also, qgis tries to reproject 900913-tiles to 3857, which makes them look a bit weired. Could be that your Extend problem has a similar reason. EPSG:900913 will be depricated in future versions of Qgis. – AndreJ Aug 21 '12 at 12:12
  • I read now that creating a new project, add openlayer (project CRS will switch to EPSG:3857) and then opening my own layer it will reproject to EPSG:3857. But that also gives the same displacement as mentioned earlier. Having learned that the plugin i use works with EPSG:3857 is very helpful but unfortunately doesn't answer my original question why QGIS doesn't show metadata after i manually convert my geospatial column. – mrg Aug 21 '12 at 12:24
  • Has Qgis set the layer (not project!) CRS correctly to 28992? – AndreJ Aug 21 '12 at 12:30
  • Layer CRS = EPSG:28992 - Amersfoort / RD New – mrg Aug 21 '12 at 13:06

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.