<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>
<body>
<p>FYI Jeremy Palmer has just submitted a fix for that:
<a class="moz-txt-link-freetext" href="https://github.com/OSGeo/gdal/pull/6896">https://github.com/OSGeo/gdal/pull/6896</a><br>
</p>
<div class="moz-cite-prefix">Le 30/11/2022 à 16:12, Daniel Mannarino
a écrit :<br>
</div>
<blockquote type="cite"
cite="mid:CAO8qHHGKVmgVGVjr63Pi9R7E5jC7pgHe-QirFbRiRcgj9nsM_g@mail.gmail.com">
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
<div dir="ltr">
<p class="MsoNormal"
style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">Hello!</p>
<p class="MsoNormal"
style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)"><br>
</p>
<p class="MsoNormal"
style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">I'm
having trouble using the temporary table option of ogr2ogr:<span
class="gmail-Apple-converted-space"> </span><a
href="https://gdal.org/drivers/vector/pg.html#layer-creation-options"
title="https://gdal.org/drivers/vector/pg.html#layer-creation-options"
moz-do-not-send="true" class="moz-txt-link-freetext">https://gdal.org/drivers/vector/pg.html#layer-creation-options</a></p>
<p class="MsoNormal"
style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">Ultimately
I am trying to load data from a Shapefile into a temporary
table, enrich the rows in the temporary table, and then copy
from the temporary table into a final table. However I cannot
get Postgres and ogr2ogr to work together. It sounds to me
from the logs that they disagree on the table name. In my
example code below I have removed everything that is to happen
after loading the data (enriching, copying) for clarity.</p>
<p class="MsoNormal"
style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)"><br>
</p>
<p class="MsoNormal"
style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">Very
little is said about the temporary table option on the web,
but from the source (and Postgres logs) it looks like ogr2ogr
assumes a schema of "pg_temp_1":<span
class="gmail-Apple-converted-space"> </span><a
href="https://github.com/OSGeo/gdal/blob/dc39f7d68095621842a2547ab06c60a03fd34a19/ogr/ogrsf_frmts/pg/ogrpgdatasource.cpp#L1865-L1872"
title="https://github.com/OSGeo/gdal/blob/dc39f7d68095621842a2547ab06c60a03fd34a19/ogr/ogrsf_frmts/pg/ogrpgdatasource.cpp#L1865-L1872"
moz-do-not-send="true" class="moz-txt-link-freetext">https://github.com/OSGeo/gdal/blob/dc39f7d68095621842a2547ab06c60a03fd34a19/ogr/ogrsf_frmts/pg/ogrpgdatasource.cpp#L1865-L1872</a></p>
<p class="MsoNormal"
style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)"><br>
</p>
<p class="MsoNormal"
style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">It
sounds to me from the Postgres docs this isn't safe to assume,
but let's say it is for the moment.</p>
<p class="MsoNormal"
style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)"><br>
</p>
<p class="MsoNormal"
style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">Here
is my simplified script:</p>
<p class="MsoNormal"
style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">#!/bin/bash</p>
<p class="MsoNormal"
style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">set
-e</p>
<p class="MsoNormal"
style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)"><br>
</p>
<p class="MsoNormal"
style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">PGHOST="<host>"</p>
<p class="MsoNormal"
style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">PGDATABASE="<db>"</p>
<p class="MsoNormal"
style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">PGUSER="<user>"</p>
<p class="MsoNormal"
style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">PGPASSWORD="<pass>"</p>
<p class="MsoNormal"
style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">PGPORT=<port></p>
<p class="MsoNormal"
style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)"><br>
</p>
<p class="MsoNormal"
style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">LOCAL_FILE="/vsizip/test.shp.zip"</p>
<p class="MsoNormal"
style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">SRC_LAYER="test"</p>
<p class="MsoNormal"
style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">FID_NAME="gfw_fid"</p>
<p class="MsoNormal"
style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">GEOMETRY_NAME="geom"</p>
<p class="MsoNormal"
style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">TEMP_SCHEMA="pg_temp_1"</p>
<p class="MsoNormal"
style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">TEMP_TABLE="bazinga"</p>
<p class="MsoNormal"
style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)"><br>
</p>
<p class="MsoNormal"
style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)"><br>
</p>
<p class="MsoNormal"
style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">ogr2ogr
-f "PostgreSQL" PG:"password=$PGPASSWORD host=$PGHOST
port=$PGPORT dbname=$PGDATABASE user=$PGUSER" \</p>
<p class="MsoNormal"
style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">
"$LOCAL_FILE" "$SRC_LAYER" \</p>
<p class="MsoNormal"
style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">
-lco GEOMETRY_NAME="$GEOMETRY_NAME" \</p>
<p class="MsoNormal"
style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">
-lco SPATIAL_INDEX=NONE \</p>
<p class="MsoNormal"
style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">
-lco FID="$FID_NAME" \</p>
<p class="MsoNormal"
style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">
-lco TEMPORARY=ON \</p>
<p class="MsoNormal"
style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">
-nlt PROMOTE_TO_MULTI \</p>
<p class="MsoNormal"
style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">
-nln "$TEMP_SCHEMA.$TEMP_TABLE" \</p>
<p class="MsoNormal"
style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)"> -t_srs
EPSG:4326 \</p>
<p class="MsoNormal"
style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">
--config PG_USE_COPY YES \</p>
<p class="MsoNormal"
style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">
-makevalid -update</p>
<p class="MsoNormal"
style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)"><br>
</p>
<p class="MsoNormal"
style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">#
End of script</p>
<p class="MsoNormal"
style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)"><br>
</p>
<p class="MsoNormal"
style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)"><br>
</p>
<p class="MsoNormal"
style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">Here
is the Postgres log from attempting to run this script with
the Shapefile in the PS:</p>
<p class="MsoNormal"
style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)"><br>
</p>
<p class="MsoNormal"
style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">#
START LOG</p>
<p class="MsoNormal"
style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">2022-11-28
21:16:51.206 UTC [504] LOG: statement: set client_encoding to
'UTF8'</p>
<p class="MsoNormal"
style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">2022-11-28
21:16:51.209 UTC [504] LOG: execute <unnamed>: SHOW
client_encoding</p>
<p class="MsoNormal"
style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">2022-11-28
21:16:51.239 UTC [504] LOG: execute <unnamed>: SELECT
n.nspname FROM pg_proc p JOIN pg_namespace n ON n.oid =
p.pronamespace WHERE proname = 'postgis_version'</p>
<p class="MsoNormal"
style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">2022-11-28
21:16:51.245 UTC [504] LOG: execute <unnamed>: SELECT
version()</p>
<p class="MsoNormal"
style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">2022-11-28
21:16:51.246 UTC [504] LOG: execute <unnamed>: SHOW
standard_conforming_strings</p>
<p class="MsoNormal"
style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">2022-11-28
21:16:51.250 UTC [504] LOG: execute <unnamed>: SELECT
oid, typname FROM pg_type WHERE typname IN ('geometry',
'geography') AND typtype='b'</p>
<p class="MsoNormal"
style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">2022-11-28
21:16:51.323 UTC [504] LOG: execute <unnamed>: SELECT
postgis_version()</p>
<p class="MsoNormal"
style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">2022-11-28
21:16:51.326 UTC [504] LOG: execute <unnamed>: SET
ENABLE_SEQSCAN = ON</p>
<p class="MsoNormal"
style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">2022-11-28
21:16:51.342 UTC [504] LOG: execute <unnamed>: SELECT 1
FROM information_schema.tables WHERE table_name =
'geometry_columns' LIMIT 1</p>
<p class="MsoNormal"
style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">2022-11-28
21:16:51.345 UTC [504] LOG: execute <unnamed>: SELECT 1
FROM information_schema.tables WHERE table_name =
'spatial_ref_sys' LIMIT 1</p>
<p class="MsoNormal"
style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">2022-11-28
21:16:51.354 UTC [504] LOG: execute <unnamed>: SELECT
ST_Srid('POINT EMPTY'::GEOMETRY)</p>
<p class="MsoNormal"
style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">2022-11-28
21:16:51.356 UTC [504] LOG: execute <unnamed>: SELECT
current_schema()</p>
<p class="MsoNormal"
style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">2022-11-28
21:16:51.394 UTC [504] LOG: execute <unnamed>: BEGIN</p>
<p class="MsoNormal"
style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">2022-11-28
21:16:51.415 UTC [504] LOG: execute <unnamed>: SELECT
c.oid FROM pg_class c JOIN pg_namespace n ON
c.relnamespace=n.oid WHERE c.relname = 'bazinga' AND n.nspname
= 'pg_temp_1'</p>
<p class="MsoNormal"
style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">2022-11-28
21:16:51.418 UTC [504] LOG: execute <unnamed>: SELECT
c.oid FROM pg_class c JOIN pg_namespace n ON
c.relnamespace=n.oid WHERE c.relname = 'bazinga' AND n.nspname
= 'pg_temp_1'</p>
<p class="MsoNormal"
style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">2022-11-28
21:16:51.439 UTC [504] LOG: execute <unnamed>: SELECT
c.relname, n.nspname, c.relkind, a.attname, t.typname,
postgis_typmod_dims(a.atttypmod) dim,
postgis_typmod_srid(a.atttypmod) srid,
postgis_typmod_type(a.atttypmod)::text geomtyp,
array_agg(pg_get_constraintdef(s.oid))::text att_constraints,
a.attnotnull, d.description FROM pg_class c JOIN pg_attribute
a ON a.attrelid=c.oid JOIN pg_namespace n ON c.relnamespace =
n.oid AND c.relkind in ('r','v','m','f') AND NOT ( n.nspname =
'public' AND c.relname = 'raster_columns' ) JOIN pg_type t ON
a.atttypid = t.oid AND (t.typname = 'geometry'::name OR
t.typname = 'geography'::name) LEFT JOIN pg_constraint s ON
s.connamespace = n.oid AND s.conrelid = c.oid AND a.attnum =
ANY (s.conkey) AND (pg_get_constraintdef(s.oid) LIKE
'%geometrytype(% = %' OR pg_get_constraintdef(s.oid) LIKE
'%ndims(% = %' OR pg_get_constraintdef(s.oid) LIKE '%srid(% =
%') LEFT JOIN pg_description d ON d.objoid = c.oid AND
d.classoid = 'pg_class'::regclass::oid AND d.objsubid = 0
GROUP BY c.relname, n.nspname, c.relkind, a.attname,
t.typname, dim, srid, geomtyp, a.attnotnull, c.oid, a.attnum,
d.description ORDER BY c.oid, a.attnum</p>
<p class="MsoNormal"
style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">2022-11-28
21:16:51.452 UTC [504] LOG: execute <unnamed>: SELECT
srid FROM spatial_ref_sys WHERE auth_name = 'EPSG' AND
auth_srid = 4326</p>
<p class="MsoNormal"
style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">2022-11-28
21:16:51.545 UTC [504] LOG: execute <unnamed>: CREATE
TEMPORARY TABLE "bazinga" ( "gfw_fid" SERIAL, PRIMARY KEY
("gfw_fid"), "fid" NUMERIC(11,0), "geom"
geometry(MULTIPOLYGON,4326) )</p>
<p class="MsoNormal"
style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">2022-11-28
21:16:51.575 UTC [504] LOG: execute <unnamed>: COMMENT
ON TABLE "pg_temp_1"."bazinga" IS NULL</p>
<p class="MsoNormal"
style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">2022-11-28
21:16:51.575 UTC [504] ERROR: relation "pg_temp_1.bazinga"
does not exist</p>
<p class="MsoNormal"
style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">2022-11-28
21:16:51.575 UTC [504] STATEMENT: COMMENT ON TABLE
"pg_temp_1"."bazinga" IS NULL</p>
<p class="MsoNormal"
style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">2022-11-28
21:16:51.585 UTC [504] ERROR: current transaction is aborted,
commands ignored until end of transaction block</p>
<p class="MsoNormal"
style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">2022-11-28
21:16:51.585 UTC [504] STATEMENT: COPY "pg_temp_1"."bazinga"
("geom", "fid") FROM STDIN;</p>
<p class="MsoNormal"
style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">2022-11-28
21:16:51.587 UTC [504] LOG: execute <unnamed>: ROLLBACK</p>
<p class="MsoNormal"
style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)"><br>
</p>
<p class="MsoNormal"
style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">#
END LOG</p>
<p class="MsoNormal"
style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)"><br>
</p>
<p class="MsoNormal"
style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)"><br>
</p>
<p class="MsoNormal"
style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">I
have tried many variations of quoting the table and schema
name to no avail (including</p>
<p class="MsoNormal"
style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">
-nln "\"$TEMP_SCHEMA\".\"$TEMP_TABLE\"" \</p>
<p class="MsoNormal"
style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">).</p>
<p class="MsoNormal"
style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)"><br>
</p>
<p class="MsoNormal"
style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">I
also tried wrapping the whole thing in a transaction by
specifying</p>
<p class="MsoNormal"
style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">
-doo PRELUDE_STATEMENTS="BEGIN;" \</p>
<p class="MsoNormal"
style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">
-doo CLOSING_STATEMENTS="COMMIT;" \</p>
<p class="MsoNormal"
style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)"><br>
</p>
<p class="MsoNormal"
style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">Can
anyone explain how to get this to work? I actually first tried
to create a temporary table by myself with -doo, but ran into
other problems and will leave that for another thread.</p>
<p class="MsoNormal"
style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)"><br>
</p>
<p class="MsoNormal"
style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">Thanks!</p>
<p class="MsoNormal"
style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">Daniel
Mannarino</p>
<p class="MsoNormal"
style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)"><br>
</p>
<p class="MsoNormal"
style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">PS:
Here's the Shapefile I'm using:<span
class="gmail-Apple-converted-space"> </span><a
href="https://github.com/wri/gfw-data-api/blob/master/tests/fixtures/test.shp.zip"
title="https://github.com/wri/gfw-data-api/blob/master/tests/fixtures/test.shp.zip"
moz-do-not-send="true" class="moz-txt-link-freetext">https://github.com/wri/gfw-data-api/blob/master/tests/fixtures/test.shp.zip</a></p>
<p class="MsoNormal"
style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">PPS:
I am running PostgreSQL 12 (specifically the
postgis/postgis:12-2.5-alpine Docker image) and running
ogr2ogr from Homebrew's GDAL 3.5.3 package on MacOS 12.6
(amd64).</p>
</div>
<br>
<fieldset class="moz-mime-attachment-header"></fieldset>
<pre class="moz-quote-pre" wrap="">_______________________________________________
gdal-dev mailing list
<a class="moz-txt-link-abbreviated" href="mailto:gdal-dev@lists.osgeo.org">gdal-dev@lists.osgeo.org</a>
<a class="moz-txt-link-freetext" href="https://lists.osgeo.org/mailman/listinfo/gdal-dev">https://lists.osgeo.org/mailman/listinfo/gdal-dev</a>
</pre>
</blockquote>
<pre class="moz-signature" cols="72">--
<a class="moz-txt-link-freetext" href="http://www.spatialys.com">http://www.spatialys.com</a>
My software is free, but my time generally not.</pre>
</body>
</html>