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. Jeff
On Tue, Jun 30, 2009 at 1:00 PM, Graham Jones <[email protected] > wrote: > 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! > > Graham > > 2009/6/30 Jeffrey Warren <[email protected]> > > I'd like to, but how does the schema you want differ from the schemas Brett >> Henderson hosts? >> 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? >> >> Jeff >> >> On Tue, Jun 23, 2009 at 4:48 PM, Graham Jones < >> [email protected]> wrote: >> >>> Jeffrey, >>> 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. >>> I think that http://wiki.openstreetmap.org/wiki/Database/Model 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? >>> >>> Regards >>> >>> >>> Graham. >>> >>> 2009/6/22 Jeffrey Warren <[email protected]> >>> >>> 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. >>>> 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: >>>> >>>> >>>> ERROR: insert or update on table "current_way_nodes" violates foreign >>>> key constraint "current_way_nodes_node_id_fkey" >>>> >>>> DETAIL: Key (node_id)=(17704640) is not present in table >>>> "current_nodes". >>>> >>>> >>>> ********** Error ********** >>>> >>>> >>>> ERROR: insert or update on table "current_way_nodes" violates foreign >>>> key constraint "current_way_nodes_node_id_fkey" >>>> >>>> SQL state: 23503 >>>> >>>> Detail: Key (node_id)=(17704640) is not present in table >>>> "current_nodes". >>>> >>>> >>>> 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: >>>> >>>> >>>> ALTER TABLE ONLY current_way_nodes >>>> >>>> ADD CONSTRAINT current_way_nodes_node_id_fkey FOREIGN KEY (node_id) >>>> REFERENCES current_nodes(id); >>>> >>>> >>>> Has anyone else encountered this? Is there a better way to resolve it? >>>> >>>> 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. >>>> >>>> Jeff >>>> >>>> ======== history of a node with leftover current_way_node ========== >>>> >>>> <?xml version="1.0" encoding="UTF-8"?> >>>> <osm version="0.6" generator="OpenStreetMap server"> >>>> <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"> >>>> <tag k="created_by" v="JOSM"/> >>>> </node> >>>> <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"/> >>>> <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"/> >>>> <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"/> >>>> <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"/> >>>> <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"/> >>>> </osm> >>>> >>>> >>>> >>>> On Mon, Jun 22, 2009 at 4:09 PM, 80n <[email protected]> wrote: >>>> >>>>> On Mon, Jun 22, 2009 at 2:29 AM, Brett Henderson <[email protected]>wrote: >>>>> >>>>>> Graham Jones wrote: >>>>>> > Brett, >>>>>> > >>>>>> > I'll have a look at --write-pgsql-dump and add some words about that >>>>>> > too. Then I'll have a go at MySQL... >>>>>> > >>>>>> > The other thing I would like to add is a write-up on choosing a >>>>>> > database to use, because it is not obvious to me which would be the >>>>>> > best, but I suspect this has been looked at a lot before now. I >>>>>> think >>>>>> > the choices are: >>>>>> I'm not aware of a page summarising this, so it sounds very useful. >>>>>> Lots of people ask about it. >>>>>> > >>>>>> > * PostgreSQL/PostGIS - Can handle big datasets, and has >>>>>> geographic >>>>>> > extensions. What I do not know is whether these extensions >>>>>> are >>>>>> > fast or not, or if for simple things like selecting for a >>>>>> > bounding box it would be quicker to just select on lat/lon >>>>>> > directly. Mapnik uses this database, but the schema is >>>>>> > optimised for rendering. >>>>>> > >>>>>> There's actually 3 PostgreSQL schemas which causes much confusion: >>>>>> 1. The API database. This is the database behind the main OSM >>>>>> ruby-based API. This is supported by the osmosis "apidb" tasks. The >>>>>> only db capable of holding full history. >>>>>> 2. The Mapnik database. Used by the mapnik renderer. Loaded using >>>>>> osm2pgsql. Not supported by osmosis. Uses PostGIS extensions. Holds >>>>>> a >>>>>> filtered subset of data appropriate for rendering. >>>>>> 3. The pgsql "simple" schema. Badly named, but this is a schema I >>>>>> created for holding OSM snapshots (ie. doesn't maintain history). It >>>>>> does support replication for keeping it up to date. Uses PostGIS >>>>>> extensions. Used by ROMA servers. >>>>>> >>>>>> All three schemas above can support minutely updates, 1 and 3 by >>>>>> osmosis, 2 by osm2pgsql. >>>>>> > >>>>>> > * >>>>>> > >>>>>> > >>>>>> > * MySQL - Can handle big datasets, but does not have geographic >>>>>> > estensions. >>>>>> > >>>>>> This is the same schema as the PostgreSQL API schema, but implemented >>>>>> in >>>>>> MySQL. Provides identical features, but no longer the preferred >>>>>> database of choice. It's being maintained in the short term, but I >>>>>> suspect it will get dropped over time if PostgreSQL continues to work >>>>>> well. >>>>>> > >>>>>> > * Sqlite - I have seen a discussion about this and I think there >>>>>> > are doubts over how well it works for big datasets? >>>>>> > >>>>>> No idea on this one, but yes I'd be dubious about its use for large >>>>>> databases. Not supported by osmosis. >>>>>> >>>>>> On this note, I've also messed about with the Berkeley DB Java Edition >>>>>> which didn't work well on large datasets. I've long since deleted the >>>>>> osmosis tasks that used it. >>>>>> > >>>>>> > * GT.M - I know nothing about this, but the xapi servers seem to >>>>>> > use it? >>>>>> > >>>>>> I don't know anything about this either. >>>>>> >>>>> >>>>> GT.M is a high performance schemaless datastore. A bit like CouchDB. >>>>> >>>>> Probably not what you are looking for. >>>>> >>>>> 80n >>>>> >>>>> >>>>> >>>>>> > >>>>>> > * >>>>>> > >>>>>> > >>>>>> > I would also like to include some example SQL queries to deal with >>>>>> > selecting nodes / ways etc. in a given bounding box, or searching >>>>>> for >>>>>> > points of interest. These are not too difficult to do, but I >>>>>> suspect >>>>>> > that some ways are more efficient than others, so it would be nice >>>>>> to >>>>>> > include 'optimum' examples. >>>>>> Sounds reasonable. You may wish to check out the osmosis "pgsql" >>>>>> queries in this space, it uses true geo-spatial queries if the >>>>>> optional >>>>>> way "linestring" and "bbox" columns are available. >>>>>> > >>>>>> > Do you know if this has already been done and written up somewhere? >>>>>> > If not I will set up a template and ask for help filling it in! >>>>>> I haven't seen it before (not that I've looked ...). Sounds great to >>>>>> me! >>>>>> >>>>>> A couple of other db mechanisms are available, details are on the >>>>>> wiki. >>>>>> TRAPI - Stores data using a tile-based mechanism. >>>>>> http://wiki.openstreetmap.org/wiki/TRAPI >>>>>> osmbin - A compact custom storage mechanism. Supported by osmosis via >>>>>> a >>>>>> plugin. >>>>>> >>>>>> http://wiki.openstreetmap.org/wiki/Osmosis/DetailedUsage#--write-osmbin-0.6 >>>>>> >>>>>> Brett >>>>>> >>>>>> >>>>>> _______________________________________________ >>>>>> dev mailing list >>>>>> [email protected] >>>>>> http://lists.openstreetmap.org/listinfo/dev >>>>>> >>>>> >>>>> >>>>> _______________________________________________ >>>>> dev mailing list >>>>> [email protected] >>>>> http://lists.openstreetmap.org/listinfo/dev >>>>> >>>>> >>>> >>>> _______________________________________________ >>>> dev mailing list >>>> [email protected] >>>> http://lists.openstreetmap.org/listinfo/dev >>>> >>>> >>> >>> >>> -- >>> Dr. Graham Jones >>> Hartlepool, UK >>> email: [email protected] >>> >> >> > > > -- > Dr. Graham Jones > Hartlepool, UK > email: [email protected] >
_______________________________________________ dev mailing list [email protected] http://lists.openstreetmap.org/listinfo/dev
