Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

mysql query points within polygon - no results

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...
like image 921
ppetree Avatar asked Jun 12 '13 18:06

ppetree


1 Answers

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.

like image 159
eggyal Avatar answered Sep 30 '22 00:09

eggyal