0

I have a table, stores, with thousands of stores that deliver. If i have the lat, lng, and delivery_radius for each store (I can add a point column), what is the most efficient way to query the table to see which stores can deliver to where I stand currently?

I feel that checking if the distance between myself and each row is less than the delivery_radius would be a very long process. Would it be best to add a column to store a polygon calculated from each row's info and see if my current point is in that polygon (point-in-polygon)? Any other suggestions?

Patrick H.
  • 66
  • 11

1 Answers1

1

You can get the distance between two geo points by using following code segment in a SQL query.

ROUND((3959 * acos(cos(radians(IFNULL(P1.LAT, 0))) * cos(radians(IFNULL(P2.LAT, 0))) * cos(radians(IFNULL(P2.LNG, 0)) - radians(IFNULL(P1.LNG, 0))) + sin(radians(IFNULL(P1.LAT, 0))) * sin(radians(IFNULL(P2.LAT, 0))))),3) AS DISTANCE

However this is very costly operation and you will definitely have performance issues when the data grows. Maintaining a polygon also might be difficult as you have to update the polygon for each new store addition and the update process will exponentially slow down when data grows.

If it is not really a need to have this on a RDMBS please consider about using other technology like elasticsearch which natively support this kind of operations. Please refer https://www.elastic.co/guide/en/elasticsearch/reference/current/geo-queries.html

  • I think you may have been misunderstanding the polygon. This wouldn't be a single polygon that contains all stores. This would be a polygon saved for each store. That way my current location could be compared to that polygon. My theory is that this eliminates one more calculation of creating the "serviceable area" for each store. Does this change your take on it at all? Definitely looking into elasticsearch. – Patrick H. Apr 20 '17 at 19:38
  • I actually quite don't understand the polygon concept that you are mentioning. Just to clarify, Is your location is dynamic or a static one? How the polygon is getting impacted once new store is added? Is the requirement is to identify list of stores which can deliver to your current location? How the polygon address this with new store addition? –  Apr 21 '17 at 16:37
  • My location is dynamic, it will be created depending on my location. The polygon concept I'm referring to is a point in polygon idea. My current location is a point, but I could save the delivery radius of each store as a polygon. Is it quicker to search by seeing if my current point is in the polygon of each store rather than calculating the distance between each and seeing if its less than or equal to the radius? – Patrick H. Apr 24 '17 at 15:16
  • Ok, I'm still confused with your polygon idea. Because your search result should changed when you change the location. For an example when you change your location form X to Y the number of applicable store should change with the distance you traveled. Subset of previously out of rage stores might have some closer. I'm still not sure how it can be done with static set and I still feel either you have to either change the polygon or calculate the distance dynamically and filter the stores within range when the location is change. Sometimes I might not understood the requirement correctly. –  Apr 24 '17 at 15:57