Finding regions that include a point with MySQL

Question

I'm using MySQL GIS and Spatial Extensions.

I've a table with "circular" regions described by latitude, longitude and radius.

I need a query to get all the rows whose region include a given point defined by a latitude and a longitude. The "circular" regions can intersect and therefore the point can fall in more than one region.

Answer 1

I came up with a solution and I would like to post it here just to have an opinion about it. Notice that the table structure above was slightly modified by adding a new column "location POINT" that is built using the latitude and the longitude at each insert.

CREATE PROCEDURE GetRequestsAroundLocation( IN lat DOUBLE, 
                                            IN lon DOUBLE )
BEGIN
  SET @answerLocation = GeomFromText( CONCAT( 'POINT( ', lat, ' ', lon, ' )' ) );

  SELECT id, token, latitude, longitude FROM Request 
  WHERE answerId = -1 AND 
        Intersects( @answerLocation, 
                    GeomFromText( CONCAT( 'POLYGON((', latitude - radius, ' ', longitude - radius, ',', 
                                                       latitude + radius, ' ', longitude - radius, ',', 
                                                       latitude + radius, ' ', longitude + radius, ',', 
                                                       latitude - radius, ' ', longitude + radius, ',', 
                                                       latitude - radius, ' ', longitude - radius, '))' ) ) ) AND 
        SQRT( POW( ABS( lat - latitude ), 2 ) + POW( ABS( lon - longitude ), 2 ) ) < radius;  
END //