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
