OpenStreetMap file into a mysql database

 Reference Wikipedia Link is a module (in extension to to transfer data extracted from an osm file into a mysql database.


With the growing size of germany.osm (or osm in general) it is becoming harder by the day to handle all this data. Especially if not enough RAM is present. So I decided to move the data from the osm file (and RAM) to disk. On the one hand this will be convenient because all data is present. On the other hand this will significantly slow down some tasks. Otherwise I always wanted to do something with an sql server (again).


  • Install mysql server
  • change root password (maybe create user/password for osm use)
  • create osmdb.ini in program folder and put in two lines (user=name AND password=abc)
  • create database(s) (tables, indexes etc. will be handled by
  • Install perl DBI module
  • put in module folder (like

Programs used

  • (takes osm file and database name and transfers data to database)
  • (takes osc file and database name and updates data in database)


  • germany.osm (11.4GB) could be transferred to a database in 2.5hrs on a (2GHz, 7200rpm) machine. That is a bulk load on indexed tables (ids, keys, values). Import of germany.osc (342MB, one week of changes) takes 1hr.
  • Hamburg (208MB) on Atom 1.6GHz: bulk load 6.5mins. Apply diff of 3.3MB size (changes of one week) takes 50secs.

Diff files

To keep your data up to date you can either load a new extract or use two osm files and osmosis to create an osc file. This can be used to update your database. If you are using a planet file these osc files are provided on a regular basis.

Data recall

Single objects

dbConnect ($databaseName) ;
my ($refProperties, $refTags) = getDBnode ($nodeId) ;
my %properties = %$refProperties ;
my @tags = @$refTags
dbDisconnect ($databaseName) ;


dbConnect ($databaseName) ;
loopInitNodes(null, null) ; # for all nodes
# loopInitNodes("place", "city") ; # only place=city
my $id = loopGetNextNode() ;
my ($refProperties, $refTags) = getDBNode ($id) ;
while (defined $properties) {
   # next
   $id = loopGetNextNode() ;
   ($refProperties, $refTags) = getDBNode ($id) ;
dbDisconnect ($databaseName) ;

Tags (key/values)

my ($name) = getTag ($type, $objectId, $key) ;
my ($name2) = getTag ("node", 12345, "name") ;
my ($ref) = getTag ("way", 12345, "ref") ;

Coordinates for waynodes

my ($lonRef, $latRef) = getDBWayNodesCoords ($wayId) ; # returns refs to hashes (keys = nodeId)

Data manipulation

deleteDBNode ($id)
deleteDBWay ($id)
deleteDBRelation ($id)
storeDBNode ($propRef, $tagRef)
storeDBWay ($propRef, $nodeRef, $tagRef)
storeDBRelation ($propRef, $memberRef, $tagRef)


The following tables will be created:

  • nodes
  • nodetags
  • ways
  • waytags
  • waynodes
  • relations
  • relationtags
  • relationmembers

The ids, keys and values are indexed (k/v only first 12 characters to save time and space)


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s