[gdal-dev] gdal og2ogr: insert/update postgis table from SHP?

[celati Laurent]

Good morning,



I work with windows, Postgis and gdal (I use gdal/ogr2ogr from the command
prompt. Version of gdal available under C:\Program Files\QGIS 3.16\bin).



Up to now, within my postgis database, I was inserting new features into an
existing table

thanks to SQL queries :



>
> *    INSERT INTO SCHEMA_human.TableDestination (*
> *    "fieldXX",*
> *    "geom")*
>
> *    SELECT*
> *    "fieldXX",*
> *    "geom"*
>     *    FROM source_table*



Or features updates thanks to :



>
> *    update*
> *    set*
> *    from**    where*



It worked. But the limit of this approach is that it requires first
importing the shp into the postgis database beforehands. So my goal is to
directly insert new features/ update (from shp source file / toward postgis
destination table using gdal. In order to ovoid/skip/break free this import
of shp in the bdd beforehands.



The Source file is 1 POINTS shapefile (EPSG 2154).

The target/destination table is therefore an existing postgis table of
POINTS (EPSG 2154)

(The schema of the destination table is NOT the default public schema but
the 'human' schema.)



There seem to be 2 approaches to accomplish this task. Either use the
-append options associated with -fieldmap. (or - update for updates). Or
the approach with -sql by defining the SQL query.

I priorize the approach with -sql if possible.



Here is the structure I was thinking of for example for an insert.



>
> *    ogr2ogr - progress - append D:\XXX.shp*
> *    -f PG:"dbname='dbname' host='ipXXXXX' port='5432' user='XXXX'
> password='XXXX' active_schema='human'"*
> *    -sql*
> *    INSERT INTO human_schema.TableName (*
> *    "field1",*
> *    "field2",*
> *    "field3",*
> *    "geom")*
> *    --selection of SHP fields*
> *    SELECT*
> *    "field1",*
> *    "field2",*
> *    "field3",*
> *    "geom"*
> *    FROM*
> *    shp*

Could someone tell me if I'm wrong? Tell me where are my mistakes? With
this method, is it possible to specify the fields of the source shp within
the SQL clause? (non-geom field and geom field?). When, like me, we have a
shp input and a postgis table output, should we rather prioritize the
approach with -append -addfield (or -update) rather than the approach with
-sql?

For input shp, the name of geometry field  (that shoud be set into sql
query) is always "geom"? If not, what would be the way to find out?





Thanks a lot.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/gdal-dev/attachments/20220606/abd0dea1/attachment.htm>