[GIS] `ogr2ogr` command doesn’t run the same through Docker

dockergdalogr2ogr

I have the following command:

ogr2ogr --debug ON -f 'KML' ./areas_desarrollo_indigena_2017_03_07.kml PG:"host=localhost port=5432 dbname=mapserver user=postgres password=" -sql "SELECT * from ( SELECT ST_Intersection(datasrc.geom, polygon) as sharedPolygon, datasrc.* from ST_MakePolygon(ST_GeomFromText('LINESTRING(-69.02160644531251 -20.053351219365318,-69.78515625000001 -20.45789615504684,-69.09851074218751 -20.979392255760608,-69.02160644531251 -20.053351219365318)',4326)) as polygon,datasources.areas_desarrollo_indigena_2017_03_07_15 as datasrc) as sharedPolygon WHERE ST_IsEmpty(sharedPolygon)='false'"

Which runs perfectly. Notice the added --debug ON, I've run it with and without debug.

Now I want to execute the same command through docker because I want to target a specific GDAL version:

docker run "geodata/gdal:2.1.3" ogr2ogr --debug ON -f 'KML' ./areas_desarrollo_indigena_2017_03_07.kml PG:"host=localhost port=5432 dbname=mapserver user=postgres password=" -sql "SELECT * from ( SELECT ST_Intersection(datasrc.geom, polygon) as sharedPolygon, datasrc.* from ST_MakePolygon(ST_GeomFromText('LINESTRING(-69.02160644531251 -20.053351219365318,-69.78515625000001 -20.45789615504684,-69.09851074218751 -20.979392255760608,-69.02160644531251 -20.053351219365318)',4326)) as polygon,datasources.areas_desarrollo_indigena_2017_03_07_15 as datasrc) as sharedPolygon WHERE ST_IsEmpty(sharedPolygon)='false'"

Notice I've just prepended this: docker run "geodata/gdal:2.1.3" (I've also tried specifying the image without quotes, so that's not and issue).

This command complains it cannot connect to DB and also (see last error message line) interprets my Postgresql connection string as another datasource.

This is the output when I run the command through Docker:

ERROR 1: PQconnectdb failed.
could not connect to server: Connection refused
    Is the server running on host "localhost" (127.0.0.1) and accepting
    TCP/IP connections on port 5432?
could not connect to server: Cannot assign requested address
    Is the server running on host "localhost" (::1) and accepting
    TCP/IP connections on port 5432?

FAILURE:
Unable to open datasource `PG:host=localhost port=5432 dbname=mapserver user=postgres password=' with the following drivers.

Also it doesn't honor --debug ON parameter, it just spits previous string followed by the formats it supports.

UPDATE: Just found that I need to connect local database with container through Docker parameter --net, so the new command is:

docker run --rm --net=host -v /tmp/1:/data "geodata/gdal:2.1.3" ogr2ogr --debug ON -f 'KML' /tmp/1/areas_desarrollo_indigena_2017_03_07.kml PG:"host=localhost port=5432 dbname=mapserver user=postgres password=" -sql "SELECT * from ( SELECT ST_Intersection(datasrc.geom, polygon) as sharedPolygon, datasrc.* from ST_MakePolygon(ST_GeomFromText('LINESTRING(-69.02160644531251 -20.053351219365318,-69.78515625000001 -20.45789615504684,-69.09851074218751 -20.979392255760608,-69.02160644531251 -20.053351219365318)',4326)) as polygon,datasources.areas_desarrollo_indigena_2017_03_07_15 as datasrc) as sharedPolygon WHERE ST_IsEmpty(sharedPolygon)='false'"

However now it cannot write the output file despite the -v /tmp/1:/data, now it says:

PG: DBName="mapserver"
PG: PostgreSQL version string : 'PostgreSQL 10.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.2.0, 64-bit'
PG: PostGIS version string : '2.4 USE_GEOS=1 USE_PROJ=1 USE_STATS=1'
GDAL: GDALOpen(PG:host=localhost port=5432 dbname=mapserver user=postgres password=X this=0x23d1a20) succeeds as PostgreSQL.
GDAL: GDALDriver::Create(KML,/tmp/1/areas_desarrollo_indigena_2017_03_07.kml,0,0,0,Unknown,(nil))
KML: Attempt to create: /tmp/1/areas_desarrollo_indigena_2017_03_07.kml
ERROR 4: Failed to create KML file /tmp/1/areas_desarrollo_indigena_2017_03_07.kml.
ERROR 1: KML driver failed to create /tmp/1/areas_desarrollo_indigena_2017_03_07.kml
GDAL: GDALClose(PG:host=localhost port=5432 dbname=mapserver user=postgres password=X this=0x23d1a20)

Best Answer

Found the second problem, first one was the missing --net=host parameter to connect inner command with outside Postgresql, as @user30184 correctly pointed out.

The second problem, not writing the KML file, was due to the wrong path in the file, which was /tmp/1/areas_desarrollo_indigena_2017_03_07.kml and since I added the -v /tmp/1:/data it was trying to write it into the wrong place (there's no /tmp/1 inside the container.

So the fixed command is:

docker run --rm --net=host -v /tmp/1:/data "geodata/gdal:2.1.3" ogr2ogr --debug ON -f 'KML' areas_desarrollo_indigena_2017_03_07.kml PG:"host=localhost port=5432 dbname=mapserver user=postgres password=" -sql "SELECT * from ( SELECT ST_Intersection(datasrc.geom, polygon) as sharedPolygon, datasrc.* from ST_MakePolygon(ST_GeomFromText('LINESTRING(-69.02160644531251 -20.053351219365318,-69.78515625000001 -20.45789615504684,-69.09851074218751 -20.979392255760608,-69.02160644531251 -20.053351219365318)',4326)) as polygon,datasources.areas_desarrollo_indigena_2017_03_07_15 as datasrc) as sharedPolygon WHERE ST_IsEmpty(sharedPolygon)='false'"
Related Question