title: How to build a 'roads' graph in a database from an .osm file
body: <p><strong>Required result:</strong></p>
  
  <p>A database with two tables, 'edges' and 'nodes', which can be used to generate a graph which represents all drivable roads (highways) in a certain area (e.g. city). The dataset is parsed from an .osm file.</p>
  
  <p><strong>Background:</strong></p>
  
  <p>I'm going for my second try of creating a database with two tables - nodes and edges which can be used to do different shortest path calculations on it.</p>
  
  <p>First I tried to manually extract different data into the database (php scripts):</p>
  
  <ul>
  <li>parse all the nodes from the .osm file into 'nodes' table</li>
  <li>parse all the edges into the 'edges' table (that is parse the ways and generate edges (consisting of a starting node and a destination node) from those, adding the tags to the edges simultaneously;</li>
  <li>delete all the edges that have blacklisted tags (that are not highways) from the edges table;</li>
  <li>delete all nodes that are not present in the edges table from the nodes table.</li>
  </ul>
  
  <p>The problem is that this sequence is very unreliable and it works 'so-so' only on small datasets, like thousands of nodes, but not millions.</p>
  
  <p><strong>Problem:</strong></p>
  
  <p>With a database consisting of millions of nodes and edges, road-extracting (i.g. generating edges based on a certain tags filter) is extremely slow.</p>
  
  <p><strong>Possible solution:</strong></p>
  
  <p>What I'm going to do this time:</p>
  
  <ul>
  <li>set up PostgreSQL rdbms;</li>
  <li>parse the .osm file with OSM ready-made scripts to get full nodes, ways and relations tables;</li>
  <li>extract all edges with certain tags (e.g. 'highway') into a new 'edges' table from the ways table;</li>
  <li>extract all nodes that are present in the edges table into a new graph_nodes table;</li>
  <li>after that I probably could generate a 'roads' graph from 'edges' and 'graph_nodes' tables.</li>
  </ul>
  
  <p><strong>Question:</strong></p>
  
  <p>How do I correctly parse an .osm file into a database to have 'edges' and 'nodes' tables as a result? </p>
  
  <p>How can I extract only drivable roads from .osm into a database? </p>
  
  <p>How do I do it fast (I mean hours of scripts running for millions of nodes and edges tables, not weeks)?</p>
  
  <p>Should I take relations into account? </p>
  
  <p>If yes, how should I do that?</p>