sql-server-2008 – 在sqlserver 2008中使用Geography数据类型的两点之间的距离？

发布时间：2020-05-24 09:53:17 所属栏目：MsSql 来源：互联网

导读：这是我的代码. Create Table [dbo].[MajorCities]([CityID] int Identity(1,1),[CityName] varchar(60),[Latitude] float,[Longitude] float,GeoRef Geography)INSERT INTO dbo.[MajorCities] values (New Delh

这是我的代码.

Create Table [dbo].[MajorCities]
(
[CityID] int Identity(1,1),[CityName] varchar(60),[Latitude] float,[Longitude] float,GeoRef Geography
)

INSERT INTO dbo.[MajorCities] values
 ('New Delhi,India',28.6,77.2,null),('Paris,France',48.86667,2.3333,('Rio de Janeiro,Brazil',-22.9,-43.23333,('Sydney,Australia',-33.88306,151.21667,('New York City,USA',40.78333,-73.96667,null)

select * from [MajorCities]


UPDATE [dbo].[MajorCities]
SET [GeoRef] = geography::STPointFromText ('POINT (' + CAST ([Longitude] AS VARCHAR (20)) + ' ' +
                    CAST ([Latitude] AS VARCHAR (20)) + ')',4326)

我想找到KM中两个位置之间的距离.

请帮我.
谢谢.

解决方法

如果你有纬度和经度的度数(就像你的表中一样),那么你可以使用以下功能：

CREATE FUNCTION dbo.DictanceKM(@lat1 FLOAT,@lat2 FLOAT,@lon1 FLOAT,@lon2 FLOAT)
RETURNS FLOAT 
AS
BEGIN

    RETURN ACOS(SIN(PI()*@lat1/180.0)*SIN(PI()*@lat2/180.0)+COS(PI()*@lat1/180.0)*COS(PI()*@lat2/180.0)*COS(PI()*@lon2/180.0-PI()*@lon1/180.0))*6371
END

或者,如果您坚持使用地理类型,则使用方法是：

DECLARE @g geography;
DECLARE @h geography;
SET @g = geography::STGeomFromText('LINESTRING(-122.360 47.656,-122.343 47.656)',4326);
SET @h = geography::STGeomFromText('POINT(-122.34900 47.65100)',4326);
SELECT @g.STDistance(@h);

（编辑：安卓应用网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!