[OSM-dev] 0.6 database table creation script for mysql?

Brett Henderson brett at bretth.com
Wed Dec 10 22:04:34 GMT 2008

Andy Allan wrote:
> On Tue, Dec 9, 2008 at 10:41 PM, Maarten Deen <mdeen at xs4all.nl> wrote:
>> I'm not? Than how do I create the database so that I can store OSM data with
>> osmosis?
>> I mean: have it however you want, but if people can't make the database, then
>> how are they going to use it?
> The concept of "the database" is the flaw here. The only place that
> needs to be the exact same format as the rails_port (i.e. the main
> server) is development work related to the main server. And in that
> case, rails is quite important too.
> As for anything else, the schema you need is related to the purpose
> you need it for. So there's a db schema for osm2pgsql (rendering), a
> different one for each routing program, a different one for almost any
> purpose. Even osmosis doesn't have the same db schema for 0.6 postgres
> as the rails' migrations output when run with the postgres db adapter.
> But it doesn't need to. And there doesn't even need to be an sql
> schema for the main db, it could all be hidden behind the API in one
> giant flat file[1] and nobody would need to know.
> So remember, OSM data is an xml format, and there are many db schemas
> available depending on the purpose that data is being used for.
Just to add to that (or summarise, or reword :-), the main 
representations of osm data I'm currently aware of are:
* The production MySQL db schema.  Used by the rails_port.  Contains 
full OSM data representation.  Supported by osmosis.
* The XML format.  Used by all systems currently exchanging data 
including the main API.  Supported by most apps dealing with OSM data 
including osmosis.
* The mapnik postgresql db schema.  Obviously used by the mapnik 
renderer.  Only contains data applicable to rendering, it is a one way 
conversion into this db.  Supported by the osm2pgsql tool.
* The OSMXAPI schema.  Backend to the OSMXAPI ... err API.  Contains all 
data with perhaps the exception of the created_by tag ...  Uses custom 
load scripts.
* The Osmosis pgsql schema.  A generic schema containing a full OSM 
snapshot representation.  Used as a backend for systems such as ROMA.  
Supported by osmosis.
* Binary OSM data.  Lots of discussions underway, but I don't believe 
final implementations are ready for consumption yet.

The rails schema is a reasonable place to start, but it contains a lot 
of tables that aren't relevant to most purposes including history tables 
which at least double the size of the db.  The osmosis pgsql schema was 
intended to be a simpler base on which to develop applications but is 
not optimised for any particular purpose.  The XML format is fine if you 
don't need random access to data.  Depending on the tool to be built, it 
often makes sense to build a new schema specifically targeted at the 
requirements of the application but if one of the above list meets your 
needs then by all means use it.


More information about the dev mailing list