FetchXML-GeoCode距离搜索距离 [英] FetchXML - GeoCode Distance search by distance

问题描述

我有一个包含纬度和经度字段的实体。

I have an entity which has latitude and longitude fields.

现在我有一个地址，我知道此地址的纬度和经度，我想检索记录在3英里内。

Now I have a address, I know the latitude and longitude of this address, I want to retrieve the records within 3 miles.

如何使用FetchXML做到这一点？

How to do it using FetchXML?

推荐答案

由于没有提供地理编码功能，因此您将无法直接通过FetchXml执行此操作。

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).

一种选择可能是放宽您的要求，而仅使用FetchXml查找经度为+/-的记录 1.5英里和纬度+/- 1.5英里（当然，它查询的是形状像正方形而不是半径的区域）。

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).

如果需要更加严格，您可以尝试重写以下 MySQL 查询（即不是MSSQL！）并将其与某种QueryExpression一起使用吗？因此，例如，一个查询表达式确定一个粗略的数据子集（如上）（例如，经度和纬度为中心点1.5英里），然后在返回这些结果之后，针对每个结果计算其是否落在所需半径内...

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]

这篇关于FetchXML-GeoCode距离搜索距离的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！