<div dir="ltr"><div>Hi Laurent</div><div><br></div><div>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.<br></div><div><br></div><div>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 (<a href="https://gdal.org/drivers/vector/pg.html#examples">https://gdal.org/drivers/vector/pg.html#examples</a>).</div><div><br></div><div>If you don't have to filter or rename fields between the SHP and the DB, the simplest command is :
<i><span style="background-color:rgb(204,204,204)">ogr2ogr - progress - append D:\XXX.shp<br> -f PG:"dbname='dbname' host='ipXXXXX' port='5432' user='XXXX' password='XXXX' active_schema='human'"</span></i>
</div><div><br></div><div>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 : <a href="https://gdal.org/user/ogr_sql_dialect.html#ogr-sql-dialect">https://gdal.org/user/ogr_sql_dialect.html#ogr-sql-dialect</a>).</div><div><br></div><div>Regards</div><div>Alexandre<br></div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">Le lun. 6 juin 2022 à 03:06, celati Laurent <<a href="mailto:laurent.celati@gmail.com">laurent.celati@gmail.com</a>> a écrit :<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="ltr"><div><br></div><div>Good morning,</div><div><br></div>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).<br><br>Up to now, within my postgis database, I was inserting new features into an existing table<br>thanks to SQL queries :<br><br><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><span style="background-color:rgb(204,204,204)"><i> INSERT INTO SCHEMA_human.TableDestination (<br></i><i> "fieldXX",<br></i><i> "geom")<br></i><i> <br> </i><i> SELECT<br></i><i> "fieldXX",<br></i><i> "geom"<br></i><i> <br> </i><i> FROM source_table</i></span></blockquote><br>Or features updates thanks to<br><br><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><span style="background-color:rgb(204,204,204)"> <i> update<br> set<br> from<br> where<br></i><i> set<br></i><i> from<br></i><i> where</i></span></blockquote><br>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.<br><br>The Source file is 1 POINTS shapefile (EPSG 2154).<br>The target/destination table is therefore an existing postgis table of POINTS (EPSG 2154)<br>(The schema of the destination table is NOT the default public schema but the 'human' schema.)<br><br>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.<br>I priorize the approach with -sql if possible.<br><br>Here is the structure I was thinking of for example for an insert.<br><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><b style="background-color:rgb(238,238,238)"><br></b><i><b style="background-color:rgb(238,238,238)"> </b><span style="background-color:rgb(238,238,238)"> </span><span style="background-color:rgb(204,204,204)"> ogr2ogr - progress - append D:\XXX.shp<br> -f PG:"dbname='dbname' host='ipXXXXX' port='5432' user='XXXX' password='XXXX' active_schema='human'"<br> -sql<br> INSERT INTO human_schema.TableName (<br> "field1",<br> "field2",<br> "field3",<br> "geom")<br> <br> --selection of SHP fields<br> SELECT<br> "field1",<br> "field2",<br> "field3",<br> "geom"<br> <br> FROM<br> shp</span></i></blockquote><br>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?<br>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?<br><br><br>Thanks a lot.<br></div>
_______________________________________________<br>
gdal-dev mailing list<br>
<a href="mailto:gdal-dev@lists.osgeo.org" target="_blank">gdal-dev@lists.osgeo.org</a><br>
<a href="https://lists.osgeo.org/mailman/listinfo/gdal-dev" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/gdal-dev</a><br>
</blockquote></div><br clear="all"><br>-- <br><div dir="ltr" class="gmail_signature">Alexandre Gacon</div>