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

[Alexandre Gacon]

Hi Laurent

The SQL option is executed against the source of the date (in your case the
SHP file) and not against your database. So you can use it to select the
fields of your SHP you want in your database, ogr2ogr taking care of
creating the database if needed.

For the SHP, there is no geom field, it will be automatically included in
your database, unless you say so. Have a look at the examples of the
PostgreSQL (https://gdal.org/drivers/vector/pg.html#examples).

If you don't have to filter or rename fields between the SHP and the DB,
the simplest command is :
*ogr2ogr - progress - append D:\XXX.shp    -f PG:"dbname='dbname'
host='ipXXXXX' port='5432' user='XXXX' password='XXXX'
active_schema='human'"*

If you need to include only some fields of your SHP, with or without
renaming, you can add the --sql option (more information on the default SQL
dialect : https://gdal.org/user/ogr_sql_dialect.html#ogr-sql-dialect).

Regards
Alexandre

Le lun. 6 juin 2022 à 03:06, celati Laurent <laurent.celati at gmail.com> a
écrit :

>
> 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*
>> *    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.
> _______________________________________________
> gdal-dev mailing list
> gdal-dev at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/gdal-dev
>


-- 
Alexandre Gacon
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/gdal-dev/attachments/20220606/606fe022/attachment-0001.htm>