计算两个充满GEOGRAPHY点的表格之间的距离 [英] Calculating distances between two tables full of GEOGRAPHY points

问题描述

我正在使用SQL Server.

我有两个这样的表:

表1 :

Column1, Column2, Column3, GeoLoc
-----------------------------------
a        b        c        0xE61...

表2 :

Column1, Column2, Column3, GeoLoc 
-----------------------------------
a        b        c        0xE62...

我正在寻找一个输出表，该表将比较两个表中的所有点，并向我显示table1的GeoLoc在table2中GeoLoc的X距离之内. >

有人知道该怎么做吗?一个表大约有800行，另一个表大约有300,000行.我很困惑，甚至从哪里开始...

解决方案

假定您的GeoLoc列在SQL Server中为地理位置"数据类型，则您应该可以使用以下内容:

select
  t1.*,
  t2.*,
  t1.GeoLoc.STDistance(t2.GeoLoc) as DistanceApart
from table1 t1
join table2 t2
on (t1.GeoLoc.STDistance(t2.GeoLoc) <= @distanceX)

"DistanceApart"和"distanceX"值以米为单位

I'm using SQL Server.

I have two tables like this:

Table1:

Column1, Column2, Column3, GeoLoc
-----------------------------------
a        b        c        0xE61...

Table2:

Column1, Column2, Column3, GeoLoc 
-----------------------------------
a        b        c        0xE62...

I am looking to get an output table which will compare all of the points in both tables, and show me where table1 has a GeoLoc which is within X distance of GeoLoc in table2.

Does anyone know how to go about this? One table has around 800 rows, and the other has about 300,000 rows. I'm stumped as to even where to start...

解决方案

Assuming your GeoLoc column is of the 'Geography' data type in SQL server, you should be able to use something like this:

select
  t1.*,
  t2.*,
  t1.GeoLoc.STDistance(t2.GeoLoc) as DistanceApart
from table1 t1
join table2 t2
on (t1.GeoLoc.STDistance(t2.GeoLoc) <= @distanceX)

with the 'DistanceApart' and 'distanceX' values being in meters

这篇关于计算两个充满GEOGRAPHY点的表格之间的距离的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！