10

Afternoon,

I need to search for all objects within X miles of a set of known geo-located lat-longs. I've been looking at the geolocation features of SF but I don't see any mention of using multiple targets for the query. I'm sure I could do something like:

WHERE DISTANCE(mylocation1, mylocation2, 'unit') OR DISTANCE(mylocation1, mylocation3, 'unit') OR ...

However, This isn't a set of 10, or 20 points, but more like a set of 10-150 thousand points.

For example:

  1. Given a Object (named Intersection) containing LAT/LONG for every intersection named 1st and Martin Luther King Dr.
  2. Given an Object (named Resturant) containing LAT/LONG for every McDonalds Restaurant in the US.

    I want to find every intersection within 10 miles of any McDonalds restaurant in the US.

Fun right? Any ideas on how to do this? I'm sure I'd hit query limits before I enumerated all the various restaurant locations.

2

That does sound fun! Depending on your definition of fun :)

I'd probably opt for pre-caching the distance information on Location, via trigger. I'd add a field to Location called something like "Distance to Nearest Restaurant". On each location record, upon insert and update (if intersections move :), I'd query the nearest restaurant, and then store the distance (or lat/long, or reference to the Restaurant object) in that new field.

Of course, as Restaurants open/close/move, you'll need to handle updating nearby locations also, which is why storing a reference to the closest Restaurant (possibly in addition to distance) may be helpful.

Then you can simply query all Locations where distance < 10, etc.

If you have multiple types of Restaurants (thinking like banks where geolocation cares about closest ATM/branch/affiliate etc), this approach may require more fields. Also, if the filtering by Restaurant needs to be adhoc or complex, this approach may not work.

I'm pretty curious as to what the actual scenario is, assuming it isn't McDonalds!

0

you will need to fire queries on both the objects and get the big list of suspected records, then write the post filtering logic.

Since the Geolocation distance search in sfdc is quiet new, so definitely its not that flexible.

0

If query speed isn't critical, then you could try breaking the problem down.

So rather than finding every​ intersection within 10 miles of ​any​ McDonalds restaurant in the US you:

  1. Run a batch to find every McDonalds restaurant in the US (adjust scope accordingly)
  2. Within the batch execution, run a geolocation query to find every intersection within 10 miles of that restaurant using the DISTANCE function.
  3. Do something with the matching locations

This wouldn't be so great if a user is watching the screen waiting for the results.

-1

As a thought (more logic wise than a coding solution), would it be possible to reduce the number of calls by limiting the search to locations within a 20km (i.e. 2 times the search radius) grid with the set known location at the centre.

That is you don't check distances if the locations are outside of 10km north, east, south or west of the known object. Obviously you will still have some locations in the "corners" of the grid that are outside of the 10km radius, but it would still eliminate a vast number of the locations to check.

If you worked out the long and lat brackets to search in you could then do a SOQL query to return the possible locations within the grid and then calculate their distances.

Not sure if this would help, but it's an idea.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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