Selecting nearest geo coordinates using spatial index on mysql table not working

Question

I have a mysql MyISAM table with many rows that contain information about places, such as geo coordinates and placenames.

I want to select the nearest place from this table depending given an input latitude and longitude. I want to do this creating a column with points of the geocoordinates, using a spatial index and MBRContains to select the data.

I get an error when selecting the data. I will write down the steps that I did here:

Step 1

ALTER TABLE mytable ADD mypoint Point;

UPDATE  mytable
SET     mypoint = Point(lat, lon);

ALTER TABLE mytable MODIFY mypoint NOT NULL;

CREATE SPATIAL INDEX sx_mytable_coords ON mytable(mypoint );

Result: I see this in the mypoint column (I'm not sure if that's oke, but it's the same for all records): [GEOMETRY - 25Bytes] I see that the index was added: Name: mypoint Type: SPATIAL Field: mypoint (32) Collation: A

Step 2

I tried to select the data in two ways:

This is recommended for version lower than MySQL 5.1 (My case)

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


SELECT  *
FROM    mytable
WHERE   MBRContains(LineFromText(CONCAT(
        '('
        , @lon + 10 / ( 111.1 / cos(RADIANS(@lon)))
        , ' '
        , @lat + 10 / 111.1
        , ','
        , @lon - 10 / ( 111.1 / cos(RADIANS(@lat)))
        , ' '
        , @lat - 10 / 111.1 
        , ')' )
        ,mypoint)

I get this error:

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIMIT 0, 30' at line 15

This is recommended for version higher than MySQL 5.1

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

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

I get this error:

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Point ( ' at line 12

See the [Editing FAQ](http://stackoverflow.com/editing-help#code) on information about how to format a post with Markdown. — Madara's Ghost, May 04 '12 at 08:41
Also, wouldn't you be better of in performing these calculations on PHP side, rather than the query itself? — Madara's Ghost, May 04 '12 at 08:42
@Truth I'm doing the calculations on PHP side right now, but it's pretty slow. I found that this should be the fastest way for a Mysql table. — BastiaanWW, May 04 '12 at 08:45
Pretty slow? How fast do you need it I can't imagine even having 0.5 seconds of calculations is THAT bad. Or maybe your calculation algorithm needs rethinking. Please add the PHP code you're currently using. — Madara's Ghost, May 04 '12 at 08:48
@Truth See an earlier post of mine: http://stackoverflow.com/questions/10432222/what-is-the-fastest-way-to-select-nearest-geographical-place-from-mysql-database — BastiaanWW, May 04 '12 at 08:56
I actually need it really fast since this calculation has to be performed many times for a single request. — BastiaanWW, May 04 '12 at 08:59

score 2 · Accepted Answer · answered May 04 '12 at 09:11

You have a missing bracket, you're not closing your LineFromText.

Try:

SELECT  *
FROM    mytable
WHERE   MBRContains(LineFromText(CONCAT(
    '('
    , @lon + 10 / ( 111.1 / cos(RADIANS(@lon)))
    , ' '
    , @lat + 10 / 111.1
    , ','
    , @lon - 10 / ( 111.1 / cos(RADIANS(@lat)))
    , ' '
    , @lat - 10 / 111.1 
    , ')' ))
    ,mypoint)

score 0 · Answer 2 · answered May 04 '12 at 09:05

0

You're missing a ).
You're missing a , between the two Point function calls.

answered May 04 '12 at 09:05

eggyal

122,705
18
212
237

score 0 · Answer 3 · answered May 04 '12 at 09:05

isn't needed , separator between point in LineString? Something like

LineString(1 1,2 2,3 3)

OR

LineString
(
   Point
   (
        @lon + 10 / ( 111.1 / COS(RADIANS(@lat))),
        @lat + 10 / 111.1
   ) 
   ,
   Point
   (
        @lon - 10 / ( 111.1 / COS(RADIANS(@lat))),
        @lat - 10 / 111.1
   ) 
)

Selecting nearest geo coordinates using spatial index on mysql table not working

Step 1

Step 2

I get this error:

I get this error:

3 Answers3

Linked