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

1 Answer 1

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

5
  • 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
    Commented May 8, 2013 at 21:01
  • I posted an update to my answer Commented May 8, 2013 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
    Commented May 8, 2013 at 21:22
  • without the =1 at the end of the query, I get a #1416 error
    – Richard
    Commented May 8, 2013 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
    Commented May 8, 2013 at 22:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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