更新表格中的“地理位置"列 [英] Update Geography column in table

问题描述

我有下表

您可以找到，地理位置"列(数据类型为地理位置")为空，我目前在此表中有11913行，我要尝试的操作是使用以下语句更新地理位置"列，并使用以下数据填充地理位置"列由Geography :: STGeomFromText提供

As you can the Geo column (data type Geography) is null I currently have 11913 rows in this table what I'm trying to do is update the Geo column by using the following statement and populate the Geo column with the data that is provided by Geography::STGeomFromText

DECLARE @Temp TABLE
(
Id bigint,
Latitude decimal(9,6),
Longitude decimal(9,6)
)

Insert Into @Temp (Id, Latitude, Longitude)
    Select id, Latitude, Longitude from Location.Cities 
where Active = 1

Update Location.Cities
set Geo = geography::STGeomFromText (POINT(Select Latitude, Longitude from  @Temp), 4326)
where Id = -- massively confused.....

在@Temp中选择纬度，经度"时遇到了两个问题，它说POINT不是公认的内置函数名称，另一个是如何确保我在正确的位置更新了正确的记录/行从中选择了纬度和经度.

Two issues I have come against where I say Select Latitude, Longitude from @Temp it says POINT is not a recognized built-in function name and the other is how can I make sure I update the right record/row where I've selected the latitude and longitude from.

之所以需要这样做，是因为在我们的应用程序中，我们允许最终用户按半径搜索.

The reason I need to do this is because on our application we are allowing the end user to search by radius.

任何帮助都会很棒.

推荐答案

您不需要临时表@Temp.您可以在表Location.Cities上直接使用 geography::Point .

You don't need a temp table @Temp. You can use geography::Point directly on your table Location.Cities.

类似这样的东西.

Update Location.Cities
set Geo = geography::Point(Latitude, Longitude , 4326)

如果您要使用 geography::STGeomFromText ，则可以使用像这样

If you want to use geography::STGeomFromText, you can use it like this.

    Update Location.Cities
        set Geo = geography::STGeomFromText('POINT(' + CONVERT(VARCHAR(30),Longitude ) + ' ' + CONVERT(VARCHAR(30),Latitude) + )',4326)

这篇关于更新表格中的“地理位置"列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！