Mysql, How to find intersaction of rectangular bounds

Question

I have saved bounds in my database table coming from google maps. Now i have to show these entries based on matching the bounds.

For example, I have a table for dashboard entries that are based on locations/bounds. I am saving bounds against each dashboard entry that is returned by google maps. Now, if user wanted to see the dashboard posts that are related to his city/country then i have to do matching based on user city/country bounds with dashboard entries bounds. So for that user, all those dashboard entries will appear whose bounds overlaps with user city/country bounds.

Right now i am trying with MySql OVERLAPS. But I amm unable to get proper result :(

Dump of table with some test entries:

INSERT INTO dashboard_entries 
  (id, category_id, entry_title, entry_description, customer_id, entry_date, latitude, longitude, bounds_ne_lat, bounds_ne_lng, bounds_sw_lat, bounds_sw_lng)
VALUES (150, 10, 'Blog Post for China Trip', 'Test Description', 43, '2012-02-08 15:17:46', '48.229147', '16.346052', 39.9084350, 78.6526391, 32.5301142, 116.3918633), (151, 16, 'Trip Post for Lahore Pakistan', 'Trip Post for Lahore Pakistan', 43, '2012-02-08 15:19:14', '48.229147', '16.346052', 34.0160320, 66.6578507, 24.7482340, 75.3818660);

Here is the query that i am using:

select * 
from dashboard_entries 
where Overlaps(GeomFromText('Polygon((34.0160320 66.6578507,34.0160320 75.3818660,24.7482340 75.3818660,24.7482340 66.6578507,34.0160320 66.6578507))'), GeomFromText('Polygon((bounds_ne_lat bounds_ne_lng,bounds_ne_lat bounds_sw_lng,bounds_sw_lat bounds_sw_lng,bounds_sw_lat bounds_ne_lng,bounds_ne_lat bounds_ne_lng))'))

Dump of table with some test entries: INSERT INTO `dashboard_entries` (`id`, `category_id`, `entry_title`, `entry_description`, `customer_id`, `entry_date`, `latitude`, `longitude`, `bounds_ne_lat`, `bounds_ne_lng`, `bounds_sw_lat`, `bounds_sw_lng`) VALUES (150, 10, 'Blog Post for China Trip', 'Test Description', 43, '2012-02-08 15:17:46', '48.229147', '16.346052', 39.9084350, 78.6526391, 32.5301142, 116.3918633), (151, 16, 'Trip Post for Lahore Pakistan', 'Trip Post for Lahore Pakistan', 43, '2012-02-08 15:19:14', '48.229147', '16.346052', 34.0160320, 66.6578507, 24.7482340, 75.3818660); — Suleman Siddiqui, Feb 09 '12 at 05:28
Here is the query that i am using: select * from dashboard_entries where Overlaps(GeomFromText('Polygon((34.0160320 66.6578507,34.0160320 75.3818660,24.7482340 75.3818660,24.7482340 66.6578507,34.0160320 66.6578507))'), GeomFromText('Polygon((bounds_ne_lat bounds_ne_lng,bounds_ne_lat bounds_sw_lng,bounds_sw_lat bounds_sw_lng,bounds_sw_lat bounds_ne_lng,bounds_ne_lat bounds_ne_lng))')) — Suleman Siddiqui, Feb 09 '12 at 05:46

score 1 · Accepted Answer · edited May 16 '12 at 08:16

I have used INTERSECT function of mysql to find the intersection on two rectangles. I believe Overlaps function is not implemented yet. It takes two rectangular bounds params as shown below. Second parameter is also a polygon that is saved in database.

SELECT * 
FROM dashboard_entries 
WHERE intersects(GeomFromText('Polygon((34.0160320 66.6578507,34.0160320 75.3818660,24.7482340 75.3818660,24.7482340 66.6578507,34.0160320 66.6578507))'), location_bounds)

Mysql, How to find intersaction of rectangular bounds

1 Answers1