[OSM-dev] 0.6 database table creation script for mysql?
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
>> 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 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
* 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
* 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