starefossen / docker-pgrouting

:whale: Docker Image for pgRouting with PostGIS and official Postgres Docker Image

Docker pgRouting

The starefossen/pgrouting Image provides a Docker Image with Postgres, PostGIS, and pgRouting installed. This image is based on the postgres and mdillon/postgis Docker Images.

Supported tags and respective Dockerfile links

The following Docker Image tags are supported.

Tag	Postgres	PostGIS	pgRouting
latest (Dockerfile)	latest	latest	latest
9-2-2 (Dockerfile)	9.6	2.x	2.x
9.4-2.1-2.1 (Dockerfile)	9.4.x	2.1.x	2.1.x
9.4-2.1-2.0 (Dockerfile)	9.4.x	2.1.x	2.0.x
9.6-2.3-2.3 (Dockerfile)	9.6.x	2.3.x	2.3.x
10.1-2.4-2.5 (Dockerfile)	10.1.x	2.4.x	2.5.x

What is pgRouting

pgRouting extends the PostGIS / PostgreSQL geospatial database to provide geospatial routing functionality.

How to use this image

In order to run a basic container capable of serving a pgRouting-enabled database, start a container as follows:

$ docker run --name some-pgrouting \
  -e POSTGRES_PASSWORD=mysecretpassword \
  -d starefossen/pgrouting

For more detailed instructions about how to start and control your Postgres container, see the documentation for the postgres image here.

Once you have started a database container, you can then connect to the database as follows:

$ docker run -it --link some-pgrouting:postgres --rm postgres \
  sh -c 'exec psql -h "$POSTGRES_PORT_5432_TCP_ADDR" -p "$POSTGRES_PORT_5432_TCP_PORT" -U postgres'

Image Variants

The starefossen/pgrouting images come in two flavors, each designed for a specific use case.

starefossen/pgrouting:latest

This is the bleeding edge latest version of the image. You should use this under active development, but not for stable production usage.

starefossen/pgrouting:<version>

This is the locked down version of the image. If comes in two variants, major versions major+minor for Postgres, PostGIS, and pgRouting as the version tag.

License

This Docker image is licensed under the MIT License.

Software contained in this image is licensed under the following:

• PostgreSQL: PostgreSQL License
• PostGIS: GNU GPL v2
• pgRouting: GNU GPL v2

Supported Docker versions

This image is officially supported on Docker version 1.8.1.

Support for older versions (down to 1.0) is provided on a best-effort basis.

User Feedback

Documentation

• Postgres
• PostGIS
• pgRouting

Issues

If you have any problems with or questions about this image, please contact us through a GitHub issue.

Contributing

You are invited to contribute new features, fixes, or updates, large or small; we are always thrilled to receive pull requests, and do our best to process them as fast as we can.

Before you start to code, we recommend discussing your plans through a GitHub issue, especially for more ambitious contributions. This gives other contributors a chance to point you in the right direction, give you feedback on your design, and help you find out if someone else is working on the same thing.

docker-pgrouting's Issues

could not access file "$libdir/libpgrouting-2.5"

db_1 | 2018-07-03 15:01:31.624 UTC [31] STATEMENT: CREATE EXTENSION pgrouting
db_1 | 2018-07-03 15:01:42.165 UTC [31] ERROR: could not access file "$libdir/libpgrouting-2.5": No such file or directory
db_1 | 2018-07-03 15:01:42.165 UTC [31] CONTEXT: SQL function "pgr_bddijkstra" statement 1

now i use this,please fix it:
docker exec -it bash:
ln -s usr/lib/postgresql/10/lib/libpgrouting-2.6.so usr/lib/postgresql/10/lib/libpgrouting-2.5.so

Upgrade to pgRouting v2.5.4

A new release of pgRouting 2.5.x and 2.6.x is available that fix a server crash for many functions.
It could be good to upgrade to the last version on all docker images.

Tag and actually installed version are not consistent (10.1-2.4-2.5)

Hi, thank you for providing the image 😁

Contrary to the tag, actually installed versions are:

• postgresql 10.6
• postgis 2.5.1
• pgrouting 2.6.1

psql (11.1, server 10.6 (Debian 10.6-1.pgdg90+1))
Type "help" for help.

postgres=# \dx
                                            List of installed extensions
          Name          | Version |   Schema   |                             Description
------------------------+---------+------------+---------------------------------------------------------------------
 fuzzystrmatch          | 1.1     | public     | determine similarities and distance between strings
 pgrouting              | 2.6.1   | public     | pgRouting Extension
 plpgsql                | 1.0     | pg_catalog | PL/pgSQL procedural language
 postgis                | 2.5.1   | public     | PostGIS geometry, geography, and raster spatial types and functions
 postgis_tiger_geocoder | 2.5.1   | tiger      | PostGIS tiger geocoder and reverse geocoder
 postgis_topology       | 2.5.1   | topology   | PostGIS topology spatial types and functions
(6 rows)

PGRouting 2.1.0

It seems that there is no docker file for pgrouting 2.1.0. Is there an easy way to get pgrouting 2.1.0 in this container?

error parsing HTTP 403 response body: invalid character '<'

When running:

docker run --name gis -p 5432:5432 -e POSTGRES_USER=$PGUSER -e POSTGRES_PASSWORD=mysecretpassword -e POSTGRES_DB=gis -d starefossen/pgrouting

We get:

C:\Program Files\Docker Toolbox\docker.exe: error parsing HTTP 403 response body: invalid character '<' looking for beginning of value: "<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n<Error><Code>InvalidAccessKeyId</Code><Message>The AWS Access Key Id you provided does not exist in our records.</Message><AWSAccessKeyId>AKIAIF2ALI2VM33FK36Q</AWSAccessKeyId><RequestId>3A3BD7D837D70686</RequestId><HostId>qTDAYO3py7Ta1V+kT+C1FBCDV0lSYUXqJY0zKAnVOdDzuy4kKalbNwcwVRQRygzB7KIaS8wYmV0=</HostId></Error>".
See 'C:\Program Files\Docker Toolbox\docker.exe run --help'.

Workaround is to pin version to 9.4-2.1-2.0; make the run command:

docker run --name gis -p 5432:5432 -e POSTGRES_USER=$PGUSER -e POSTGRES_PASSWORD=mysecretpassword -e POSTGRES_DB=gis -d starefossen/pgrouting:9.4-2.1-2.0

PGROUTING_VERSION doenst control pgrouting version

In an effort to try using postgres 10 with pgrouting 2.2.2 (because of a bug in node network) I tried switching the PGROUTING_VERSION and rebuilding but it still uses 2.5.2 since postgresql-$PG_MAJOR-pgrouting always pulls down the latest pgrouting. The only thing that I can think of doing would be to pull directly from pgrouting on github and compile from source if you wanted to specify the version.

Publish install instructions to pgRouting/pgrouting/wiki

We have a wiki page, about installation, if it is not much to ask, can you write in the wiki about how people using Docker can get pgRouting.

https://github.com/pgRouting/pgrouting/wiki/Notes-on-Download%2C-Installation-and-building-pgRouting

Chat History

Importing Data Challanges

1. Imported Data Using osm2pgsql (Some memory issues but finally the following worked

export PGPASS=mysecretpassword
osm2pgsql
--host 172.17.0.2
--port 5432/tcp
--database postgres
--username postgres
--cache 14000
--slim
--drop
/mnt/xvdf/north-america-latest.osm

However, When I looked into using PGROUTING functions, it looks like I need to import data using:
osm2pgrouting
-f /mnt/xvdf/north-america-latest.osm
-c /usr/share/osm2pgrouting/mapconfig.xml
--clean 1
-d routing
-u postgres
-h 172.17.0.2
-p 5432/tcp
--passwd mysecretpassword

Problem: osm2pgrouting requires I run:

add PostGIS functions

psql -d routing -f /usr/share/postgresql/8.4/contrib/postgis-1.5/postgis.sql
psql -d routing -f /usr/share/postgresql/8.4/contrib/postgis-1.5/spatial_ref_sys.sql

add pgRouting core functions

psql -d routing -f /usr/share/postlbs/routing_core.sql
psql -d routing -f /usr/share/postlbs/routing_core_wrappers.sql
psql -d routing -f /usr/share/postlbs/routing_topology.sql

I realize the files have moved

/usr/share/postgresql/9.3/contrib/postgis-2.1/postgis.sql

I called docker run pointing to /usr/share/postgresql/9.3/contrib/postgis-2.1/postgis.sql
docker run -it --link some-pgrouting:postgres --rm postgres
sh -c 'exec psql -h "$POSTGRES_PORT_5432_TCP_ADDR" -p "$POSTGRES_PORT_5432_TCP_PORT" -U postgres
-d routing -f /usr/share/postgresql/9.3/contrib/postgis-2.1/postgis.sql'

But I can't get docker to see the file.
I have tried copying the file into the docker image.
docker cp /usr/share/postgresql/9.3/contrib/postgis-2.1/postgis.sql some-pgrouting:/postgis.sql

docker run -it --link some-pgrouting:postgres --rm postgres
sh -c 'exec psql -h "$POSTGRES_PORT_5432_TCP_ADDR" -p "$POSTGRES_PORT_5432_TCP_PORT" -U postgres
-d routing -f postgis.sql'

I have also logged into the docer image:
docker run -it --link some-pgrouting:postgres --rm postgres
sh -c 'exec psql -h "$POSTGRES_PORT_5432_TCP_ADDR" -p "$POSTGRES_PORT_5432_TCP_PORT" -U postgres -d routing'

and attempted:
\i /usr/share/postgresql/9.3/contrib/postgis-2.1/postgis.sql
and
\i postgis.sql

to no avail. How can I see these files? Or is there an easier way to get pgrouting functions to work?

P.S. If there is a way I can pay you back like sending you some cash via Paypal, just send me your email.

How would I add OSM data to this Docker Image

I seem not to be able to get to a command prompt. Only access to psql. So how would I run Osm2pgsql to import OSM datafiles into the postgres database.

Container initialization fails when POSTGRES_USER != postgres

Container initialization fails with error in case when environment variable POSTGRES_USER is set to something that doesn't equals to postgres.

Initialization error:

...
/usr/local/bin/docker-entrypoint.sh: running /docker-entrypoint-initdb.d/routing.sh
2019-04-23 13:27:42.903 UTC [96] FATAL:  role "postgres" does not exist

To reproduce:
docker run -e POSTGRES_USER=test -e POSTGRES_DB=test -e POSTGRES_PASSWORD=test starefossen/pgrouting

docker exec -it 9a5c836d34f0 -c 'osm2pgrouting -f FORTALEZA.osm -h 172.19.0.3 -p 5432 -d routing' OCI runtime exec failed: exec failed: container_linux.go:345: starting container process caused "exec: \"-c\": executable file not found in $PATH": unknown

how do i run a command to import data with osm2pgrouting?
I use this command:
docker exec -it 9a5c836d34f0 -c 'osm2pgrouting -f FORTALEZA.osm -h 172.19.0.3 -p 5432 -d routing' bash

and I get this error:
docker exec -it 9a5c836d34f0 -c 'osm2pgrouting -f FORTALEZA.osm -h 172.19.0.3 -p 5432 -d routing' OCI runtime exec failed: exec failed: container_linux.go:345: starting container process caused "exec: "-c": executable file not found in $PATH": unknown

Add osm2pgrouting to the installation

Can simply be installed by adding it to the apt-get install section. This way a great tool would be available within the image to import osm files. I suggest also adding wget so downloading osm files and importing them into the database can be done from within the image.

ERROR: could not access file "$libdir/librouting": No such file or directory

I run this Docker image using the following command:

docker run -d --name ${POSTGRES_CONTAINER_NAME} -p 5432:5432 -e POSTGRES_USER="${POSTGRES_USER}" -e POSTGRES_PASSWORD="${POSTGRES_PASSWORD}" -v ${POSTGRES_HOST_DATA_PATH}:/var/lib/postgresql/data starefossen/pgrouting:9.4-2.1-2.1

When we run PGRouting queries, like:

SELECT seq, id1 vertex_id, id2 line_id, cost
FROM pgr_dijkstra(
  'SELECT id, source::int, target::int, cost FROM osm_2po_4pgr WHERE geom_way && ST_Expand(ST_SetSRID(ST_MakeEnvelope(-87.6902068, 41.8844823, -87.6363398, 41.939693), 4326), 0.02)',
  6276441, 6334195, false, false
)

It gave me this error:

ERROR:  could not access file "$libdir/librouting": No such file or directory

********** Error **********

ERROR: could not access file "$libdir/librouting": No such file or directory
SQL state: 58P01

Last time, I run from latest (it should be 9.4-2.1-2.0 at that time) and it run properly. But, when I re-download the latest or 9.4-2.1-2.1, it is broken.

Latest is still 9.6 instead of the 10.1

If you pull the latest tag from docker hub it still is attached to 9.6 instead of the 10.1 version of Postgres.