2

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
                    )
Richard
  • 121
  • 1
  • 5

1 Answers1

2

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 !!!

Community
  • 1
RolandoMySQLDBA
  • 182,700
  • 33
  • 317
  • 520
  • the new query with MBRCONTAINS did not work, maybe it has to do with how I use MULTIPOINT? I have added the coords for the bounding box to MULTIPOINT, maybe that's wrong? I have to read some sql pages I guess. – Richard May 08 '13 at 21:01
  • I posted an update to my answer – RolandoMySQLDBA May 08 '13 at 21:05
  • yes, I have try'd that too..see updated question. Is MULTIPOINT not just for testing if there is litterly an exact point that matches with a point in the database? Can it be used as a bounding box?http://dev.mysql.com/doc/refman/5.1/en/gis-class-multipoint.html – Richard May 08 '13 at 21:22
  • without the =1 at the end of the query, I get a #1416 error – Richard May 08 '13 at 21:23
  • No, it's annoying, but all of the combinations diddn't work, wasn't the wkb syntax the old syntax, maybe I need that. Am just guessing now ofcourse.. – Richard May 08 '13 at 22:24