[Mapserver-dev] Fwd: Re: MapServer MySQL interface

[David Blasby]

>  As you maybe noticed my
> connector implementation is based on almost pure ansi sql, so with
> minimal changes it can be used practically with ANY sql database. It IS
> slower than postgis, but even with postgis-mysql 4.1 available, this
> solution is an option because it does not require to jump on a
> relatively unstable new mysql, does not require to install postgis if
> you do not already have it and, finally, it does not require additional
> knowledge beside plain sql to use and modify. So basically it is a
> 'sql-lite' connector - compatible with practically everything and easy
> to use, the downside being slower than a regular postgis approach.

We (the developers of PostGIS) havent heard anything from the mysql folks
regarding spatial capabilities.  I've heard from-a-friend-of-a-friend that
they are doing something, but I havent heard of anything solid.  If anyone
knows who's doing it, I'd like to get in contact with them.
The biggest issue they need to solve is indexing. I met with the main
developers a little more than a year ago and talked about adding spatial
capabilities to mySql.  They said it would be very difficult, but they were
interested in adding it.

Second, the OGC SF SQL specification provides an implementation that can be
implemented in any SQL database.  Its called the "relational implementation"
if I remember correctly.  PostGIS implements the "fully integrated" approach.
The SF SQL spec is available at http://www.opengis.org/techno/specs/99-049.pdf

I *HIGHLY* recommend implementing their specification rather than creating
your own.

If you just want to get something working in mySQL, you might want to consider
some of these ideas:

1. store the geometry as either WKT or WKB in a column in mysql.  Have the
mapserver connector perform the WKB (or WKT) to ShapeObj.  The PostGIS
connector using WKB as a transit representation - you could steal the parser
from mappostgis.c.  This allows you store more complex geometries.  See the SF
SQL spec - it gives this as an implementation option.
2. You might want to use a query re-writer stored procedure to do queries. For
example:

    spatial_optimize( table  varchar, columns varchar, xmin number, ymin
number, xmax number, ymax number)

    spatial_optimize ( 'mydata', 'the_geom,id, area', 0,0, 100 ,100)

    ->  SELECT the_geom,id,area FROM mydata
                WHERE mydata.bbox_xmax >= 0 AND
                                mydata.bbox_ymax >=0 AND
                                mydata.bbox_xmin <= 100 AND
                                mydata.bbox_ymin <=100

   That way you can build indexes and query quickly without having the
operator think too much.  It also allows you to change how you're representing
your data without having to change your connector.


But, please please please consider following the OGC SF SQL spec!!

dave