MySQL Syntax – Searching Points Within Polygons Using GeomFromText

MySQLspatial

I am trying to select points within polygons using the GIS/spatial extensions for MySQL. I'm fairly new to this, so bear with me if this is an elementary question.

I have points defined as text within the database, and am trying to get a boolean value for whether a point falls within a certain defined polygon. Here are my queries, with a slow WHERE statement to ensure that all should return TRUE (note that these are LAT/LNG values, and the polygon is a square starting in the northwest corner, being defined clockwise):

mysql> SET @zone = GeomFromText('Polygon((-73.9650952400 40.8072101100,-73.9531904800 40.8072101100,-73.9531904800 40.7982011000,-73.9650952400 40.7982011000,-73.9650952400 40.8072101100))');
Query OK, 0 rows affected (0.00 sec)

Checking that this query worked:

mysql> SELECT AsText(@zone);
+---------------------------------------------------------------------------------------------------------------------------------------+
| AsText(@zone)                                                                                                                         |
+---------------------------------------------------------------------------------------------------------------------------------------+
| POLYGON((-73.96509524 40.80721011,-73.95319048 40.80721011,-73.95319048 40.7982011,-73.96509524 40.7982011,-73.96509524 40.80721011)) |
+---------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Then the problematic statement:

mysql> SELECT id,latitude,longitude, MBRWithin(GeomFromText(geo),@zone), AsText(geo)
FROM image_meta WHERE 
(latitude BETWEEN 40.7982011 AND 40.80721011) AND
(longitude BETWEEN -73.96509524 AND -73.95319048)
LIMIT 10;

and the result:

+---------+---------------+----------------+------------------------------------+-----------------------------------+
| id      | latitude      | longitude      | MBRWithin(GeomFromText(geo),@zone) | AsText(geo)                       |
+---------+---------------+----------------+------------------------------------+-----------------------------------+
| 2598514 | 40.7982018590 | -73.9636609250 |                               NULL | POINT(-73.963660925 40.798201859) |
| 2490688 | 40.7982019370 | -73.9624817730 |                               NULL | POINT(-73.962481773 40.798201937) |
| 1325723 | 40.7982019940 | -73.9649998380 |                               NULL | POINT(-73.964999838 40.798201994) |
| 2466868 | 40.7982025000 | -73.9537353330 |                               NULL | POINT(-73.953735333 40.7982025)   |
|  442505 | 40.7982033330 | -73.9584200000 |                               NULL | POINT(-73.95842 40.798203333)     |
| 9517611 | 40.7982033330 | -73.9635783330 |                               NULL | POINT(-73.963578333 40.798203333) |
| 2681112 | 40.7982033580 | -73.9649211860 |                               NULL | POINT(-73.964921186 40.798203358) |
| 2681113 | 40.7982033580 | -73.9649211860 |                               NULL | POINT(-73.964921186 40.798203358) |
|  338950 | 40.7982050000 | -73.9649306570 |                               NULL | POINT(-73.964930657 40.798205)    |
| 1204589 | 40.7982052380 | -73.9649509480 |                               NULL | POINT(-73.964950948 40.798205238) |
+---------+---------------+----------------+------------------------------------+-----------------------------------+
10 rows in set (0.00 sec)

This query was designed based on this documentation: http://dev.mysql.com/doc/refman/5.6/en/spatial-relation-functions-mysql-specific.html#function_mbrcontains

I would expect the MBRWithin(GeomFromText(geo),@zone) column to be TRUE (or 1) for every row, but they are NULL – is my syntax wrong, or are my expectations?

Many thanks in advance.

Best Answer

I may be wrong, but I believe that MBRWithin(GeomFromText(geo),@zone), should be MBRWithin(geo, @zone) -no need to convert anything, it is stored already in spatial format.

When functions like these get bad-type operands, they return null.

Related Question