I'm seeking some advice on how to build a simulation based on OpenStreetMap Data for my masterthesis. My knowledge of spatial data is very limited, since I come from another field of science, namely information systems. I have experience with object oriented programming, as well as mySQL as relational database.

The research goal is to evaluate if applications which inform the user of the current gasoline price actually have the potential to save money. So the idea is to simulate the routes people travel between their home and work by car and when the car needs gasoline to find the nearest gasoline station and refill. As Database I will use data from the www.zensus2011.de which can tell me from which administrative district people leave and to which administrative district they travel.

Within OSM I have the following Tags that can identify the administrative regions/districts which I need

  • de:amtlicher_gemeindeschluessel => integer
  • de:regionalschluessel => integer
  • admin_level => 6
  • boundary => administrative

So my idea is to use this data to generate random routes with a certain length based on other theses which describe how far people travel between home and work. One of the main problems which I could identify from reading the OSM wiki and other online posts is that the OSM data per se is not routable and needs to be transformed for that purpose. I for that I found two programs osm2pgrouting and OSM2PO. With the latter one I managed to import the map of Germany into a PostgreSQL Database. I also imported the OSM data using osm2pgsql with the "default.style" settings into another PostgreSQL Database. Now I'm a little overwhelmed by all the data and the way how to use this data.

But let's continue with the idea. What I need to understand now is how the map data with all the information and the routing data can be connected to generate the routes. What I thought of was: I use the administrative boundary to get a list of streets within this boundary, then randomly choose one as a starting point and do the same for the destination, calculate the route and save the route to a database for further use in the simulation. Still I need to find out a way to determine the start and destination points better, but for the beginning and understanding it would be fine with the above.

Some final words on the implementation of the simulation. Currently my plan is to use python and code the whole simulation, which I hope is feasible. I found two related works which I'm currently investigating namely MATSim and www.roaf.de. Also I'm still browsing other questions here which might help me understand the topic.

To summarize my questions:

  • Is the way I choose feasible?
  • How would SQL statements look like to connect the two Data sets?
  • Is it possible to save generated routes to a database?
  • Why choose randomly? If you spread the points randomly and then assign each a nearest point on the road and then calculate route for each you should get proportions of which roads are used (probably there are main routes out of each zone). You could build a heat map, calculate average distance etc. Aug 20 '14 at 20:11

Long question, short answer :)

ad. 1 - Yes
ad. 2 It would be better if you have this two datasets in one database, database have to be spatially enabled. Postgis extension gives you lots of functions to access, create, edit and compare spatial data e.g.

select * 
from hh_2po_4pgr routing
join planet_osm_polygon poly on poly.boundary = 'administrative' and st_intersects(routing.way,poly.way)
where poly.name = 'Berlin' 

This should give you all edges laying inside or intersecting with boundary of Berlin

ad. 3 Yes


I suggest another option:

  1. Write procedure to generate random numbers in range
  2. Find a bbox of your data
  3. Use proc from p.1 and st_xmin (st_xmax,st_ymin,st_ymax) as ranges to create random point inside bbox of your data
  4. Find nearest edge of your route graph for this point and use it as source (or destination)
  5. write simple procedure to find shortest path and use it in select or update (of course you have to change sum(cost) with st_union(geom) and procedure will be returning geometry)

Hope now it helped :)

  • Well this really does not help me understandt the topic, but at least I know now that my approach is feasible. So basicly i should put the data in one database to be able to query them in one flush.
    – Benjamin
    Aug 19 '14 at 18:29
  • @Benjamin - I added some e.g. workflow - hope it will help you
    – Jendrusk
    Aug 19 '14 at 21:46
  • Can the Bbox be a polygon? or is it always a square?
    – Benjamin
    Aug 22 '14 at 10:29
  • @Benjamin: no, bbox is always rectangle but it's not a problem - if you'll generate point outside your data nearest edge will be somewhere near border of data... Of course there is possibility to use hull instead of bbox and create procedure which will give you point inside this hull but as for begining bbox should be ok
    – Jendrusk
    Aug 22 '14 at 11:38

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