[Solved] FetchXML – GeoCode Distance search by distance

by


You won’t be able to do this directly via FetchXml as geocoding is not offered as a feature.

This means you’ll need to start rolling something by hand which will not be easy. This site has a pretty good overview of what you’d need to do (even if the language is different to what you’d likely use with CRM).

One option may be to relax your requirement and just use FetchXml to look for records with a longitude +/- 1.5 miles and latitude +/- 1.5 miles (which of course queries an area shaped like a square rather than a radius).

If you need to be more strict, you could try to rewrite the following MySQL (i.e. not MSSQL!) query and use it alongside some sort of QueryExpression instead? So, for example, a query expression to determine a rough subset of data, as above (e.g. where longitude and latitude is with 1.5 miles of central point) and then after returning those results, for each one calculate if it falls within the desired radius…

SELECT id, X(gm_coor) AS latitude, Y(gm_coor) AS longitude,
  ATAN2(
    SQRT(
      POW(COS(RADIANS(__LAT__)) *
           SIN(RADIANS(Y(gm_coor) - __LNG__)), 2) + 
      POW(COS(RADIANS(X(gm_coor))) * SIN(RADIANS(__LAT__)) - 
          SIN(RADIANS(X(gm_coor))) * COS(RADIANS(__LAT__)) * 
          COS(RADIANS(Y(gm_coor) - __LNG__)), 2)), 
    (SIN(RADIANS(X(gm_coor))) * SIN(RADIANS(__LAT__)) + 
     COS(RADIANS(X(gm_coor))) * COS(RADIANS(__LAT__)) * 
     COS(RADIANS(Y(gm_coor) - __LNG__)))
  ) * 6372.795 AS distance 
FROM geocoded
HAVING distance < [RANGE IN KILOMETRES]

1

solved FetchXML – GeoCode Distance search by distance