I have a hunch about this, but you’ll need to try this out to see if it’s correct :-)
As user30184 pointed out, it looks like you’re running a PostGIS query on a database without the PostGIS extension installed.
The mdillon/postgis dockerfile copies a file (as postgis.sh) into the same directory as your .sql files.
postgis.sh sets up a Postgres template, then it installs extensions (including PostGIS) on all databases.
This dockerfile doesn’t have a CMD or ENTRYPOINT directive. So when you run the container, the start point is really implemented in a base image (the standard docker postgres image). That entrypoint is docker-entrypoint.sh .
When docker-entrypoint.sh runs, it finds .sql scripts and .sh scripts in the /docker-entrypoint-initdb.d/
directory and executes them. You can see the code for that here
Your scripts start with an “n”, so they run before the “postgis.sh” script. That may explain your errors.
You could try renaming your scripts to go after “postgis.sh” in the alphabet, e.g. “z_myscript.sql”. Or even “postgis_myscript.sql”
If that works it’s of a bit of a kludge, there are probably (several) better ways to do this :-)
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'"
Best Answer
First, you need to ensure your Docker container has it's ports exposed, so do something like:
And now, depending on your platform, you should be able to connect to it using QGIS by setting the host to
localhost
, the username topostgres
an the password tomysecretpassword
. It should be clear how to change the rest.Notes on Docker usage from here: https://hub.docker.com/r/mdillon/postgis/
Caveat for the old way of running Docker on Windows. If you are using the Virtualbox method of running Docker on Windows, i.e., Docker Machine, you need to get the Docker IP address using
docker-machine ip
. See: https://docs.docker.com/machine/reference/ip/