gpt4 book ai didi

sql - 如何在 sql server 2014 中将经度和纬度存储为地理?

转载 作者:行者123 更新时间:2023-12-03 22:16:33 26 4
gpt4 key购买 nike

我在经度和纬度坐标中有位置。我的目标是最终能够从 myTable 中选择距离小于 2 公里的所有行。

  • 如何使用经度和纬度在地理列中存储位置?(因为它应该只有一个地理点而不是两个对吗?不是一个经度和一个纬度?)
  • 现在我已经有了地理点,我如何选择特定距离内的所有行(在我的例子中是 2 公里)?
  • 最佳答案

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



    您可以使用 geography::STPointFromText / geography::Point 将经度和纬度存储在地理数据类型中。
    SELECT geography::STPointFromText('POINT(' + CAST([Longitude] AS VARCHAR(20)) + ' ' + CAST([Latitude] AS VARCHAR(20)) + ')', 4326)

    或者
    SELECT geography::Point(Latitude, Longitude , 4326)

    引用链接:

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



    您可以使用 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);

    引用链接:

    Distance between two points using Geography datatype in sqlserver 2008?

    插入查询
    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);

    获取距离查询
    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;

    关于sql - 如何在 sql server 2014 中将经度和纬度存储为地理?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/30322924/

    26 4 0
    Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
    广告合作:1813099741@qq.com 6ren.com