PostGIS vs. MySQL spatial in MapServer

[Klaus Benndorf]

Hi,

as Arnulf wrote, we built myWMS as we needed special carthographic functions 
for our german customers. But as so often, you do much more than intended 
when starting the project. Meanwhile myWMS is a complete map server that 
covers a lot of functionality described by OGC/WMS.

We decided to use MySQL, as many of our customers already worked with it. But 
myWMS does not strictly depend on MySQL. As a Java servlet, using JDBC, you 
should be able to connect to PostGIS as well. Maybe it takes some adjustments 
in SQL statements, but I don't think it is too much.

If you compare PostGIS and MySQL, you see, that MySQL offers not so many 
functions and some of them are only working on the bounding box of an object. 
But it is enough for what we want to do with myWMS.

Looking at the performance, MySQL seems to be excellent. We tested the import 
of about 3 Million objects and MySQL was about 30% faster than PostGIS. Maybe 
thats due to the fact, that MySQL implements spatial objects in MYISAM , not 
in INNODB. MYISAM does not know transactions. So it is very lean and fast.

Also the spatial index in MySQL seems to work excellent. We could not find any 
performance leak, i.e. depending on the number of objects, the size of the 
database, or the number of concurrent accesses.

I'm going to describe more technical background on myWMS website 
(http://www.bt-gis.de/mywms), but it takes a while. Unfortunately we don't 
have an english website yet, but I am working on it. Hope it will be 
available in about 2 weeks. 

Klaus


Am Mittwoch, 1. Juni 2005 14:53 schrieb Arnulf Christl:
> Kralidis,Tom [Burlington] wrote:
> > Hi,
> >
> > Can someone point me to some comparisons of the spatial functionality of
> > these packages?
> >
> > Thanks
> >
> > ..Tom
> >
> > =========================
> > Tom Kralidis
> > Senior Systems Scientist
> > Environment Canada
> > Tel: +01-905-336-4409
> > http://www.ec.gc.ca/
>
> Just to add to the confusion... there is a project called MyWMS which
> has been implemented by Klaus Benndorf (all in German:
> http://www.bt-gis.de) in cooperation with MySQL AB.
> There is a case study (very short, German language):
> http://www.mysql.de/it-resources/case-studies/mysql-bt-gis-anwenderbericht.
>pdf
>
> It states that MyWMS is OGC WMS compliant (it probably should say
> implementing) and uses some MySQL "spatial operations".
> The proprietary forerunner GISeye contains a very powerful rendering
> engine which can handle the cartographic wilderness that German
> cadastral maps boast (Steve added some of this functionality to
> MapServer last year). It will be (was, is) released under the GNU/GPL
> with a dual licensing simliar to that by MySQL AB.
>
> i did not yet have time to dig into it. If you are interested in
> internal details of MySQL spatial functionality probably Klaus will know
> best.

-- 
BT-GIS
Klaus Benndorf
Kessenicher Straße 108
53129 Bonn

Telefon 0049228 978510
Fax     0049228 9785111
Mail    benndorf at mail.bt-gis.de
WWW     http://www.bt-gis.de