3

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?

3
  • 1
    What's the casing of your tables in postgres. I see your SQL has mixed case tables. I think if really is mixed case, you might need to quote the table name in the -sql. I forget how, but you could try ""
    – Regina Obe
    Feb 22, 2017 at 17:31
  • What do you mena with mixed case? And with quote table name do you mean something like this: -sql "SELECT * FROM public."cutafroceantt"" this doesn´t work too
    – Moehre
    Feb 23, 2017 at 8:15
  • Yes. If you have upper case in your table name, then you'll need to quote it. Sorry meant to say anything that isn't completely lower case rather than mixed case. I think your query might need to be though "SELECT * FROM ""CutAFROCeanTT"" "
    – Regina Obe
    Feb 23, 2017 at 19:55

1 Answer 1

1

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"" "
1
  • Sometimes I find myself wondering why people create tables with capital letters... All this pain could be avoided so easily
    – Magno C
    May 30, 2022 at 19:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.