Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
                    )
share|improve this question
    
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

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

share|improve this answer
    
ah, thanks..I will look into that.. –  Richard May 8 '13 at 20:40
    
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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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