<div dir="ltr"><p class="MsoNormal" style="margin:0cm;font-size:11pt;font-family:Calibri,sans-serif">Good morning,</p>
<p class="MsoNormal" style="margin:0cm;font-size:11pt;font-family:Calibri,sans-serif"> </p>
<p class="MsoNormal" style="margin:0cm;font-size:11pt;font-family:Calibri,sans-serif">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).</p>
<p class="MsoNormal" style="margin:0cm;font-size:11pt;font-family:Calibri,sans-serif"> </p>
<p class="MsoNormal" style="margin:0cm;font-size:11pt;font-family:Calibri,sans-serif">Up to now, within my postgis database, I was inserting new
features into an existing table</p>
<p class="MsoNormal" style="margin:0cm;font-size:11pt;font-family:Calibri,sans-serif">thanks to SQL queries :</p>
<p class="MsoNormal" style="margin:0cm;font-size:11pt;font-family:Calibri,sans-serif"> </p>
<blockquote style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex" class="gmail_quote"><i> <span style="background:silver">INSERT INTO
SCHEMA_human.TableDestination (<br></span></i><i><span style="background:silver"> "fieldXX",<br></span></i><i><span style="background:silver"> "geom")<br></span></i><i><span style="background:silver"> <br> </span></i><i><span style="background:silver"> SELECT<br></span></i><i><span style="background:silver"> "fieldXX",<br></span></i><i><span style="background:silver"> "geom"<br></span></i><i><span style="background:silver"> <br> </span></i><i><span style="background:silver"> FROM source_table</span></i></blockquote>
<p class="MsoNormal" style="margin:0cm;font-size:11pt;font-family:Calibri,sans-serif"> </p>
<p class="MsoNormal" style="margin:0cm;font-size:11pt;font-family:Calibri,sans-serif">Or features updates thanks to :</p>
<p class="MsoNormal" style="margin:0cm;font-size:11pt;font-family:Calibri,sans-serif"> </p>
<blockquote style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex" class="gmail_quote"><i> <span style="background:silver">update<br></span></i><i><span style="background:silver"> set<br></span></i><i><span style="background:silver"> from<br></span></i><i><span style="background:silver"> where</span></i></blockquote>
<p class="MsoNormal" style="margin:0cm;font-size:11pt;font-family:Calibri,sans-serif"> </p>
<p class="MsoNormal" style="margin:0cm;font-size:11pt;font-family:Calibri,sans-serif">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.</p>
<p class="MsoNormal" style="margin:0cm;font-size:11pt;font-family:Calibri,sans-serif"> </p>
<p class="MsoNormal" style="margin:0cm;font-size:11pt;font-family:Calibri,sans-serif">The Source file is 1 POINTS shapefile (EPSG 2154).</p>
<p class="MsoNormal" style="margin:0cm;font-size:11pt;font-family:Calibri,sans-serif">The target/destination table is therefore an existing postgis
table of POINTS (EPSG 2154)</p>
<p class="MsoNormal" style="margin:0cm;font-size:11pt;font-family:Calibri,sans-serif">(The schema of the destination table is NOT the default
public schema but the 'human' schema.)</p>
<p class="MsoNormal" style="margin:0cm;font-size:11pt;font-family:Calibri,sans-serif"> </p>
<p class="MsoNormal" style="margin:0cm;font-size:11pt;font-family:Calibri,sans-serif">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.</p>
<p class="MsoNormal" style="margin:0cm;font-size:11pt;font-family:Calibri,sans-serif">I priorize the approach with -sql if possible.</p>
<p class="MsoNormal" style="margin:0cm;font-size:11pt;font-family:Calibri,sans-serif"> </p>
<p class="MsoNormal" style="margin:0cm;font-size:11pt;font-family:Calibri,sans-serif">Here is the structure I was thinking of for example for an
insert.</p>
<p class="MsoNormal" style="margin:0cm;font-size:11pt;font-family:Calibri,sans-serif"> </p>
<blockquote style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex" class="gmail_quote"><i> <span style="background:silver">ogr2ogr - progress - append
D:\XXX.shp<br></span></i><i><span style="background:silver"> -f PG:"dbname='dbname' host='ipXXXXX'
port='5432' user='XXXX' password='XXXX' active_schema='human'"<br></span></i><i><span style="background:silver"> -sql<br></span></i><i><span style="background:silver"> INSERT INTO human_schema.TableName (<br></span></i><i><span style="background:silver"> "field1",<br></span></i><i><span style="background:silver"> "field2",<br></span></i><i><span style="background:silver"> "field3",<br></span></i><i><span style="background:silver"> "geom")<br></span></i><i> <span style="background:silver">--selection of SHP fields<br></span></i><i><span style="background:silver"> SELECT<br></span></i><i><span style="background:silver"> "field1",<br></span></i><i><span style="background:silver"> "field2",<br></span></i><i><span style="background:silver"> "field3",<br></span></i><i><span style="background:silver"> "geom"<br></span></i><i><span style="background:silver"> FROM<br></span></i><i><span style="background:silver"> shp<br></span></i> </blockquote>
<p class="MsoNormal" style="margin:0cm;font-size:11pt;font-family:Calibri,sans-serif">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?</p>
<p class="MsoNormal" style="margin:0cm;font-size:11pt;font-family:Calibri,sans-serif">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?</p>
<p class="MsoNormal" style="margin:0cm;font-size:11pt;font-family:Calibri,sans-serif"> </p>
<p class="MsoNormal" style="margin:0cm;font-size:11pt;font-family:Calibri,sans-serif"> </p>
<p class="MsoNormal" style="margin:0cm;font-size:11pt;font-family:Calibri,sans-serif">Thanks a lot.</p></div>