Hi,
As it often is with this things, it was actually pretty simple: I included the
table name in the connection. It seems that this confused the program. But when
I did it like you, with just username, password and server, it worked.
Thanks for your help,
Marius Kress
________________________________
Von: Rahkonen Jukka (MML) <jukka.rahko...@maanmittauslaitos.fi>
Gesendet: Freitag, 13. November 2020 08:48:03
An: Kreß, Marius
Cc: 'gdal-dev@lists.osgeo.org'
Betreff: Re: [gdal-dev] Oracle Spatial Driver: Is it possible to append
features to a table?
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 <kre...@eba.bund.de>
Lähetetty: torstai 12. marraskuuta 2020 18.40
Vastaanottaja: Rahkonen Jukka (MML) <jukka.rahko...@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.rahko...@maanmittauslaitos.fi>
Gesendet: Mittwoch, 11. November 2020 16:58
An: Kreß, Marius <kre...@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 <kre...@eba.bund.de>
Lähetetty: keskiviikko 11. marraskuuta 2020 17.53
Vastaanottaja: Rahkonen Jukka (MML) <jukka.rahko...@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-boun...@lists.osgeo.org> Im Auftrag von jratike80
Gesendet: Mittwoch, 11. November 2020 16:19
An: gdal-dev@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@.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@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/gdal-dev
_______________________________________________
gdal-dev mailing list
gdal-dev@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/gdal-dev
