Polygon search fails... and fails... and fails...
In my table I have three geospatial fields:
`lat` decimal(12,7) NOT NULL,
`lon` decimal(12,7) NOT NULL,
`location` point NOT NULL,
And three geospatial indices:
KEY `lat` (`lat`),
KEY `lon` (`lon`),
SPATIAL KEY `location` (`location`)
I then have a trigger defined as 'on before update' which sets the `location` from the lat/lon pair like this:
FOR EACH ROW SET NEW.location = PointFromText(CONCAT('POINT(',NEW.lat,' ',NEW.lon,')'))
Here's my query:
SET @bbox = 'POLYGON((32.70 -117.16, 47.97 -122.19, 44.80 -68.77, 25.77 -80.19 ))';
SELECT * , AsText( location )
FROM geo_table
WHERE Intersects( location, GeomFromText( @bbox ) );
Running this query through phpMyAdmin I get the following message for both SQL statements:
# MySQL returned an empty result set (i.e. zero rows).
The data points shown in the above SET @bbox are from 4 corners of the US (entered clockwise) and should return 99% of all points but returns nothing. Here are the coordinates:
San Diego, CA: +32.70 -117.16
Seatle, WA: 47.97 -122.19
Bangor, Maine: 44.80 -68.77
Miami, FL: 25.77 -80.19
I have also tried this against over 3500 polygons from the NWS CAP 1.1 feed and I get nothing! I figure I must be doing something wrong but, for the life of me, I can't figure out what it is!
Any suggestions?