Importing Data Challanges

[dirklieske]

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.

[dirklieske]

I figured out how to move files between the docker and the host (And where to locate the sql files. I also identified that I don't need to run the SQL files (Since they have already been run in public)

But I still haven't figured out how to load data using osm2pgrouting

[Starefossen]

Sorry for not answering you earlier @dirklieske! I will look into this issue later tonight. Ok?

[dirklieske]

Ok,

I finally got osm2pgrouting to work (Basically I needed a really big Box) So I provisioned a "m4.10xlarge" on Amazon box and processed for 48 hours to get a North America OSM file set up (48,000,000,000 ways) Now I just need to test out how the functions work against the data. (It is too bad I had to process that OSM file into ways data for pgrouting to work, since the processing of that data wasn't free, but once loaded into the database the data wasn't all that big. (Maybe some setting changes would have made the osm2pgrouting would have made it run faster, but I really only need to run it once) Thanks

[Starefossen]

Great to hear it worked out! Feel free to open another issue if you have any other questions about pgRouting and I will answer them to the best of my ability 😄