How can I sort/group Salesforce leads by geography ?

Question

If I had lat/long data for all our leads in Salesforce, is there a way to write a query to group them, or say list all the leads within 10 miles of San Francisco, CA ?

[EDIT: Clarification] I have thousands of leads with both a full address, and long/lats. I want to build a query on these leads that will give me all of the leads near San Francisco, CA. This means doing GIS type work within salesforce.

I could of course filter specifically on city, or zipcodes or area code, but this presents some problems when trying to rollup a whole metro area.

Answer 1

Yes. You need to Reverse GeoCode them with a tool/service. In the past I have used Maporamas service but it was quite expensive and that was before Google maps and virtual earth existed so I am sure there is something cheaper(free) out there now.... Googling around I have found this and this

EDIT:

OK from What I understand you are trying to calculate the distance between 2 lat/long points. I would start by discounting the ones that where outside you sphere of (lets say) 10 miles. So from your central point you will want to get the the coordinates 10 miles, East, West, South and North. To do this you need to use the Great-circle distance formula.

From that point you have you Sales Force Data if you wish to break this data up further then you need to order the points by distance from the central point. To do this you need to use the Haversine formula

I am not sure what you language preference is so I just included some examples from SQL(mainly) and C#

Haversine Formula in C# and in SQL

Determine the distance between ZIP codes using C#

Great Circle SQL

Great Circle 2

Answer 2

You may want to check out this article on visualizing and mapping SalesForce leads using SpatialKey.

Answer 3

Use GeoHash.org (either as a web service or implement the algorithm). It hashes your lat-long coords into a form that appears similar for nearby places. For example A may have a hash like "akusDf3af" and B might have a hash like "akusDf3b2" if they are nearby. Then do a SOQL query that looks for places starting with the same n characters as a known location. Your n will determine the radius of the lookup.

Answer 4

These are some great technical solutions that can provide very exact answers, but two things to consider:

• geospatial proximity does not map neatly to responsibility

Ownership calculation seems to be done best through postal code lookups or other rules that don't allow for gaps or overlaps. Otherwise, you'll have two (or more) salespeople fighting over leads that are close to both of them, and ignore those leads that are far away from both of them.

So, if you're using geo-calculations like those above to assign ownership, just acknowledge the system will leak and create business rules to accomodate for that. But a simple postal lookup to define territories (as salesforce's own territory management feature does) might be better.

I'd suggest the problem we're trying to solve geospatially is not who owns which lead. Rather, given all the leads you own, which are nearby?

• maps often offer more data per pixel than columnar reports

Again, geospatial data in a report may not be the best answer. A lead 50km away, but along a major road, is more interesting than another lead 50km away on the other side of a mountain or lake. Or a lead close to other leads is more interesting than a lead by itself.

A report can't show this, but a map can.

Salesforce has some great examples of Google Maps integrations. Instead of a columnar report called "My Nearby Leads", why not a visualforce page, with a google map inside? You're giving the user far more information than a columnar report could. They might like it better, and it's easier to implement than trying to calculate some of the equations above.

Just another perspective that may (or may not) be appropriate to the problem at hand.