Getting query output from ogr2ogr when loading data?

Question

I am trying to load a shapefile into a PostGIS instance using ogr2ogr. I am getting an odd error that I think might have to do with the specific version of PostGIS that we are using.

In order to debug what ogr2ogr is trying to do I would like to see the sql query that is generated behind the scenes.

I believe that Getting query output from ogr2ogr? is trying to get at the same thing. Like this OP, I am interested in reproducing the functionality available with shp2pgsql, which produces the create and insert statements as outputs with:

shp2pgsql -s 4326 shapefile.shp geodata_tablename > statements.sql

statements.sql then contains the create table statements that were used as well as the individual insert statements.

• user30184: Perhaps it would help to try the pgdump driver gdal.org/drv_pgdump.html. SQL is propably not exactly same but may still help with debugging. Also adding -- debug on into your ogr2ogr command may print usable information.
• Dave-Evans: Yes! PGDump is exactly what I'm looking for. Thank you so much. If you want to move this to an answer I can mark it as the accepted.

Answer 1

The pgdump driver http://www.gdal.org/drv_pgdump.html is what you need. It writes a PostgreSQL SQL dump file as plain text for you. Here is an example about how it looks:

SET standard_conforming_strings = OFF;
DROP TABLE IF EXISTS "public"."polygons" CASCADE;
DELETE FROM geometry_columns WHERE f_table_name = 'polygons' AND f_table_schema = 'public';
BEGIN;
CREATE TABLE "public"."polygons" ( "ogc_fid" SERIAL, CONSTRAINT "polygons_pk" PRIMARY KEY ("ogc_fid") );
SELECT AddGeometryColumn('public','polygons','wkb_geometry',-1,'POLYGON',2);
CREATE INDEX "polygons_wkb_geometry_geom_idx" ON "public"."polygons" USING GIST ("wkb_geometry");
COMMENT ON TABLE "public"."polygons" IS NULL;
INSERT INTO "public"."polygons" ("wkb_geometry" ) VALUES ('010300000001000000040000000000000000507C400000000......

Answer 2

You can't read a .SQL with OGR you can to issue each one as it's one line as part of OGRINFO or OGR2OGR. If you were interacting with SQLite then you could use SQLite3 command line .read

• Dave-Evans: I'm not quite sure what you are saying here. You can in fact run sql from a .sql file with ogr2ogr, gis.stackexchange.com/questions/185072/…. What do you mean "you can to issue each one"?
• Jordan Bess: WOW I was not aware that you could do that. I am always making huge batch scripts this will save me tremendously. --optfile C:/TEMP/sql.sql does the .sql file need to have -sql "statement"
• Dave-Evans: I suggest you ask this at the aforementioned question.