[gdal-dev] Problem with using ogr2ogr to migrate data from Postgis to TSQL/Oracle Spatial

[surma]

Hello guys,

I'm learning about ogr2ogr and have encountered a problem.

So I have some data isnide my postgis database that Im trying to migrate to
the tsql and oracle spatial databases.

My postgres config:
###################################################
create sequence my_geometry_table_id_SEQ;
CREATE TABLE public.my_geometry_table
(
  id integer NOT NULL DEFAULT nextval('my_geometry_table_id_SEQ'::regclass),
  date_modified timestamp with time zone,
  geom geometry(Polygon,3765),
  CONSTRAINT my_geometry_table_id PRIMARY KEY (id)
);

insert into public.my_geometry_table(id, date_modified, geom)
values(nextval('my_geometry_table_id_SEQ'),'2013-02-11 09:43:00+01',
ST_GeomFromText('POLYGON ((30 10, 40 40, 20 40, 10 20, 30 10))', 3765));
###################################################


ogr2ogr script Im using:
###################################################
ogr2ogr --debug -overwrite -s_srs EPSG:3765 -t_srs EPSG:3765-f 
"MSSQLSpatial"  MSSQL:server=xy;database=xy;uid=xy;pwd=xy "PG:host=xy
user=xy dbname=xy password=xy port=5432" -lco PRECISION=NO -nlt POLYGON -nln
my_geometry_table -sql "select * from public.my_geometry_table"
###################################################


Error when sending to ORACLE SPATIAL:
###################################################
ERROR 1: ORA-01830: date format picture ends before converting entire input
string
 in OCIStmtExecute
###################################################

 
Error when sending to TSQL:
###################################################
ERROR: Cannot convert varchar to datetime. Conversion failed when converting
date and/or time from character string.
###################################################

When I remove the timezone column from the POSTGIS source, or convert the
column type to varchar via ogr the data gets transfered. 

So it seems that the ogr2ogr cannot handle postgres timezone info from a
timestamp column. Is there a known workaround around this, or must I use
python bindings to trim the timezone portion for each row?

I don't want to convert the column to varchar.

Used versions:
oracle spatial: 11g
tsql: 11.0
postgis: POSTGIS="2.1.7 r13414" 
ogr2ogr version: using 2.0.1, tried with 1.11, 1.13

Thanks.



--
View this message in context: http://osgeo-org.1560.x6.nabble.com/Problem-with-using-ogr2ogr-to-migrate-data-from-Postgis-to-TSQL-Oracle-Spatial-tp5232425.html
Sent from the GDAL - Dev mailing list archive at Nabble.com.