CODEWITHSUNDEEP
mysqlsqlgoogle-mapsgoogle-maps-api-3gis
Nyxynyx
Nyxynyx

Reputation: 63619

Searching a distance around a point (Using MBRcontains)

I have an existing SQL query that uses lat, lng and a greater circle formula to calculate all listings within a 1km radius around a particular listing.

It works well, but I am trying to speed it up using MySQL Geospatial extension and using a MBR rectangle instead of a circle which is fine with me.

Problem: The new code that uses MBRcontains returns zero results! Did I miss out on something in the SQL query?

Lat: 42.353443 Lng: -71.076584

Old SQL Query

SELECT `price`, `city`, `state` FROM (`listings`) 
WHERE `bedroom` = '1' 
AND `post_timestamp` BETWEEN DATE_SUB( NOW(), INTERVAL 10 DAY) AND DATE_SUB( NOW(), INTERVAL 0 DAY) 
AND (6371 * acos( cos( radians(listings.lat) ) * cos( radians( 42.353443 ) ) * cos( radians( -71.076584 ) - radians(listings.lng) ) + sin( radians(listings.lat) ) * sin( radians( 42.353443 ) ) ) ) < 1 
LIMIT 100

New SQL Query (Formula)

SELECT `price`, `city`, `state` FROM (`listings`) 
WHERE `bedroom` = '1' 
AND `post_timestamp` BETWEEN DATE_SUB( NOW(), INTERVAL 10 DAY) AND DATE_SUB( NOW(), INTERVAL 0 DAY) 
AND MBRContains( 
    LineString( Point(42.353443 + 1 / ( 111.1 / COS(RADIANS(-71.076584))), 42.353443 + 1 / 111.1), 
                            Point(-71.076584 - 1 / ( 111.1 / COS(RADIANS(42.353443))), 42.353443 - 1 / 111.1) )
                            , p) 
) 
LIMIT 100

Upvotes: 0

Views: 2241

Answers (1)

Nguyen Viet Anh
Nguyen Viet Anh

Reputation: 96

I think you can try this sql:

SELECT `price`, `city`, `state` FROM (`listings`) 
    WHERE `bedroom` = '1' 
    AND `post_timestamp` BETWEEN DATE_SUB( NOW(), INTERVAL 10 DAY) AND DATE_SUB( NOW(), INTERVAL 0 DAY) 
    AND MBRContains( 
            LineString( 
                        Point(
                            42.353443 + 1 / ( 111.1 / COS(RADIANS(-71.076584))), 
                            -71.076584 + 1 / 111.1), 
                        Point(
                            42.353443 - 1 / ( 111.1 / COS(RADIANS(-71.076584))), 
                            -71.076584 - 1 / 111.1)
                            )
            , p) 
    ) 
    LIMIT 100

I have changed the lat-lon positions.

Upvotes: 2

Related Questions