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.

Answer 1

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`);