12

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...
ppetree
  • 826
  • 3
  • 15
  • 31

1 Answers1

11

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.

eggyal
  • 122,705
  • 18
  • 212
  • 237
  • Nice catch... I'm going back through and testing some others that I have that should produce more results. – ppetree Jun 12 '13 at 20:19
  • Closing the polygon in the original example worked but when I run the query on the production server I get nothing... even when there are 1000's of possible results. – ppetree Jun 14 '13 at 21:41
  • this is not accurate, it's like checking against a rectangular, not a polygon – Developerium Jul 09 '14 at 08:59
  • 3
    @tinybyte: You are correct that [`Contains()`](http://dev.mysql.com/doc/en/spatial-relation-functions-mbr.html#function_contains) tests against the MBR (as of v5.6.1, one can use [`ST_Contains()`](http://dev.mysql.com/doc/en/spatial-relation-functions-object-shapes.html#function_st-contains) to test against the exact polygon), but that wasn't the question being asked: I stand by the fact that this post correctly and fully answers the problem posed. – eggyal Jul 10 '14 at 09:11