Why doesn't QGIS accept my manually altered geospatial column?

Question

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 openlayer map (through the OpenLayers plugin) it is off by a few hundred meters.

Then i converted it to 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?

Answer 1

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

Answer 2

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.