Perform a subquery on a spatial table in MySQL

Question

I have a spatial table of polygons. I am trying to find what polygon a lat/lng point lies within in a MySQL spatial table efficiently.

I know ST_Within is available for MySQL 5.6 however my version pre-dates this so I am combining two functions I have at my disposal into something more efficient/accurate than either individually.

These are the native MBRWITHIN function and custom GISWithin from a MySQL forum post. MBRWITHIN usually gives me about 2 rows for a point where there should only be one but is quick, GISWithin gives the correct row, however is quite slow.

So I am trying to write a query that :

Initially selects the few rows that could contain the point using the quick MBRWITHIN funciton.

Then refine this list to the single correct row using the slower GISWithin that is now operating on about 2 rows rather than 9000.

I have tried all sorts of subqueries like

SET @x = -0.3578;
SET @y = 51.477;
SET @point = CONCAT('POINT(',@x,' ',@y,')');


SELECT * FROM 
(SELECT `geometry` FROM world_borders WHERE MBRWITHIN( @point , `geometry` )) AS TT 
WHERE GISWithin(GeomFromText(@point), `geometry`);

and

SELECT * FROM world_borders WHERE GISWithin(GeomFromText(@point), `geometry`) IN (SELECT ogr_fid FROM world_borders WHERE MBRWITHIN( @point , `geometry` ));

only to get the error message

#1416 - Cannot get geometry object from data you send to the GEOMETRY field

Does anyone know how I might write such a query or function?

The polygon field in my table is called geometry and is of type Geometry - imported from ogr2ogr for those that know about that.

http://stackoverflow.com/questions/5875327/spatial-index-in-mysql-error-cannot-get-geometry-object-from-data-you-send-t — Mihai, Nov 29 '13 at 19:53
@Mihai I followed that post and checked for nulls, every row matched. then updated where null X or Y with UPDATE locations SET coordinates = POINT(0,0) WHERE X(coordinates) IS NULL OR Y(coordinates) IS NULL; query was successful, then re ran query SELECT id FROM locations WHERE X(coordinates) IS NULL OR Y(coordinates) IS NULL; to re check, which then returned no rows. Same error still remains however — Gga, Nov 29 '13 at 20:04
@Mihai changing these nulls as per that post also stops the actual point finding functions working — Gga, Nov 29 '13 at 20:08
That was a shot in the dark,you have to ask somebody more knowledgeable than me in spatial dbs. — Mihai, Nov 29 '13 at 20:20

score 1 · Accepted Answer · answered Nov 30 '13 at 05:27

The error comes from trying to use a WKT (text) representation of a point in a place where you need a geometry object, because @point contains a text string and you aren't consistently using GeomFromText() to convert it.

But you don't need to concatenate a string and convert it to a geometry at all, since MySQL has a built-in POINT() function to generate a geometry object directly.

SET @x = -0.3578;
SET @y = 51.477;
SET @point = POINT(@x,@y);

With @point now containing a proper geometry object, the query should be doable by simply combining both tests in a single query with AND.

Since both conditions have to be true, the optimizer should attempt to do the easiest thing first -- realizing that MBRWithin() can be resolved using the spatial index, it should try to find matching rows from the index and then execute the GISWithin() function only on the rows it finds, which would further eliminate any rows from the result-set that don't evaluate to true with the stored function.

SELECT * 
  FROM world_borders 
 WHERE MBRWithin(@point,`geometry`)
   AND GISWithin(@point,`geometry`);

That was it thanks, I had realised my error shortly after but have only just revisited this Q. — Gga, Dec 03 '13 at 18:02

Perform a subquery on a spatial table in MySQL

1 Answers1