[Mapserver-users] MySQL Version 4.1.0-alpha

[Paul Ramsey]

Bart van den Eijnden wrote:
> 
> Do you know the main differences between MySQL 4.1 and MySQL 5.0? And 
> what does version 4.1 miss to be a real (spatial) database, which things 
> are not possible yet?

Some things to note about MySQL versioning:
4.0 is the shipping version, the version they consider "done".
4.1 is the current beta, which you can download and play with.
5.0 is their "gleam in the eye", essentially the development trunk. Not 
so much a version as the notion of a version. You can pull the source 
from their BitKeeper tree and try and compile it.

MySQL has thus far announced no further enhancements to their GIS 
capability for the 5.0 release. However, the 5.0 concept is still pretty 
young, and they may still tack functionality into that release in the 
future. The most important announced "upcoming" feature in 5.0 is stored 
procedures.

The 4.1 release includes their first attempt at spatial support. They 
have generally followed the OpenGIS specification, except where their 
architecture does not allow it (because MySQL does not allow function 
overloading, they had to rename Length() to GLength(), length was 
already used as a function name on another type).

What 4.1 *has* is:
- Spatial objects (point, linestring, polygon, etc)
- Indexing on those objects (rtree implementation, we haven't read into 
it to find out if it is the linear or quadratic implementation)
- Input/output functions (GeomFromText, GeomFromBinary, AsText, AsBinary)
- Basic analytic functions (GLength(), Area()).

What 4.1 *lacks* is:
- Support for triggers and procedural languages.
- SRID support, and support for coordinate reprojection.
- OpenGIS metadata tables.
- A data loader/dumper tool.
- Predicates (Relate(), Contains(), Touches(), Overlaps(), etc etc)
- Operators (Intersection(), Union(), Buffer(), etc)

The documentation is pretty complete and available at 
http://www.mysql.com/doc/en/GIS_spatial_extensions_in_MySQL.html. 
Remember to read the fine print. The predicates and operators are listed 
in the documentation, with a note indicating that they are not 
*actually* implemented. We checked the source, and the documentation is 
correct, there are just MBR stubs.

The lack of even a Distance() function makes MySQL 4.1 pretty useless 
for anything which analyzes the relationships between spatial layers. So 
spatial joins are largely "out". Most of the things which Dave and Chris 
demonstrated at the MUM would not be possible with MySQL.

The devil is in the details. When we start working more intently on the 
MySQL spatial engine, we will have a better idea of its strengths and 
limitations, thus far we have just done a quick source code review to 
see what was implemented and not. For example, if the spatial objects 
are only available in the default MySQL table types and not in InnoDB, 
then MySQL spatial will be alot less useful in production settings, 
since the default tables do not support transactions.

I hope that helps,

Paul

PS - If you want to here the complete tail of my misgivings about MySQL, 
you are required to buy me a beer.

> Thanks,
> Bart
> 
> On Wed, 11 Jun 2003 16:29:20 -0700, David Blasby 
> <dblasby at refractions.net> wrote:
> 
>> Jim Butcher wrote:
>>
>>> First my apologies if this question has been beaten to death, but.....
>>> With the future release of MySQL version 4.1 and its Spatial Extension.
>>> Are there plans to include MySQL support in future releases of
>>> MapServer?
>>
>>
>>
>> Yes.
>>
>> We at Refractions Research are going to be writing a MySQL-mapserver 
>> adapter.  This will be based on the current PostGIS connector.
>>
>> Currently we are adding the complex spatial operators and functions to 
>> PostGIS - things like relate(), contains(), intersect(), buffer(), 
>> convexhull(), etc...  Once this is done we want to use the same 
>> technology (cf. http://geos.refractions.net and 
>> http://postgis.refractions.net) to extend the very simple spatial 
>> support in mySQL into something extremely useful.
>>
>> This is actually an outcome of the Mapserver Users Meeting - I got 
>> tired of people asking me if we were going to be supporting mySQL.  
>> Now that mySQL 4.1.0 allows support for sub-selects, it looks like a 
>> connector is possible.  With the features promised in 5.0, it looks 
>> like mySQL will be a "real" database!
>>
>> Who is actually using the mySQL spatial support?
>>
>> dave
>>
>> _______________________________________________
>> Mapserver-users mailing list
>> Mapserver-users at lists.gis.umn.edu
>> http://lists.gis.umn.edu/mailman/listinfo/mapserver-users
>>
>>
> 
> 
> 


-- 
       __
      /
      | Paul Ramsey
      | Refractions Research
      | Email: pramsey at refractions.net
      | Phone: (250) 885-0632
      \_