MySQL

[Tim Schaub]

> Can anyone confirm or refute the following: " I should NOT 
> attempt to model any spatial data in MySQL using its spatial 
> constructs if I want to display them in Mapserver."

As you'll hear from others, if you have a choice, PostgreSQL with PostGIS
will give you more than what you'll get from MySQL.

That said, if you have some other reason to use MySQL, you can take
advantage of the spatial data types in MySQL with GDAL/OGR 1.3.2.

The only example on the MapServer site that I know about (that uses MySQL
spatial data types) is in the comments that I added to the bottom of the OGR
page:
http://mapserver.gis.umn.edu/docs/howto/ogr_howto

Basically, use OGR as the CONNECTIONTYPE, use the
"MYSQL:dbname,user=username,password=secret" syntax in your CONNECTION
string, and put your SELECT statement in the DATA string.

Also (as you'll read in the second MySQL comment on the linked page), if you
want MySQL to use the spatial index on your features, you'll need to test
for some geometry relation in your SELECT statement.  Though MapServer is
only rendering a limited extent, the spatial index is not invoked unless you
use something like MBRContains in your SELECT statement.

Good luck,
Tim


PS - in case an example would be useful, here's an ugly one:

LAYER
	NAME "Abundance"
	GROUP "Survey Data"
	TYPE POINT
	CONNECTION "MySQL:dbname,user=username,password=secret"
	CONNECTIONTYPE OGR
	DATA "select the_geom, geog.geog as geog, geog.geogid as geogid,
geog.regionid as regionid, count(distinct TWAsurveys.formid) as surveys,
(avg(fish.abundance) * count(distinct TWAsurveys.formid)/geog.surveys) as
abundance from fish, TWAsurveys, geog where
MBRContains(GeomFromText('Polygon((%minx% %miny%, %minx% %maxy%, %maxx%
%maxy%, %maxx% %miny%, %minx% %miny%))'), the_geom) and fish.region = 'TWA'
and TWAsurveys.formid = fish.formid and geog.regionid = 'TWA' and
geog.geogid = TWAsurveys.geogr and TWAsurveys.type = '1' and %where% group
by TWAsurveys.geogr order by abundance;"
...
END

> 
> Thanks for any assist -- might save several hours of investigation.
> 
> Robert H.
>