FYI Jeremy Palmer has just submitted a fix for that:
https://github.com/OSGeo/gdal/pull/6896
Le 30/11/2022 à 16:12, Daniel Mannarino a écrit :
Hello!
I'm having trouble using the temporary table option of
ogr2ogr:https://gdal.org/drivers/vector/pg.html#layer-creation-options
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.
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":https://github.com/OSGeo/gdal/blob/dc39f7d68095621842a2547ab06c60a03fd34a19/ogr/ogrsf_frmts/pg/ogrpgdatasource.cpp#L1865-L1872
It sounds to me from the Postgres docs this isn't safe to assume, but
let's say it is for the moment.
Here is my simplified script:
#!/bin/bash
set -e
PGHOST="<host>"
PGDATABASE="<db>"
PGUSER="<user>"
PGPASSWORD="<pass>"
PGPORT=<port>
LOCAL_FILE="/vsizip/test.shp.zip"
SRC_LAYER="test"
FID_NAME="gfw_fid"
GEOMETRY_NAME="geom"
TEMP_SCHEMA="pg_temp_1"
TEMP_TABLE="bazinga"
ogr2ogr -f "PostgreSQL" PG:"password=$PGPASSWORD host=$PGHOST
port=$PGPORT dbname=$PGDATABASE user=$PGUSER" \
"$LOCAL_FILE" "$SRC_LAYER" \
-lco GEOMETRY_NAME="$GEOMETRY_NAME" \
-lco SPATIAL_INDEX=NONE \
-lco FID="$FID_NAME" \
-lco TEMPORARY=ON \
-nlt PROMOTE_TO_MULTI \
-nln "$TEMP_SCHEMA.$TEMP_TABLE" \
-t_srs EPSG:4326 \
--config PG_USE_COPY YES \
-makevalid -update
# End of script
Here is the Postgres log from attempting to run this script with the
Shapefile in the PS:
# START LOG
2022-11-28 21:16:51.206 UTC [504] LOG: statement: set client_encoding
to 'UTF8'
2022-11-28 21:16:51.209 UTC [504] LOG: execute <unnamed>: SHOW
client_encoding
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'
2022-11-28 21:16:51.245 UTC [504] LOG: execute <unnamed>: SELECT
version()
2022-11-28 21:16:51.246 UTC [504] LOG: execute <unnamed>: SHOW
standard_conforming_strings
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'
2022-11-28 21:16:51.323 UTC [504] LOG: execute <unnamed>: SELECT
postgis_version()
2022-11-28 21:16:51.326 UTC [504] LOG: execute <unnamed>: SET
ENABLE_SEQSCAN = ON
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
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
2022-11-28 21:16:51.354 UTC [504] LOG: execute <unnamed>: SELECT
ST_Srid('POINT EMPTY'::GEOMETRY)
2022-11-28 21:16:51.356 UTC [504] LOG: execute <unnamed>: SELECT
current_schema()
2022-11-28 21:16:51.394 UTC [504] LOG: execute <unnamed>: BEGIN
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'
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'
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
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
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) )
2022-11-28 21:16:51.575 UTC [504] LOG: execute <unnamed>: COMMENT ON
TABLE "pg_temp_1"."bazinga" IS NULL
2022-11-28 21:16:51.575 UTC [504] ERROR: relation "pg_temp_1.bazinga"
does not exist
2022-11-28 21:16:51.575 UTC [504] STATEMENT: COMMENT ON TABLE
"pg_temp_1"."bazinga" IS NULL
2022-11-28 21:16:51.585 UTC [504] ERROR: current transaction is
aborted, commands ignored until end of transaction block
2022-11-28 21:16:51.585 UTC [504] STATEMENT: COPY
"pg_temp_1"."bazinga" ("geom", "fid") FROM STDIN;
2022-11-28 21:16:51.587 UTC [504] LOG: execute <unnamed>: ROLLBACK
# END LOG
I have tried many variations of quoting the table and schema name to
no avail (including
-nln "\"$TEMP_SCHEMA\".\"$TEMP_TABLE\"" \
).
I also tried wrapping the whole thing in a transaction by specifying
-doo PRELUDE_STATEMENTS="BEGIN;" \
-doo CLOSING_STATEMENTS="COMMIT;" \
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.
Thanks!
Daniel Mannarino
PS: Here's the Shapefile I'm
using:https://github.com/wri/gfw-data-api/blob/master/tests/fixtures/test.shp.zip
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).
_______________________________________________
gdal-dev mailing list
gdal-dev@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/gdal-dev
--
http://www.spatialys.com
My software is free, but my time generally not.
_______________________________________________
gdal-dev mailing list
gdal-dev@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/gdal-dev
