What is the syntax for querying a spatial column?

Question

What is the right syntax to get all the points within the bounding box? The table consists of a lat and lng column and a spatial column "pt" generated from the lat/lng values.

calculated the topleft and bottomright coords for the MBR

tested with the following query ( without the haversine )

SELECT * 
FROM tb_gps 
WHERE (lng BETWEEN 4.88263070241 AND 4.90030729759) 
  AND (lat BETWEEN 52.3677890788 and 52.3785809212);

testing the spatial column generates an error

SELECT * 
FROM tb_gps 
WHERE MBRWITHIN(pt, MULTIPOINT(52.3641917981 4.87673850395, 52.3821782019 4.90619949605))

This will give null results

SELECT * FROM tb_gps as t WHERE MBRCONTAINS( GeomFromText( 'MULTIPOINT(52.3641917981 4.87673850395, 52.3821782019 4.90619949605)' ) , t.pt )=1;

The mysql version is

protocol_version    10
version     5.1.68-cll

It could be that I am using the wrong syntax, am not sure. Could someone help me out with this

( the coords in the second query are different, but that's not really the point)

edit

I had success with this query(at least I got some results back, so I am going to work on that a little more)

SELECT  *
    FROM    tb_gps
    WHERE   MBRContains
                    (
                    LineString
                            (
                            Point
                                    (
                                    4.8914691 + 10 / ( 111.1 / COS(RADIANS( 52.373185))),
                                     52.373185 + 10 / 111.1
                                    ), 
                            Point
                                    (
                                    4.8914691 - 10 / ( 111.1 / COS(RADIANS(52.373185))),
                                     52.373185 - 10 / 111.1
                                    ) 
                            ),
                    pt
                    )

Answer 1

I think you want MBRContains

I have an example from a previous post : Less restrictive query return less result due to simple removing one additional constraint

Give it a Try !!!

UPDATE 2013-05-08 17:05 EDT

You have this:

SELECT * 
FROM tb_gps 
WHERE MBRWITHIN(pt, MULTIPOINT(52.3641917981 4.87673850395, 52.3821782019 4.90619949605))

Put the MULTIPOINT in quotes

SELECT * 
FROM tb_gps 
WHERE MBRWITHIN(pt, 'MULTIPOINT(52.3641917981 4.87673850395, 52.3821782019 4.90619949605)')

I did this in my example

UPDATE 2013-05-08 17:39 EDT

If MULTIPOINT is not working for you, try using POLYGON

SELECT * 
FROM tb_gps 
WHERE
    IF(CONTAINS(GeoFromText('POLYGON(52.3641917981 4.87673850395,
    4.87673850395 4.90619949605,4.90619949605 52.3821782019,
    52.3821782019 52.3641917981)'pt),1,0)
;

You know something? It just dawned on me. You have the parameters backwards.

You have

SELECT * 
FROM tb_gps 
WHERE MBRWITHIN(pt,'MULTIPOINT(52.3641917981 4.87673850395, 52.3821782019 4.90619949605)')

Try reversing the parameters:

SELECT * 
FROM tb_gps 
WHERE MBRWITHIN('MULTIPOINT(52.3641917981 4.87673850395, 52.3821782019 4.90619949605)',pt)

Give it a Try !!!