[OSM-dev] MySQL GIS extensions - some tipps

[Andreas Kalsch]

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
> dev at openstreetmap.org
> http://lists.openstreetmap.org/listinfo/dev
>
>
>