使用 SQL Server 查找最近场地的半正弦公式 - vb.net [英] Haversine formula using SQL server to find closest venue - vb.net
问题描述
我正在从表单中获取邮政编码.然后我可以将此邮政编码转换为 lng,lat 坐标,因为我将这些坐标存储在表中.
I am grabbing a postcode from a form. I can then convert this postcode to lng,lat coordinates as I have these stored in a table.
SELECT lng, lat from postcodeLngLat WHERE postcode = 'CV1'
我有另一个表,用于存储所选场地的 lng、lat.
I have another table which stores the lng,lat of a selection of venues.
SELECT v.lat, v.lng, v.name, p.lat, p.lng, p.postcode, 'HAVERSINE' AS distance fromvenuepostcodes v, postcodeLngLat p WHERE p.outcode = 'CB6'按距离排序
我想做的是创建一个数据网格,显示每个场地与邮政编码(在本例中为 CV1)的距离.我知道Haversine 公式应该做我想要实现的目标,但是我不知道应该从哪里开始将它合并到我的查询中.我认为公式需要转到我在上面的查询中放置 'HAVERSINE'
的位置.
What I am trying to do is create a datagrid which shows the distance of each venue from the postcode (CV1 in this case). I know that the Haversine formula should do what I am trying to achieve but I'm lost as to where I should start incorporating it into my query. I think the formula needs to go where I've put 'HAVERSINE'
in the query above.
有什么想法吗?
编辑
SELECT o.outcode AS Lead_postcode, v.venue_name, 6371.0E * ( 2.0E *asin(case when 1.0E < (sqrt(square(sin(((RADIANS(CAST(o.lat AS FLOAT))))-(弧度(CAST(v.lat AS FLOAT)))))/2.0E)) + (cos(RADIANS(CAST(v.lat AS FLOAT))) * cos(RADIANS(CAST(o.lat AS)FLOAT))) * square(sin(((RADIANS(CAST(o.lng AS FLOAT)))-(RADIANS(CAST(v.lng AS FLOAT)))))/2.0E))))) 然后 1.0E else(sqrt(square(sin(((RADIANS(CAST(o.lat AS FLOAT)))-(RADIANS(CAST(v.lat AS FLOAT))))/2.0E)) + (cos(RADIANS(CAST(v).lat AS FLOAT))) * cos(RADIANS(CAST(o.lat AS FLOAT))) * square(sin(((RADIANS(CAST(o.lng AS FLOAT)))-(RADIANS(CAST(v.lng)AS FLOAT))))/2.0E))))) 结束 )) AS 距离场地邮政编码 v, outcodepostcodes o WHERE o.outcode = 'CB6' ORDER BY distance
推荐答案
我认为您最好将它放在 UDF 中并在您的查询中使用它:
I think you'd do best putting it in a UDF and using that in your query:
SELECT v.lat, v.lng, v.name, p.lat, p.lng, p.postcode, udf_Haversine(v.lat, v.lng, p.lat, p.lng) AS distance FROM venuepostcodes v, postcodeLngLat p WHERE p.outcode = 'CB6' ORDER BY distance
create function dbo.udf_Haversine(@lat1 float, @long1 float, @lat2 float, @long2 float) returns float begin
declare @dlon float, @dlat float, @rlat1 float, @rlat2 float, @rlong1 float, @rlong2 float, @a float, @c float, @R float, @d float, @DtoR float
select @DtoR = 0.017453293
select @R = 3937 --3976
select
@rlat1 = @lat1 * @DtoR,
@rlong1 = @long1 * @DtoR,
@rlat2 = @lat2 * @DtoR,
@rlong2 = @long2 * @DtoR
select
@dlon = @rlong1 - @rlong2,
@dlat = @rlat1 - @rlat2
select @a = power(sin(@dlat/2), 2) + cos(@rlat1) * cos(@rlat2) * power(sin(@dlon/2), 2)
select @c = 2 * atn2(sqrt(@a), sqrt(1-@a))
select @d = @R * @c
return @d
end
这篇关于使用 SQL Server 查找最近场地的半正弦公式 - vb.net的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!