Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

up vote 1 down vote accepted

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.

share|improve this answer
    
thanks - tried that with no luck, unfortunately... –  andrewniesen Jul 15 at 20:32
    
actually - yes, that did work! –  andrewniesen Jul 15 at 20:33

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.