I have a customer in NYC that we are helping to implement Field Service using Dynamics 365 (online). One of their hot issues is making it easy for them to find all other accounts near a given location, so I started thinking about the problem.
One of the features built into that solution is geocoding, so that all account addresses get a geocode. If I could use SQL queries, I would be able to use a native SQL query to find all addresses near a given point. As far as I know, the FetchXML syntax does not do those kinds of queries.
To narrow down the problem space a bit, my client only works in the NYC area which makes things a bit easier because we can simplify the variables: We don't need to worry about the curvature of the Earth over long distances, or the distance between longitudes for a given latitude. I am going to simplify this a bit more. If I ignore the fact that a radius around a point is the best representation of what is nearby, and I use a rectangle which is good enough for my clients purposes, then the query is super simple because now I just need compare the current Lat/Long with ones that are between "+" or "-" a difference in order to define the rectangle.
Lets say, from a given Lat/Long, we need to know who is inside a vicinity of 1 square mile. That means we are interested in all locations that are roughly 1/2 mile North, 1/2 mile South, 1/2 mile East and 1/2 Mile West from the current point.
Each degree of latitude is 69.99 miles apart.
At 40 degrees latitude (NYC), the longitudes are 53.08 miles apart.
Therefore, 1 mile is about (keep it simple):
1/69=0.0145 degrees of latitude
1/53=0.0189 degrees of longitude
So if we search for all addresses that are within one square mile, then we are looking for a Lat/Long that fall within the range of the
current location +/- half the 1 mile area.
So if we are at this location: 40.61938 Lat, -73.91799 Long, then we are looking for all addresses where the latitude is:
40.61938-(.0145/2)=40.61213 and 40.61938+(.0145/2)=40.62663 (up to 1/2 mile South and 1/2 mile North of us)
And the longitude is:
-73.91799-(.0189/2)=-73.90854 and -73.91799+(.0189/2)=-73.92744 (up to 1/2 mile East and 1/2 mil e West of us)
and in Advanced Find, would look like this:
Remember, because NYC is in the western hemisphere, the longitude is negative.
Ultimately my goal is to take this kind of query, build it into a JavaScript on a form and populate a map with the points. But that is a blog for another day.
