Agreed, it's quite confusing. The page you reference seems hard to maintain since it's basically an image. Perhaps a sub-page for each schema would be nice? At least mentioning the simple-schema, the rails-port schema (the one in the migrations) and the one brett hosts, those three would be quite nice to list with some notes on each and a link to download them. The simple schema comes with Osmosis and excludes history, i think. I have notes on getting the migrations to run so if you stub out the pages I'll put what notes i have in them.<div>
<br></div><div>Jeff<br><br><div class="gmail_quote">On Tue, Jun 30, 2009 at 1:00 PM, Graham Jones <span dir="ltr"><<a href="mailto:grahamjones139@googlemail.com">grahamjones139@googlemail.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex;">
I don't think I want a different one, I was just struggling to understand what the differences are between all these different databases that people use so I can choose one, and thought it would be worth documenting it once I had worked it out!<br>
<br>Graham<br><br><div class="gmail_quote">2009/6/30 Jeffrey Warren <span dir="ltr"><<a href="mailto:warren@mit.edu" target="_blank">warren@mit.edu</a>></span><div><div></div><div class="h5"><br><blockquote class="gmail_quote" style="border-left:1px solid rgb(204, 204, 204);margin:0pt 0pt 0pt 0.8ex;padding-left:1ex">
I'd like to, but how does the schema you want differ from the schemas Brett Henderson hosts? <div><br></div><div>And along those lines, should we remove the foreign key constraints from the schema, Brett, if they're not necessary and they cause Osmosis imports to fail?</div>
<div><br></div><div>Jeff<br><br><div class="gmail_quote">On Tue, Jun 23, 2009 at 4:48 PM, Graham Jones <span dir="ltr"><<a href="mailto:grahamjones139@googlemail.com" target="_blank">grahamjones139@googlemail.com</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="border-left:1px solid rgb(204, 204, 204);margin:0pt 0pt 0pt 0.8ex;padding-left:1ex">Jeffrey,<br>I can't help with your error, I'm afraid, but if you have an up to date description of the database schema I would be happy to add it ot the summary page I am writing.<br>
I think that <a href="http://wiki.openstreetmap.org/wiki/Database/Model" target="_blank">http://wiki.openstreetmap.org/wiki/Database/Model</a> is supposed to be a description of the main API DB schema, but it is labelled as out of date, so maybe this would be a good place for your description to be stored?<br>
<br>Regards<br><br><br>Graham.<br><br><div class="gmail_quote">2009/6/22 Jeffrey Warren <span dir="ltr"><<a href="mailto:warren@mit.edu" target="_blank">warren@mit.edu</a>></span><div><div></div><div><br>
<blockquote class="gmail_quote" style="border-left:1px solid rgb(204, 204, 204);margin:0pt 0pt 0pt 0.8ex;padding-left:1ex">
Hi, I'm compiling a very long description of a Postgres/Rails port schema import, though I'm writing directly to the db. I'd be happy to share/post my notes on the process as I managed to get around a number of undocumented problems. <div>
<br></div><div>I'm still not done right now (i've been pinging the list here occasionally) and my latest problem is that I'm getting several errors like the following:</div><div><p style="margin:0px;font-family:Lucida Grande;font-style:normal;font-variant:normal;font-weight:normal;font-size:13px;line-height:normal;font-size-adjust:none;font-stretch:normal;min-height:16px">
<br></p>
<p style="margin:0px;font-family:Lucida Grande;font-style:normal;font-variant:normal;font-weight:normal;font-size:13px;line-height:normal;font-size-adjust:none;font-stretch:normal">ERROR: insert or update on table "current_way_nodes" violates foreign key constraint "current_way_nodes_node_id_fkey"</p>
<p style="margin:0px;font-family:Lucida Grande;font-style:normal;font-variant:normal;font-weight:normal;font-size:13px;line-height:normal;font-size-adjust:none;font-stretch:normal">DETAIL: Key (node_id)=(17704640) is not present in table "current_nodes".</p>
<p style="margin:0px;font-family:Lucida Grande;font-style:normal;font-variant:normal;font-weight:normal;font-size:13px;line-height:normal;font-size-adjust:none;font-stretch:normal;min-height:16px"><br>
</p>
<p style="margin:0px;font-family:Lucida Grande;font-style:normal;font-variant:normal;font-weight:normal;font-size:13px;line-height:normal;font-size-adjust:none;font-stretch:normal">********** Error **********</p>
<p style="margin:0px;font-family:Lucida Grande;font-style:normal;font-variant:normal;font-weight:normal;font-size:13px;line-height:normal;font-size-adjust:none;font-stretch:normal;min-height:16px"><br>
</p>
<p style="margin:0px;font-family:Lucida Grande;font-style:normal;font-variant:normal;font-weight:normal;font-size:13px;line-height:normal;font-size-adjust:none;font-stretch:normal">ERROR: insert or update on table "current_way_nodes" violates foreign key constraint "current_way_nodes_node_id_fkey"</p>
<p style="margin:0px;font-family:Lucida Grande;font-style:normal;font-variant:normal;font-weight:normal;font-size:13px;line-height:normal;font-size-adjust:none;font-stretch:normal">SQL state: 23503</p>
<p style="margin:0px;font-family:Lucida Grande;font-style:normal;font-variant:normal;font-weight:normal;font-size:13px;line-height:normal;font-size-adjust:none;font-stretch:normal">Detail: Key (node_id)=(17704640) is not present in table "current_nodes".</p>
<p style="margin:0px;font-family:Lucida Grande;font-style:normal;font-variant:normal;font-weight:normal;font-size:13px;line-height:normal;font-size-adjust:none;font-stretch:normal;min-height:16px"><font face="arial"><span style="font-size:small"><br>
</span></font></p><p style="margin:0px;font-family:Lucida Grande;font-style:normal;font-variant:normal;font-weight:normal;font-size:13px;line-height:normal;font-size-adjust:none;font-stretch:normal;min-height:16px">
<font face="arial"><span style="font-size:small">These are current_way_nodes which were not deleted from the current_ table when their matching current_nodes were deleted. I'm now trying to delete them manually since this only occurs where nodes are visible=false, but it's confusing when this causes the foreign key constraint creation to fail:</span></font></p>
<p style="margin:0px;font-family:Lucida Grande;font-style:normal;font-variant:normal;font-weight:normal;font-size:13px;line-height:normal;font-size-adjust:none;font-stretch:normal;min-height:16px"><font face="arial"><span style="font-size:small"><br>
</span></font></p><p style="margin:0px;font-family:Lucida Grande;font-style:normal;font-variant:normal;font-weight:normal;font-size:13px;line-height:normal;font-size-adjust:none;font-stretch:normal;min-height:16px">
<font face="arial"><span style="font-size:small"><span style="font-family:'Lucida Grande';font-size:13px">ALTER TABLE ONLY current_way_nodes</span></span></font></p>
<p style="margin:0px;font-family:Lucida Grande;font-style:normal;font-variant:normal;font-weight:normal;font-size:13px;line-height:normal;font-size-adjust:none;font-stretch:normal;min-height:16px"> ADD CONSTRAINT current_way_nodes_node_id_fkey FOREIGN KEY (node_id) REFERENCES current_nodes(id);</p>
<p style="margin:0px;font-family:Lucida Grande;font-style:normal;font-variant:normal;font-weight:normal;font-size:13px;line-height:normal;font-size-adjust:none;font-stretch:normal;min-height:16px">
<font face="arial"><span style="font-size:small"><br></span></font></p><div>Has anyone else encountered this? Is there a better way to resolve it?</div><div><br></div><div>
I tried following how the rails port actually deletes from current_nodes, but was unable to find it; is this done outside rails code? Can someone point to a line number where this is done so I can try to find out why the dependent current_way_node record wasn't deleted also? Hopefully this will eliminate this problem for future submitted changesets.</div>
<div><br></div><div>Jeff</div><div><br></div><div>======== history of a node with leftover current_way_node ==========</div><div><br></div><div><div><?xml version="1.0" encoding="UTF-8"?></div><div>
<osm version="0.6" generator="OpenStreetMap server"></div><div> <node id="17704640" lat="51.5465346" lon="-2.4116768" changeset="127793" user="southglos" uid="3937" visible="true" timestamp="2006-10-06T11:08:17Z" version="1"></div>
<div> <tag k="created_by" v="JOSM"/></div><div> </node></div><div> <node id="17704640" lat="51.5465346" lon="-2.4116768" changeset="601639" user="Strange but untrue" uid="57932" visible="true" timestamp="2009-04-16T22:28:59Z" version="2"/></div>
<div> <node id="17704640" lat="51.5465346" lon="-2.4116768" changeset="601639" user="Strange but untrue" uid="57932" visible="true" timestamp="2009-04-16T22:31:23Z" version="3"/></div>
<div> <node id="17704640" lat="51.5465346" lon="-2.4116768" changeset="601639" user="Strange but untrue" uid="57932" visible="true" timestamp="2009-04-16T22:32:07Z" version="4"/></div>
<div> <node id="17704640" lat="51.5465346" lon="-2.4116768" changeset="601639" user="Strange but untrue" uid="57932" visible="true" timestamp="2009-04-16T22:32:19Z" version="5"/></div>
<div> <node id="17704640" lat="51.5465346" lon="-2.4116768" changeset="601639" user="Strange but untrue" uid="57932" visible="false" timestamp="2009-04-16T22:32:21Z" version="6"/></div>
<div></osm></div></div><div><div></div><div><div> </div><div><br></div><br><div class="gmail_quote">On Mon, Jun 22, 2009 at 4:09 PM, 80n <span dir="ltr"><<a href="mailto:80n80n@gmail.com" target="_blank">80n80n@gmail.com</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="border-left:1px solid rgb(204, 204, 204);margin:0pt 0pt 0pt 0.8ex;padding-left:1ex">
<div class="gmail_quote"><div><div></div><div>On Mon, Jun 22, 2009 at 2:29 AM, Brett Henderson <span dir="ltr"><<a href="mailto:brett@bretth.com" target="_blank">brett@bretth.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="border-left:1px solid rgb(204, 204, 204);margin:0pt 0pt 0pt 0.8ex;padding-left:1ex">
<div>Graham Jones wrote:<br>
> Brett,<br>
><br>
> I'll have a look at --write-pgsql-dump and add some words about that<br>
> too. Then I'll have a go at MySQL...<br>
><br>
> The other thing I would like to add is a write-up on choosing a<br>
> database to use, because it is not obvious to me which would be the<br>
> best, but I suspect this has been looked at a lot before now. I think<br>
> the choices are:<br>
</div>I'm not aware of a page summarising this, so it sounds very useful.<br>
Lots of people ask about it.<br>
><br>
> * PostgreSQL/PostGIS - Can handle big datasets, and has geographic<br>
<div>> extensions. What I do not know is whether these extensions are<br>
> fast or not, or if for simple things like selecting for a<br>
> bounding box it would be quicker to just select on lat/lon<br>
> directly. Mapnik uses this database, but the schema is<br>
> optimised for rendering.<br>
><br>
</div>There's actually 3 PostgreSQL schemas which causes much confusion:<br>
1. The API database. This is the database behind the main OSM<br>
ruby-based API. This is supported by the osmosis "apidb" tasks. The<br>
only db capable of holding full history.<br>
2. The Mapnik database. Used by the mapnik renderer. Loaded using<br>
osm2pgsql. Not supported by osmosis. Uses PostGIS extensions. Holds a<br>
filtered subset of data appropriate for rendering.<br>
3. The pgsql "simple" schema. Badly named, but this is a schema I<br>
created for holding OSM snapshots (ie. doesn't maintain history). It<br>
does support replication for keeping it up to date. Uses PostGIS<br>
extensions. Used by ROMA servers.<br>
<br>
All three schemas above can support minutely updates, 1 and 3 by<br>
osmosis, 2 by osm2pgsql.<br>
><br>
> *<br>
><br>
><br>
> * MySQL - Can handle big datasets, but does not have geographic<br>
> estensions.<br>
><br>
This is the same schema as the PostgreSQL API schema, but implemented in<br>
MySQL. Provides identical features, but no longer the preferred<br>
database of choice. It's being maintained in the short term, but I<br>
suspect it will get dropped over time if PostgreSQL continues to work well.<br>
><br>
> * Sqlite - I have seen a discussion about this and I think there<br>
<div>> are doubts over how well it works for big datasets?<br>
><br>
</div>No idea on this one, but yes I'd be dubious about its use for large<br>
databases. Not supported by osmosis.<br>
<br>
On this note, I've also messed about with the Berkeley DB Java Edition<br>
which didn't work well on large datasets. I've long since deleted the<br>
osmosis tasks that used it.<br>
><br>
> * GT.M - I know nothing about this, but the xapi servers seem to<br>
> use it?<br>
><br>
I don't know anything about this either.<br>
<div></div></blockquote></div></div><div><br>GT.M is a high performance schemaless datastore. A bit like CouchDB.<br><br>Probably not what you are looking for.<br><font color="#888888"><br>80n<br><br> </font></div><div>
<blockquote class="gmail_quote" style="border-left:1px solid rgb(204, 204, 204);margin:0pt 0pt 0pt 0.8ex;padding-left:1ex">
<div>><br>
> *<br>
><br>
><br>
> I would also like to include some example SQL queries to deal with<br>
> selecting nodes / ways etc. in a given bounding box, or searching for<br>
> points of interest. These are not too difficult to do, but I suspect<br>
> that some ways are more efficient than others, so it would be nice to<br>
> include 'optimum' examples.<br>
</div>Sounds reasonable. You may wish to check out the osmosis "pgsql"<br>
queries in this space, it uses true geo-spatial queries if the optional<br>
way "linestring" and "bbox" columns are available.<br>
<div>><br>
> Do you know if this has already been done and written up somewhere?<br>
> If not I will set up a template and ask for help filling it in!<br>
</div>I haven't seen it before (not that I've looked ...). Sounds great to me!<br>
<br>
A couple of other db mechanisms are available, details are on the wiki.<br>
TRAPI - Stores data using a tile-based mechanism.<br>
<a href="http://wiki.openstreetmap.org/wiki/TRAPI" target="_blank">http://wiki.openstreetmap.org/wiki/TRAPI</a><br>
osmbin - A compact custom storage mechanism. Supported by osmosis via a<br>
plugin.<br>
<a href="http://wiki.openstreetmap.org/wiki/Osmosis/DetailedUsage#--write-osmbin-0.6" target="_blank">http://wiki.openstreetmap.org/wiki/Osmosis/DetailedUsage#--write-osmbin-0.6</a><br>
<div><div></div><div><br>
Brett<br>
<br>
<br>
_______________________________________________<br>
dev mailing list<br>
<a href="mailto:dev@openstreetmap.org" target="_blank">dev@openstreetmap.org</a><br>
<a href="http://lists.openstreetmap.org/listinfo/dev" target="_blank">http://lists.openstreetmap.org/listinfo/dev</a><br>
</div></div></blockquote></div></div><br>
<br>_______________________________________________<br>
dev mailing list<br>
<a href="mailto:dev@openstreetmap.org" target="_blank">dev@openstreetmap.org</a><br>
<a href="http://lists.openstreetmap.org/listinfo/dev" target="_blank">http://lists.openstreetmap.org/listinfo/dev</a><br>
<br></blockquote></div><br></div></div></div>
<br>_______________________________________________<br>
dev mailing list<br>
<a href="mailto:dev@openstreetmap.org" target="_blank">dev@openstreetmap.org</a><br>
<a href="http://lists.openstreetmap.org/listinfo/dev" target="_blank">http://lists.openstreetmap.org/listinfo/dev</a><br>
<br></blockquote></div></div></div><br><br clear="all"><div><div></div><div><br>-- <br>Dr. Graham Jones<br>Hartlepool, UK<br>email: <a href="mailto:grahamjones139@gmail.com" target="_blank">grahamjones139@gmail.com</a><br>
</div></div></blockquote></div><br></div>
</blockquote></div></div></div><div><div></div><div class="h5"><br><br clear="all"><br>-- <br>Dr. Graham Jones<br>Hartlepool, UK<br>email: <a href="mailto:grahamjones139@gmail.com" target="_blank">grahamjones139@gmail.com</a><br>
</div></div></blockquote></div><br></div>