2

I've been looking into this matter for a few days now, but I haven't come up with a working solution.

Basically, what we are trying to do is create an entire world database, preferably in PostgreSQL, using PostGIS. Our world file is the whole planet data, in a pbf format (~34GB).

So far, I have tried using the osm2pgrouting and osm2po tools to generate this database, but both tools failed for the setup that we are using.

Our machine is a linux x64 ubuntu-server, with 20 GB of RAM plus 20 GB of swap area. We are using an HHD of 2TB for this.

In my last try with osm2po, I tried running it with 18 GB of ram (-Xmx18432m), but it failed after some time. I will be trying again with more memory allocated to the java virtual machine and report the results.

But mainly, my main questions here are:

  • Is it possible to generate a routing database for the entire world with these tools and our current machine?
  • Is RAM allocation the only optimization that I can do to allow osm2po to handle large files?
  • Can osm2po save nodes tags in the PostgreSQL database? I know this is possible for the ways table, since it already exists in the database and even has a column called osm_meta, and I also know that it is possible to retrieve this info while reading osm data file, but my guess is that this data will only be available to the front-end part of osm2po.
0

A month ago a friend of mine asked me to convert the topology for all walkable streets of the planet file with osm2po. We finally agreed to exclude motorways but to include everything from trunk to track. It took about 5 hours on (don't ask me for details) a dedicated linux server with 24Gig RAM. Okay, osm2po does lot's of preprocessing stuff and applies filters in order to reduce data as early as possible. If you really need each piece of information, you've got to fall back to databasing and have to wait for days, not for hours.

You asked Is RAM allocation the only optimization that I can do to allow osm2po to handle large files? ...

No, there is a tiling-strategy which exactly will be needed here

You asked Can osm2po save nodes tags ...

It depends on what you you are going to convert. Simple cases like taking bollards or traffic signals into account are possible and will be written to the SQL respectively.

  • Could you elaborate a little more about the tilling-strategy things that you mentioned? As for the tags that we want to keep, those would be mostly traffic lights, bumpers, stop signs, and whatever can make your car stop or slowdown. I believe most of these are kept in nodes, but I am not very familiar with the OSM data. Also, I've seen a post where someone wrote a plugin for rail roads scheduling, but I failed to reproduce that. I will retry this in the future, though. The traffic lights example tough, does not generate any extra entries in the database, AFAIK. – Canella Jun 26 '14 at 21:12
  • Please read current on-screen help on the web page and look for tileSize. Furthermore look at this interesting discussion: gis.stackexchange.com/questions/57242/… – Carsten Jun 27 '14 at 17:14
1

I was able to import a full planet file in about 5 days using osm2pgsql with almost the same machine specs you have.

You could do that, then use the PGRouting Calculate Topology functions (plus the other info on that page) to build your network.

This has the added advantage of giving you a PostGis database with all the tags as well to use for queries or Map Rendering.

Have a look at THIS Blog Post but be aware that you need to change the Create Table Statement to the following:

create table network(gid bigserial, osm_id bigint, name varchar, the_geom geometry, source bigint, target bigint, length float);

Installing PGSql is a breeze on Trusty, with Postgres 9.3 I did the following:

sudo apt-get install postgresql-9.3-pgrouting
sudo apt-get update

psql -U username -d dbname
CREATE EXTENSION pgrouting;
/q

Then I ran the Sql from the blog post above to split the ways into routeable segments and create a network graph, however I found I had to modify it to not fail on errors and remove the NOTICES, so here is wht I used

drop table if exists network;
create table network(gid bigserial, osm_id bigint, name varchar, the_geom geometry, source bigint, target bigint, length float);


CREATE OR REPLACE FUNCTION compute_network() RETURNS text as $$
DECLARE
streetRecord record;
wayRecord record;
pointCount integer;
pointIndex integer;
geomFragment record;
BEGIN
-- for each street

--FOR streetRecord in select way, osm_id, name from planet_osm_line where highway is not null and st_contains((select way from planet_osm_polygon where boundary = 'administrative' and name like 'Sector 1' limit 1), way) LOOP

    FOR streetRecord in select way, osm_id, name from planet_osm_line where highway is not null LOOP

   -- for each street in the region of interest
   SELECT * from planet_osm_ways where id = streetRecord.osm_id into wayRecord; 

   BEGIN
       FOR pointIndex in array_lower(wayRecord.nodes, 1)..array_upper(wayRecord.nodes,1)-1 LOOP
           -- RAISE NOTICE 'Inserting name % source %, target %', streetRecord.name, wayRecord.nodes[pointIndex], wayRecord.nodes[pointIndex+1];
           select st_makeline(st_pointn(streetRecord.way, pointIndex), st_pointn(streetRecord.way, pointIndex+1)) as way into geomFragment;
           insert into network(osm_id, name, the_geom, source, target, length) values(streetRecord.osm_id, streetRecord.name, geomFragment.way, wayRecord.nodes[pointIndex], wayRecord.nodes[pointIndex+1], st_length(geomFragment.way));
       END LOOP;
   EXCEPTION
       WHEN OTHERS THEN
                RAISE NOTICE 'ERROR: on streetRecord Id - % - Name < % >' ,streetRecord.osm_id,  streetRecord.name;
   END;
END LOOP;

 return 'Done';
END;
$$ LANGUAGE 'plpgsql';

select * from compute_network();

The blog is also incorrect, it does not use assign_vertex, it should be

SELECT pgr_createTopology('network', 0.00001, 'the_geom', 'gid');

I have not actually done any routing yet. Suggest you try it on a small country extract first. We will be doing the whole planet eventually when I am sure I have enough disk space for the network graph

  • 1
    i recommend to modify postgresql.conf to have bigger memory limits after installation – simplexio Jun 26 '14 at 6:39
  • I am doing some tests based on your answer, and I want to point a small observation: osm2pgsql imports by default in spherical mercator coordinates. To change to geographics coordinates I made the following change: FOR streetRecord in select ST_Transform(ST_SetSRID(planet_osm_line.way,900913),4326) as way, osm_id, name, z_order from planet_osm_line where highway is not null LOOP. I will keep looking into this answer to confirm that it attends my needs before marking this one as the solution, 'cause I want to try all approaches first. Also, thanks for taking the time to write it. – Canella Jun 26 '14 at 20:10
  • Can't upvote with less than 15 points :/ – Canella Jun 26 '14 at 20:10
  • A few things that I noticed: Using the osm_id on the source and target columns are being overridden by the pgr_createTopology function. No references to those Ids are being kept in the new tables. It is also not optimizing the database for routing since curves (and even straight roads) that contain a lot of nodes are not being merged into a single geometry line. Maybe I am doing something wrong here? – Canella Jun 26 '14 at 21:01
  • I have not got as far as checking the actual geometry. It would be possible to use st_simplify to reduce the number of nodes on each segment created if you wanted to. – Mark Cupitt Jun 26 '14 at 22:11
0

So, after following Carsten's suggestion from the comments, I could finally generate a routing database for the entire world.

The catch was to use the tileSize command line argument from osm2po. The actual values that I used were java -Xmx8192m -jar osm2po-core-4.8.8-signed.jar prefix=world tileSize=10x10,1.0 planet-140430.osm.pbf in case you are wondering it.

This generated a sql script of 44GB (14GB zipped). osm2poalso keeps the original OSM nodes id in the columns osm_source_id and osm_target_id, which gives me the means to query the tags from a osm2pgsql generated database.

Unfortunately, I couldn't make a optimized routing database from Mark C's suggestion, and I don't think there is an easy way to do that by following that path.

Thank you all for the support.

  • If you need the road network only (no footways, tracks, etc.) tileSize=30x30,1.0,c should do it as well, but a lot faster. Even 45x45,1,c might work, but drives osm2po's to its limit. And... use c – Carsten Jul 10 '14 at 18:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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