[gdal-dev] ogr2ogr: Stuck trying to append lots of gml files to postgis tables

[Saulteau Don]

I wonder if part of the problem is that you haven't included the -update
flag as well in the ogr2ogr command.

ogr2ogr -update -append -f PostgreSQL PG:"host=localhost user=postgres
dbname=data_itn" raw_itn/6360199_sn6103_2c7157.gz

Then like the warning says, there's also the lco option of -lco
OVERWRITE=YES
Which also overwrites an existing layer, but it will retain any VIEWS in
the postgresql database that are built using the layer you're appending or
updating.



Donovan



On Sun, Mar 22, 2015 at 1:33 PM, Stephen Woodbridge <woodbri at swoodbridge.com
> wrote:

> On 3/22/2015 3:32 PM, Even Rouault wrote:
>
>> Le dimanche 22 mars 2015 20:18:12, Stephen Woodbridge a écrit :
>>
>>> Hi all,
>>>
>>> I have a directory of gml files. I can load any of them into postgis
>>> without a problem. What I'm having a problem with is appending each file
>>> to the existing tables. I'm using : GDAL 1.10.1, released 2013/08/26
>>>
>>> $ ogrinfo raw_itn/6360199_sn6103_2c7157.gz
>>> Had to open data source read-only.
>>> INFO: Open of `raw_itn/6360199_sn6103_2c7157.gz'
>>>         using driver `GML' successful.
>>> 1: Road (None)
>>> 2: RoadLink (Line String)
>>> 3: RoadNode (Point)
>>>
>>> $ ogrinfo raw_itn/6360199_sn6101_2c5532.gz
>>> Had to open data source read-only.
>>> INFO: Open of `raw_itn/6360199_sn6101_2c5532.gz'
>>>         using driver `GML' successful.
>>> 1: RoadLinkInformation (Point)
>>> 2: Road (None)
>>> 3: RoadLink (Line String)
>>> 4: RoadNodeInformation (None)
>>> 5: RoadNode (Point)
>>>
>>> Here is want I am trying:
>>>
>>> dropdb -U postgres -h localhost data_itn
>>> createdb -U postgres -h localhost data_itn
>>> psql -U postgres -h localhost data_itn -c "create extension postgis"
>>> psql -U postgres -h localhost data_itn -c "create schema itn"
>>> psql -U postgres -h localhost data_itn -c "alter database data_itn set
>>> search_path to itn, public, pg_catalog"
>>>
>>> # this loads fine
>>>
>>> ogr2ogr -append -f PostgreSQL PG:"host=localhost user=postgres
>>> dbname=data_itn" raw_itn/6360199_sn6101_2c5532.gz
>>>
>>> # all successive tries to append to the existing tables fails
>>>
>>> ogr2ogr -append -f PostgreSQL PG:"host=localhost user=postgres
>>> dbname=data_itn" raw_itn/6360199_sn6103_2c7157.gz
>>> ERROR 1: Layer road already exists, CreateLayer failed.
>>> Use the layer creation option OVERWRITE=YES to replace it.
>>> ERROR 1: Terminating translation prematurely after failed
>>> translation of layer Road (use -skipfailures to skip errors)
>>>
>>> If I don't install extension postgis then there is no problem reported
>>> and it appears to load all the data by appending the tables with the
>>> geometry in a colum wkb_geometry::bytea.
>>>
>>> How can I load the data into postgis. I have over 700 gml files to load.
>>>
>>
>> Defining PG_LIST_ALL_TABLES=YES as environment variable should solve
>> this. By
>> default non-spatial tables are not listed when opening a PG database, and
>> ogr2ogr is confused as it doesn't see the existing table as existing, so
>> it
>> tries to recreate it. With trunk, ogr2ogr & the PG driver have been
>> improved
>> so that specifying  PG_LIST_ALL_TABLES=YES is not needed in that scenario.
>>
>> Wondering if we shouldn't list all tables (excluding system tables) by
>> default...
>>
>
> Thank you this is a very helpful response. I saw the
> PG_LIST_ALL_TABLES=YES in the documentation, but but never connect that
> with this problem.
>
> If there is not a huge downside to listing all tables I can't see that it
> is a problem because it only happens once after a connection. Or maybe it
> only needs to be done if -append is set.
>
> Anyway, Thanks for the help with this.
>
> -Steve
>
>
>
>>> Thanks,
>>>     -Steve
>>>
>>> ---
>>> This email has been checked for viruses by Avast antivirus software.
>>> http://www.avast.com
>>>
>>> _______________________________________________
>>> gdal-dev mailing list
>>> gdal-dev at lists.osgeo.org
>>> http://lists.osgeo.org/mailman/listinfo/gdal-dev
>>>
>>
>>
>
> ---
> This email has been checked for viruses by Avast antivirus software.
> http://www.avast.com
>
> _______________________________________________
> gdal-dev mailing list
> gdal-dev at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/gdal-dev
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/gdal-dev/attachments/20150322/9c42569e/attachment.html>