[gdal-dev] [EXTERNAL] How to use TRUNCATE lco with Oracle Spatial driver

[Smith, Michael ERDC-RDE-CRREL-NH]

Aileen,

With the TRUNCATE option, do not use –append since append will disable the use of layer creation options.

Use ogr2ogr  -s_srs "EPSG:27700" -t_srs "EPSG:27700" -f OCI OCI::xxxxx/xxxx at XE PG:"dbname=postgis active_schema=planning host=localhost user=yyyy password=yyyy!" open_country -nln open_country3 -lco TRUNCATE=yes

Mike

--
Michael Smith
Remote Sensing/GIS Center
US Army Corps of Engineers


From: Aileen Heal <aileenheal at astuntechnology.com<mailto:aileenheal at astuntechnology.com>>
Date: Friday, June 20, 2014 at 7:51 AM
To: "gdal-dev at lists.osgeo.org<mailto:gdal-dev at lists.osgeo.org>" <gdal-dev at lists.osgeo.org<mailto:gdal-dev at lists.osgeo.org>>
Subject: [EXTERNAL] [gdal-dev] How to use TRUNCATE lco with Oracle Spatial driver
Resent-From: Michael Smith <michael.smith at usace.army.mil<mailto:michael.smith at usace.army.mil>>


Can any one provide an example of how to use the TRUNCATE lco for the Oracle Spatial driver.

According to the docs.

TRUNCATE: This may be "YES" to force the existing table to be reused, but to first truncate all records in the table, preserving indexes or dependencies.

Which is exactly what I want, but every time I try to use it I get warnings or errors.

e.g. First create a simple table.

ogr2ogr -overwrite -s_srs "EPSG:27700" -t_srs "EPSG:27700" -f OCI OCI:xxxxx/xxxx at XE PG:"dbname=postgis active_schema=planning host=localhost user=yyyy password=yyyy" open_country -lco LAUNDER=yes -lco GEOMETRY_NAME=GEOLOC -nln open_country3

so I tried using append with TRUNCATE lco.

ogr2ogr -append -s_srs "EPSG:27700" -t_srs "EPSG:27700" -f OCI OCI::xxxxx/xxxx at XE PG:"dbname=postgis active_schema=planning host=localhost user=yyyy password=yyyy!" open_country -nln open_country3 -lco TRUNCATE=yes

which resulted in the the following
WARNING: Layer creation options ignored since an existing layer is being appended to.

The data is appended and the table is not truncated.

So I tried this:

ogr2ogr -overwrite -s_srs "EPSG:27700" -t_srs "EPSG:27700" -f OCI OCI:xxxxx/xxxx at XE PG:"dbname=postgis active_schema=planning host=localhost user=yyyy password=yyyy" open_country -lco LAUNDER=yes -lco GEOMETRY_NAME=GEOLOC -lco TRUNCATE=yes -nln open_country3

and I get the following errors

ERROR 1: ORA-00942: table or view does not exist in TRUNCATE TABLE "OPEN_COUNTRY3"

ERROR 1: ORA-04043: object OPEN_COUNTRY3 does not exist in OCIDescribeAny

ERROR 1: ORA-04043: object "OPEN_COUNTRY3" does not exist in OCIDescribeAny

ERROR 1: ORA-04043: object "OPEN_COUNTRY3" does not exist in OCIDescribeAny

ERROR 1: ORA-04043: object "OPEN_COUNTRY3" does not exist in OCIDescribeAny

ERROR 1: ORA-00942: table or view does not exist in ALTER TABLE OPEN_COUNTRY3 ADD "DISPLAYNAM" VARCHAR2(150)

ERROR 1: ORA-00942: table or view does not exist in ALTER TABLE OPEN_COUNTRY3 ADD "POLICY_REF" VARCHAR2(100)

ERROR 1: ORA-00942: table or view does not exist in ALTER TABLE OPEN_COUNTRY3 ADD "LAYER_NO" NUMBER(10)

ERROR 1: ORA-00942: table or view does not exist in ALTER TABLE OPEN_COUNTRY3 ADD "AREA_HA" NUMBER(24,15)

ERROR 1: ORA-00942: table or view does not exist in ALTER TABLE OPEN_COUNTRY3 ADD "TEXT_REF" VARCHAR2(20)

ERROR 1: ORA-00942: table or view does not exist in ALTER TABLE OPEN_COUNTRY3 ADD "LAYER_NAME" VARCHAR2(50)

ERROR 1: ORA-00942: table or view does not exist in ALTER TABLE OPEN_COUNTRY3 ADD "SETTLEMENT" VARCHAR2(40)

ERROR 1: ORA-00942: table or view does not exist in ALTER TABLE OPEN_COUNTRY3 ADD "NO_UNITS" NUMBER(10)

ERROR 1: ORA-00942: table or view does not exist in ALTER TABLE OPEN_COUNTRY3 ADD "MI_STYLE" VARCHAR2(254)

ERROR 1: ORA-00942: table or view does not exist in ALTER TABLE OPEN_COUNTRY3 ADD "MI_PRINX" NUMBER(10)

ERROR 1: ORA-00942: table or view does not exist in ALTER TABLE OPEN_COUNTRY3 ADD "DISPLAYNAME" VARCHAR2(150)

So it appears the -overwrite is dropping the table and then trying to truncate it hence the errors.

So I try this

ogr2ogr -s_srs "EPSG:27700" -t_srs "EPSG:27700" -f OCI OCI::xxxxx/xxxx at XE PG:"dbname=postgis active_schema=planning host=localhost user=yyyy password=yyyy" open_country -nln open_country3 -lco TRUNCATE=yes

And I get the following.

FAILED: Layer open_country3 already exists, and -append not specified.

Consider using -append, or -overwrite.

ERROR 1: Terminating translation prematurely after failed

translation of layer open_country (use -skipfailures to skip errors)

Not sure what to try next.

TIA


[https://astuntechnology.com/media/website/images/library/supporting_badge_small.png]<https://astuntechnology.com/ishare/2014-enterprise-gis-roadshows/>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/gdal-dev/attachments/20140620/801687ca/attachment-0001.html>