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
                    )
  • I am glad you are working through this one. +1 for your due diligence. Sorry I could not help. – RolandoMySQLDBA May 9 '13 at 13:55
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 !!!

  • 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 8 '13 at 21:01
  • I posted an update to my answer – RolandoMySQLDBA May 8 '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?dev.mysql.com/doc/refman/5.1/en/gis-class-multipoint.html – Richard May 8 '13 at 21:22
  • without the =1 at the end of the query, I get a #1416 error – Richard May 8 '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 8 '13 at 22:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.