Roland, Which MySQL branch have you used? I use a sandboxed 5.1.35 source distribution for testing, which implements _full_ OpenGIS functionality - it works pretty accurate and for me fast enough.
Marcus, I still use no optimization - the queries are fast enough, if your bbox is not too big. Currently, I simply prohibit queries on big bboxes. Ideas: - Split one big table into several ones - in a semantic, not in a geometric way. - Reduce Curves and Surfaces - Use a RAM disk for frequently accessed GIS tables and load indexes into memory - Instead of prohibiting queries on big bboxes you should add a second table which holds simplified features for better performance and query on this. Two sites which compare MySQL / PGSQL - as a whole: http://www.wikivs.com/wiki/MySQL_vs_PostgreSQL - and GIS extensions / PostGIS: http://docs.opengeo.org/geospiel/2009/06/16/postgis-versus-mysql-spatial/ (Attention: This one does not consider the full OpenGIS feature set of the MySQL branch I am testing) I am currently experimenting with a script which extracts political area hierarchies in an accurate way by checking GIS relations. No bugs and pretty accurate. I hope that the full feature set will be included into the main branch as soon as possible, cause the featuritis of Postgres' doesn't attract me ;) But it will probably do in the future. Now I will google for "Cern database Root" ... Andi Roland Olbricht schrieb: >> Does anyone have experience with Mysql and 2d-range-queries? >> >> I am looking for the optimal way of: >> a) selecting all nodes in a given bounding-box and >> b) selecting all ways intersecting a given bounding-box. >> >> I am not bound to any existing schema. >> >> What works best? >> > > I've started OSM3S with mySQL (and myIsam tables), but it had very poor > performance for spatial queries with all the nodes from the planet. > > For mySQL, I used an extra index containing the latitude rounded to an > integer, then the longitude. Thus, a spatial query like the one below was > aligned or nearly aligned with the index. This had the same performance than > a > quadtile index with latitude and longitude interleaved as binary numbers. The > legacy system to which I compare uses a quadtile index. > I don't have exact test results, but for retrieving a bbox like > 51.0<lat<52.0, > 7.0<lon<8.0 the legacy system takes 2 seconds while mySQL took more than two > hours. Similar results have appeared for the ways table. The tests were > performed on a Intel T9500 with 4 GB RAM, operated by Hardy Heron 32-bit. > > Monitoring the process showed that mySQL was busy with the disk all the > time. > So must probably, mySQL organises the data on the disk in a way that is > inappropriate for retrieving large amounts of data. Unfortunately, there is > no > documentation how the data is arranged on the disk and no switch how to let > it > be organised in a more useful way. > > So if you are intending to do spatial queries on a planet file sized amount > of > data, you should probably pass to a more performant system. PostgreSQL does > not document its disk storage strategy either, so the same problem might or > might not appear. Somebody has suggested the Cern database Root, but I have > not tried this one. The source code of the mentioned legacy system is also > available. > > Cheers, > Roland > > > > _______________________________________________ > dev mailing list > [email protected] > http://lists.openstreetmap.org/listinfo/dev > > > _______________________________________________ dev mailing list [email protected] http://lists.openstreetmap.org/listinfo/dev
