[gdal-dev] ogr2ogr projecting from postgis to postgis

[Bryan Hempen]

Hi!

I have a little trouble figuring out how to use ogr2ogr to read spatial data from a postgis table, project it and store it back in the database. I do not want to create a shapefile or something like that and load the data back into the database again.

Using ogr2ogr for data other than stored in a PostGIS database works perfectly fine, but I can't even export PostGIS data to a shapefile or GML (always empty) and I don't know why...

What worked out well so far is loading the PostGIS data with QGIS, save it as a shapefile, THEN project it to another shapefile and finally load the shapefile into the database. Yet, as I said before, I would like to directly project the data from database to database.

I know that PostGIS provides a function called st_transform for exactly this purpose, but I need to use ogr2ogr.

Here is the command which does not work so far, hopefully you guys can give me some help:

ogr2ogr -f "PostgreSQL" -s_srs "+proj=longlat +a=3396190 +b=3376200 +no_defs" -t_srs "+proj=tmerc +lat_0=0 +lon_0=0 +k=0.9996 +x_0=0 +y_0=0 +a=3396190 +b=3376200 +units=m +no_defs" PG:"dbname='test4' host='localhost' port='5432' user='postgres' password='xxx'" PG:"dbname='test3' host='localhost' port='5432' user='postgres' password='xxx'" alldata   

Notes:

- the table containing the geometry column is called alldata
- the_geom is added with AddGeometryColumns
- alldata contains some more non-spatial columns
- I need the results to be stored in the database. In this command above I try to store the results in a different database on the same server. The database is empty so far. What would be even better is to add an additional column in the same database as the original data like the_geom_projected and store the projected geometries in there.

I am using the last FWTools, PostGIS 2.0, PostgreSQL 8.4.  

When I run the command above I get the following message:

Warning 1: Multi-column primary key in 'raster_columns' detected but not supported.
Warning 1: Multi-column primary key in 'raster_columns' detected but not supported.
ERROR 1: AddGeometryColumn failed for layer alldata, layer creation has failed.
ERROR 1: Terminating translation prematurely after failed
translation of layer alldata (use -skipfailures to skip errors)

I have tried a lot of the examples on the website and some tutorials, but nothing worked. Even simple simple exports like 

ogr2ogr "C:\Documents and Settings\user\Deskto
p\new1.shp"  PG:"dbname='test3' host='localhost' port='5432' user='postgres' password='xxx'" alldata

produce an empty shapefile. What am I doing wrong? Thanks for your help.

Kind regards,
Bryan