[gdal-dev] Oracle Spatial Driver: Is it possible to append features to a table?

Rahkonen Jukka (MML) jukka.rahkonen at maanmittauslaitos.fi
Thu Nov 12 23:48:03 PST 2020 

Hi,

It have not been using Oracle lately but yesterday I installed the free XE version with default values on Windows. Then I made the following simple test (I apologize all the DB admins I have been working with).

ogr2ogr OCI:system/password test.shp
ogr2ogr -update -append OCI:system/password test.shp -nln test

Ogr2ogr is adding new features into the table without errors. I also tried with "-nln test_test" and it worked as well.

If I run the command again without the -append option the error I get is 
ERROR 1: Layer test already exists, and -append not specified. Consider using -append, or -overwrite.

Quite different message. Because you get an Oracle error ERROR 1: ORA-00955 I guess that it means that
1) GDAL does not know that the table already exists and
2) therefore it tries to create a new table.

It may be that your Oracle user does not have all required rights. Does ogrinfo list the table isophone_test? Did you read the few errors that you got when you fired the first ogr2ogr command (sometimes they are meaningful)? Does your user have rights to insert and update also the spatial metadata tables in Oracle? Run the -append command again by adding "--debug on" into the command and you will see what SQL statements GDAL creates. Here is what one of my tests produced. You can test the SQL commands with SQLPlus and it may tell you what rights are missing.

OCI: From 'Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production' :
OCI: Version:18
OCI: Release:0
OCI: Prepare(ALTER SESSION SET NLS_DATE_FORMAT='YYYY/MM/DD'         NLS_TIME_FORMAT='HH24:MI:SS' NLS_TIME_TZ_FORMAT='HH24:MI:SS TZHTZM'         NLS_TIMESTAMP_FORMAT='YYYY/MM/DD HH24:MI:SS'         NLS_TIMESTAMP_TZ_FORMAT='YYYY/MM/DD HH24:MI:SS TZHTZM'         NLS_NUMERIC_CHARACTERS = '. ')
OCI: Prepare(SELECT TABLE_NAME, OWNER FROM ALL_SDO_GEOM_METADATA)
OCI: Prepare(SELECT COLUMN_NAME, DATA_DEFAULT
FROM user_tab_columns
WHERE DATA_DEFAULT IS NOT NULL AND TABLE_NAME = UPPER(:table_name))
OCI: Prepare(SELECT COUNT(*)
FROM USER_SDO_GEOM_METADATA u, TABLE(u.diminfo) t
WHERE u.table_name = :table_name
  AND u.column_name = :geometry_name)
OCI: Prepare(select m.SDO_LAYER_GTYPE from user_sdo_index_metadata m, user_sdo_index_info i where i.index_name = m.sdo_index_name and i.table_name = upper(:table_name))
OCI: Prepare(SELECT SRID FROM ALL_SDO_GEOM_METADATA WHERE TABLE_NAME = UPPER(:table_name) AND COLUMN_NAME = UPPER(:geometry_name))
OCI: Prepare(SELECT COLUMN_NAME, DATA_DEFAULT
FROM user_tab_columns
WHERE DATA_DEFAULT IS NOT NULL AND TABLE_NAME = UPPER(:table_name))
OCI: Prepare(SELECT COUNT(*)
FROM USER_SDO_GEOM_METADATA u, TABLE(u.diminfo) t
WHERE u.table_name = :table_name
  AND u.column_name = :geometry_name)
OCI: Prepare(select m.SDO_LAYER_GTYPE from user_sdo_index_metadata m, user_sdo_index_info i where i.index_name = m.sdo_index_name and i.table_name = upper(:table_name))
OCI: Prepare(SELECT SRID FROM ALL_SDO_GEOM_METADATA WHERE TABLE_NAME = UPPER(:table_name) AND COLUMN_NAME = UPPER(:geometry_name))
OCI: Prepare(SELECT COLUMN_NAME, DATA_DEFAULT
FROM user_tab_columns
WHERE DATA_DEFAULT IS NOT NULL AND TABLE_NAME = UPPER(:table_name))
OCI: Prepare(SELECT COUNT(*)
FROM USER_SDO_GEOM_METADATA u, TABLE(u.diminfo) t
WHERE u.table_name = :table_name
  AND u.column_name = :geometry_name)
OCI: Prepare(select m.SDO_LAYER_GTYPE from user_sdo_index_metadata m, user_sdo_index_info i where i.index_name = m.sdo_index_name and i.table_name = upper(:table_name))
OCI: Prepare(SELECT SRID FROM ALL_SDO_GEOM_METADATA WHERE TABLE_NAME = UPPER(:table_name) AND COLUMN_NAME = UPPER(:geometry_name))
GDAL: GDALOpen(OCI:system/ollikok90, this=000001618B8C0B20) succeeds as OCI.
OCI: Prepare(INSERT /*+ APPEND */ INTO "TIME_TEST"("OGR_FID","ORA_GEOMETRY","aika","attr","column") VALUES ( :fid , :geometry,  :field_0,  :field_1,  :field_2) )
OCI: Prepare(SELECT MAX("OGR_FID") FROM "TIME_TEST")
GDALVectorTranslate: 3 features written in layer 'TIME_TEST'
Shape: 3 features read on layer 'timetest'.
GDAL: GDALClose(timetest.shp, this=000001618B8A4540)
OCI: Flushing 3 features on layer TIME_TEST
OCI: Prepare(select min(case when r=1 then sdo_lb else null end) minx, min(case when r=2 then sdo_lb else null end) miny, min(case when r=1 then sdo_ub else null end) maxx, min(case when r=2 then sdo_ub else null end) maxy from (SELECT d.sdo_dimname, d.sdo_lb, sdo_ub, sdo_tolerance, rownum r FROM ALL_SDO_GEOM_METADATA m, table(m.diminfo) d where m.table_name = UPPER('TIME_TEST') and m.COLUMN_NAME = UPPER('ORA_GEOMETRY') ) )

-Jukka Rahkonen-

-----Alkuperäinen viesti-----
Lähettäjä: Kreß, Marius <KressM at eba.bund.de> 
Lähetetty: torstai 12. marraskuuta 2020 18.40
Vastaanottaja: Rahkonen Jukka (MML) <jukka.rahkonen at maanmittauslaitos.fi>
Aihe: AW: [gdal-dev] Oracle Spatial Driver: Is it possible to append features to a table?

Hi,

I realized that I understood your first mail wrong. I read "Write your commands..." as "write them in the console" and wondered how this should work. But you meant "write them in the email", right? That makes more sense.

The first command is:
ogr2ogr OCI:(connection) "D:\(path)\isophone_6611.shp" -nln isophone_test This throws a few errors, but still works.

Then the next command would be: 
ogr2ogr -update -append OCI:(connection) "D:\(path)\isophone_6612.shp" -nln isophone_test This throws similar errors to the one above, but one more:
ERROR 1: ORA-00955: Es gibt bereits ein Objekt mit diesem Namen [which means: There is already an object with that name]  in CREATE TABLE "ISOPHONE_TEST" ( OGR_FID INTEGER PRIMARY KEY, ORA_GEOMETRY MDSYS.SDO_GEOMETRY )

That’s why I said it seems like the -update and -append options are ignored.

Best regards,
Marius Kress

-----Ursprüngliche Nachricht-----
Von: Rahkonen Jukka (MML) <jukka.rahkonen at maanmittauslaitos.fi>
Gesendet: Mittwoch, 11. November 2020 16:58
An: Kreß, Marius <KressM at eba.bund.de>
Betreff: Re: [gdal-dev] Oracle Spatial Driver: Is it possible to append features to a table?

Hi,

You wrote

" Even if I first write it into a shapefile and then try to write it in the  database with ogr2ogr, it ignores the -append and -update options."

This ogr2ogr command is what I meant with the second command. The first one is the ogr2ogr command that you used when you created the Oracle table and inserted the first batch of data into it.

-Jukka Rahkonen-

-----Alkuperäinen viesti-----
Lähettäjä: Kreß, Marius <KressM at eba.bund.de>
Lähetetty: keskiviikko 11. marraskuuta 2020 17.53
Vastaanottaja: Rahkonen Jukka (MML) <jukka.rahkonen at maanmittauslaitos.fi>
Aihe: AW: [gdal-dev] Oracle Spatial Driver: Is it possible to append features to a table?

Hi Jukka,

thank you very much for your reply! But unfortunately I don't understand what you mean. Could you maybe give me an example what the first two commands might look like?

Best regards,
Marius Kress

-----Ursprüngliche Nachricht-----
Von: gdal-dev <gdal-dev-bounces at lists.osgeo.org> Im Auftrag von jratike80
Gesendet: Mittwoch, 11. November 2020 16:19
An: gdal-dev at lists.osgeo.org
Betreff: Re: [gdal-dev] Oracle Spatial Driver: Is it possible to append features to a table?

Hi,

It should work. Write your two first ogr2ogr commands (the initial one and first to append) as a whole but without OCI connection details. The -nln parameter must appear in the append command if I remember right.

-Jukka Rahkonen-



Kreß, Marius wrote
> Dear GDAL developers and users,
> 
> I want to write contour polygons into an Oracle Spatial database. 
> Because the area is quite large, I would like to calculate them in 
> smaller boxes and write them into the table one after the other. But 
> as far as I can see, it is not possible to append features to a table.
> You can only create a new one, overwrite or truncate it. Even if I 
> first write it into a shapefile and then try to write it in the 
> database with ogr2ogr, it ignores the -append and -update options.
> 
> Is it correct that it doesn't work or did I miss anything? Do you know 
> a better solution?
> 
> Thanks for your help,
> 
> Marius Kress
> 
> _______________________________________________
> gdal-dev mailing list

> gdal-dev at .osgeo

> https://lists.osgeo.org/mailman/listinfo/gdal-dev





--
Sent from: http://osgeo-org.1560.x6.nabble.com/GDAL-Dev-f3742093.html
_______________________________________________
gdal-dev mailing list
gdal-dev at lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/gdal-dev

More information about the gdal-dev mailing list