MySQL - selecting near a spatial point

geolocation, geospatial, mysql, php, spatial

Solution

I've corrected the formula a little.

This one works for me:

CREATE TABLE lastcrawl (id INT NOT NULL PRIMARY KEY, pnt POINT NOT NULL) ENGINE=MyISAM;

INSERT
INTO    lastcrawl
VALUES  (1, POINT(40, -100));

SET @lat = 40;
SET @lon = -100;

SELECT  *
FROM    lastcrawl
WHERE   MBRContains
                (
                LineString
                        (
                        Point
                                 (
                                 @lat + 10 / 111.1,
                                 @lon + 10 / ( 111.1 / COS(RADIANS(@lat)))
                                 ),
                        Point    (
                                 @lat - 10 / 111.1,
                                 @lon - 10 / ( 111.1 / COS(RADIANS(@lat)))
                                 )
                        ),
                pnt
                );

Problem

I've based my query below to select points near a spatial point called `point` on the other SO solution, but I've not been able to get it to return any results, for the past few hours, and I'm a bit edgy now... My table `lastcrawl` has a simple schema: ``` id (primary int, autoinc) point (spatial POINT) ``` (Added the spatial key via `ALTER TABLE lastcrawl ADD SPATIAL INDEX(point);`) ``` $query = sprintf("SELECT * FROM lastcrawl WHERE MBRContains(LineFromText(CONCAT( '(' , %F + 0.0005 * ( 111.1 / cos(%F)) , ' ' , %F + 0.0005 / 111.1 , ',' , %F - 0.0005 / ( 111.1 / cos(%F)) , ' ' , %F - 0.0005 / 111.1 , ')' ) ,point)", $lng,$lng,$lat,$lng,$lat,$lat); $result = mysql_query($query) or die (mysql_error()); ``` I've successfully inserted a few rows via: `$query = sprintf("INSERT INTO lastcrawl (point) VALUES( GeomFromText( 'POINT(%F %F)' ))",$lat,$lng);` But, I'm just not able to query the nearest points to get a non-null result set. How do you get the query to select the points nearest to a given spatial point? After inserting `$lat=40, $lng=-100`, trying to query this returns nothing as well (so, not even exact coordinates match): `SELECT * FROM lastcrawl WHERE MBRContains(LineFromText(CONCAT( '(' , -100.000000 + 0.0005 * ( 111.1 / cos(-100.000000)) , ' ' , 40.000000 + 0.0005 / 111.1 , ',' , -100.000000 - 0.0005 / ( 111.1 / cos(40.000000)) , ' ' , 40.000000 - 0.0005 / 111.1 , ')' )) ,point)`

Original source

Related problems