如何在 sql server 2014 中将经度和纬度存储为地理？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/30322924/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to store longitude & latitude as a geography in sql server 2014?
提问by Mattias
I have locations in longitude and latitude coordinates. My goal is eventually to be able to select all rows from myTable Where distance is less than 2km.
我有经度和纬度坐标的位置。我的目标是最终能够从 myTable 中选择距离小于 2km 的所有行。
How can one use the longitude and latitude to store location within a geography column?(because it's supposed to be only one geographic point not two right? not one for longitude and one for latitude?)
Now that I've got the geography points, how can i select all the rows within a specific distance(in my case 2km)?
如何使用经度和纬度来存储地理列中的位置？（因为它应该只有一个地理点而不是两个，对吗？一个不是经度，一个是纬度？）
既然我已经有了地理点，我该如何选择特定距离（在我的情况下为 2 公里）内的所有行？
回答by ughai
How can i use the longitute and latitute to store location within a geography column?(because it's supposed to be only one geographic point not two right? not one for longitute and one for latitute?)
我如何使用经度和纬度在地理列中存储位置？（因为它应该只有一个地理点而不是两个，对吗？一个不是经度，一个是纬度？）
You can use geography::STPointFromText/ geography::Pointto store longitude and latitude in a geography datatype.
您可以使用geography::STPointFromText/geography::Point在地理数据类型中存储经度和纬度。
SELECT geography::STPointFromText('POINT(' + CAST([Longitude] AS VARCHAR(20)) + ' ' + CAST([Latitude] AS VARCHAR(20)) + ')', 4326)
or
或者
SELECT geography::Point(Latitude, Longitude , 4326)
Reference Link:
参考链接：
Update Geography column in table
Now that I've got the geography points, how can i select all the rows within a specific distance(in my case 2km)?
既然我已经有了地理点，我该如何选择特定距离（在我的情况下为 2 公里）内的所有行？
You can use STDistancelike this.
你可以这样使用STDistance。
DECLARE @g geography;
DECLARE @h geography;
SET @g = geography::STGeomFromText('POINT(-122.35900 47.65129)', 4326);
SET @h = geography::STGeomFromText('POINT(-122.34720 47.65100)', 4326);
SELECT @g.STDistance(@h);
Reference Link:
参考链接：
Distance between two points using Geography datatype in sqlserver 2008?
在 sqlserver 2008 中使用 Geography 数据类型的两点之间的距离？
Insert Query
插入查询
DECLARE @GeoTable TABLE
(
id int identity(1,1),
location geography
)
--Using geography::STGeomFromText
INSERT INTO @GeoTable
SELECT geography::STGeomFromText('POINT(-122.35900 47.65129)', 4326)
--Using geography::Point
INSERT INTO @GeoTable
SELECT geography::Point(47.65100,-122.34720, 4326);
Get Distance Query
获取距离查询
DECLARE @DistanceFromPoint geography
SET @DistanceFromPoint = geography::STGeomFromText('POINT(-122.34150 47.65234)', 4326);
SELECT id,location.Lat Lat,location.Long Long,location.STDistance(@DistanceFromPoint) Distance
FROM @GeoTable;
回答by Tajkumar
Addition to the above Answer @ughai
除了上述答案@ughai
Adding a column
添加列
ALTER TABLE [dbo].[Landmark]
ADD [GeoLocation] GEOGRAPHY
GO
Convert the Longitude and latitudes to Geography
将经度和纬度转换为地理
UPDATE [dbo].[Landmark]
SET [GeoLocation] = geography::STPointFromText('POINT(' + CAST([Longitude]
AS VARCHAR(20)) + ' ' +
CAST([Latitude] AS VARCHAR(20)) + ')', 4326)
GO
Finding Places in the radius of 2kms
在 2kms 范围内寻找地点
DECLARE @Origin GEOGRAPHY,
-- distance defined in meters
@Distance INTEGER = 2000;
SET @Origin = GEOGRAPHY::STGeomFromText('POINT(17.482477 78.546871)', 4326);
-- return all rows from events in 2km radius
SELECT *,GeoLocation.STDistance(@Origin) Distance FROM dbo.Locations WHERE @Origin.STDistance(GeoLocation) <= @Distance;
it worked for me to get the places within the distance
找到距离内的地方对我来说很有用
回答by Deepak Singla
You can convert lat and long to a point and save it in table.
您可以将 lat 和 long 转换为一个点并将其保存在表中。
Declare @geo Geography, @Lat varchar(10), @long varchar(10)
声明@geo Geography、@Lat varchar(10)、@long varchar(10)
SET @Lat = '34.738925' SET @Long = '-92.39764'
设置@Lat = '34.738925' 设置@Long = '-92.39764'
SET @geo= geography::Point(@LAT, @LONG, 4326)
SET @geo= geography::Point(@LAT, @LONG, 4326)
回答by ΩmegaMan
In working with GeoPoints in sprocs and selects, it became tiring to always compute the GeoPointso instead I found it convenient to create a computed column which generates a GeoPointfrom the lat/long FLOATfields when the row is selected.
在 sprocs 和 selects 中使用 GeoPoints 时，总是计算它变得很累，GeoPoint所以我发现创建一个计算列很方便，该列在选择行时GeoPoint从 lat/longFLOAT字段生成 a 。
[GeoPoint] AS ([geography]::Point([Latitude],[Longitude],(4326))) PERSISTED,
