Thank you (and Jeremy Palmer) so much! I eagerly await the release with the fix. I'd be happy to contribute a usage example to the documentation if that's helpful. Daniel Mannarino
On Sun, Dec 11, 2022 at 4:58 PM Even Rouault <even.roua...@spatialys.com> wrote: > 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 > listgdal-dev@lists.osgeo.orghttps://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
