mysql 查询polygon,多边形内的mysql查询点-没有结果

I'm pretty sure that I'm doing multiple things wrong here but I'm not sure what...

The table (minus a few fields):

CREATE TABLE IF NOT EXISTS `stuff` (

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,

`lat` decimal(12,7) NOT NULL,

`lon` decimal(12,7) NOT NULL,

`location` point NOT NULL,

UNIQUE KEY `id` (`id`),

KEY `distance` (`distance`),

KEY `lat` (`lat`),

KEY `lon` (`lon`),

SPATIAL KEY `location` (`location`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=5;

The triggers to automatically set the location point:

DROP TRIGGER IF EXISTS `stuff_insert_defaults`;

DELIMITER //

CREATE TRIGGER `stuff_insert_defaults` BEFORE INSERT ON `stuff`

FOR EACH ROW SET NEW.location = PointFromText(CONCAT('POINT(',NEW.lat,' ',NEW.lon,')'))

//

DELIMITER ;

DROP TRIGGER IF EXISTS `stuff_update_location`;

DELIMITER //

CREATE TRIGGER `stuff_update_location` BEFORE UPDATE ON `stuff`

FOR EACH ROW SET NEW.location = PointFromText(CONCAT('POINT(',NEW.lat,' ',NEW.lon,')'))

//

DELIMITER ;

Some sample data (4 random points within the state of Colorado):

/* 4 random points within the state of colorado */

INSERT INTO `stuff` (`id`, `distance`, `lat`, `lon`, `location`) VALUES

(1, 5.0000000, 40.2488450, -103.8003460, 0x000000000101000000f6622827da1f444001df6dde38f359c0),

(2, 5.0000000, 38.4849180, -107.8726700, 0x000000000101000000f19e03cb113e4340d28c45d3d9f75ac0),

(3, 5.0000000, 39.5040250, -105.3584800, 0x000000000101000000e6ae25e483c0434049111956f1565ac0),

(4, 5.0000000, 39.1904180, -106.8179680, 0x000000000101000000ed48f59d5f9843402b4b749659b45ac0);

The rough outter boundary of the state of Colorado:

NW corner: 41.000497 -109.050149

NE corner: 41.002380 -102.051881

SE corner: 36.993237 -102.041959

SW corner: 36.999037 -109.045220

The query which SHOULD return the 4 records we inserted:

SELECT *, AsText(location) FROM stuff

WHERE Contains(

GeomFromText('POLYGON((41.000497 -109.050149, 41.002380 -102.051881, 36.993237 -102.041959, 36.999037 -109.045220, 41.000497 -109.050149))'), location );

What I'm getting back...

nada...

zip...

nil...

nothing...

解决方案

As documented under Class Polygon (emphasis added):

Polygon Assertions

The boundary of a Polygon consists of a set of LinearRing objects (that is, LineString objects that are both simple and closed) that make up its exterior and interior boundaries.

You must therefore close the polygon by finishing at the starting point:

SELECT *, AsText(location) FROM stuff

WHERE Contains(

GeomFromText('POLYGON((41.000497 -109.050149, 41.002380 -102.051881, 36.993237 -102.041959, 36.999037 -109.045220, 41.000497 -109.050149))'), location );

See it on sqlfiddle.