[GIS] Exporting table with ogr2ogr

ogr2ogrpostgissql

I am importing some layers in postgis. Now I will do a SQL-query and the result should be exported as a MapInfo-file:

SET PGCLIENTENCODING=LATIN1
set MAPSCHEMA=public
set PSQLPATH="C:\Program Files\PostgreSQL\9.5\bin"
set OGR2OGRPATH="C:\Program Files\QGIS 2.14\bin"
set ACDCONN="PG: host=localhost port=5432 dbname=test_mo user=postgres password=admin"
set INPATH_SCHABLONE=C:\Users\LiM\Desktop\batch_ExtractedOcean\schablone
set INPATH="R:\TomTom\Africa\2015_12\mn_inkl_POI\
set OUTPATH=C:\Users\LiM\Desktop\batch_ExtractedOcean\Extracted_MapInfo

echo importing data into POSTGIS

set sIU=reu_oin_oi2
%OGR2OGRPATH%\ogr2ogr -f "PostgreSQL" %ACDCONN% %INPATH_SCHABLONE%\CutAFROceanTT.TAB
%OGR2OGRPATH%\ogr2ogr -f "PostgreSQL" %ACDCONN% %INPATH%%sIU%\Mapinfo\%sIU%_area_orig.MIF

echo exporting  to MapInfo files (tab/mif/mid)...
%OGR2OGRPATH%\ogr2ogr.exe -f "MapInfo File" %OUTPATH%\%sIU%_area.tab %ACDCONN% -sql "SELECT * FROM CutAFROceanTT"

The import is working but the export is not working. The file has been created but the file is empty. I can Export a hole table inside PostGIS but I only want to export the result of the query.

Where is my mistake?

Best Answer

Trying to explain in notes above is getting a bit messy, so here is answer

So here is the rule. As I recall, ogr passes any SQL literally.

So that means that it will pass:

SELECT * FROM CutAFROceanTT;

Which since your table is not quoted, PostgreSQL will convert to:

SELECT * FROM cutafroceantt;

You can confirm that your query doesn't work by using psql. I suspect your table is called CutAFROceanTT.

Which means your SQL needs to be written as:

SELECT * FROM "CutAFROceanTT"

What I'm not absolutely sure about is how to include quotes within an already quoted piece of text. AS I recall, I think you need to do:

%OGR2OGRPATH%\ogr2ogr.exe -f "MapInfo File" %OUTPATH%\%sIU%_area.tab %ACDCONN% -sql "SELECT * FROM ""CutAFROceanTT"" "
Related Question