查询返回给定行在一定地理距离内的行（使用sql server 2008）

发布于 2024-08-16 23:00:19 字数 783 浏览 7 评论 0原文

我有一个包含多个记录的表，每个记录都包含一个名为“坐标”的字段。该字段已更新为地理点。

UPDATE testing SET [coords] = geography::Point(52.029736, -113.973541, 4326)
WHERE id=2"

我需要做的是......当用户登录时，他们有一个属于他们的记录，在这个例子中，它的记录ID为#1。他们需要访问一个页面，该页面显示“坐标”字段位于一定距离内的所有其他记录。

这是我想出的最好的办法；

首先，我可以找到与此语句一致的开头；

SELECT coords FROM testing WHERE id=1

这给了我原始坐标为 coords.Lat 和 coords.Long

然后我想找到附近的坐标，所以我有这个；

SELECT * FROM testing WHERE coords.STDistance() <=(20 * 1609.344)

我不知道如何将原始坐标放入第二个语句中以使其起作用。

我是否需要存储过程，或者我可以以某种方式将 coords.Lat/ coords.Long 放在 STDistance 的括号内吗？或者我完全不知道我期望它如何工作。

仅供参考，我对 sql server 的经验很少，我从来没有做过任何“高级”的事情，只是简单的 select * from table where record = 1 以及基本的插入和更新。

原文

I have a table with multiple records, each contains a field called "coords". This field has been updated with a geography point.

UPDATE testing SET [coords] = geography::Point(52.029736, -113.973541, 4326)
WHERE id=2"

What I need to do is... when a user is logged in, they have a record that belongs to them, for this example says its record id #1. They need to visit a page that shows all the other records who's "coords" field is located within a certain distance.

This is the best I have come up with;

1stly, i can find the starting coordinated with this statement;

SELECT coords FROM testing WHERE id=1

This gives me the originating coordinate as coords.Lat and coords.Long

Then I would like to find ones close by, so I have this;

SELECT * FROM testing WHERE coords.STDistance() <=(20 * 1609.344)

I do not know how to put the originating coords, into that second statement to make it work.

Do I need a stored procedure, or can I somehow put the coords.Lat/ coords.Long inside the brackets of the STDistance? Or am I tottaly off base on how I'm expecting this to work.

FYI, I have very little experience with sql server, I've never done anything "advanced", only simple select * from table where record = 1 and basic inserts and updates.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

抹茶夏天i‖ 2024-08-23 23:00:19

这行得通吗？

SELECT
    [near].* 
FROM 
    testing [near]
INNER JOIN
    testing [user] ON
    [user].coords.STDistance( [near].coords ) < (20 * 1609.344)
WHERE
    [user].id = 1

would this work ?

SELECT
    [near].* 
FROM 
    testing [near]
INNER JOIN
    testing [user] ON
    [user].coords.STDistance( [near].coords ) < (20 * 1609.344)
WHERE
    [user].id = 1

回复收藏 0 原文

ゃ人海孤独症 2024-08-23 23:00:19

这个怎么样？：

 DECLARE @g geography;
 SELECT @g = coords FROM testing WHERE id=1;        
 SELECT * FROM testing WHERE coords.STDistance(@g) <=(20 * 1609.344)

How about this?:

 DECLARE @g geography;
 SELECT @g = coords FROM testing WHERE id=1;        
 SELECT * FROM testing WHERE coords.STDistance(@g) <=(20 * 1609.344)

回复收藏 0 原文

彼岸花ソ最美的依靠 2024-08-23 23:00:19

我自己用 2 个选择和 2 个变量解决了这个问题...有什么建议可以让它变得更好吗？

sql1 = "SELECT coords.Lat, coords.Long FROM testing WHERE id=1"
lat2 = rs(0)
lon2 = rs(1)
sql2 = "SELECT * FROM testing WHERE id <> 1 AND coords.STDistance(geography::Point(" & lat2 & ", " & lon2 & ", 4326)) <=(20 * 1609.344)"
'20 miles or less

I solved it myself with 2 selects and 2 variables... Any suggestions to make it better?

sql1 = "SELECT coords.Lat, coords.Long FROM testing WHERE id=1"
lat2 = rs(0)
lon2 = rs(1)
sql2 = "SELECT * FROM testing WHERE id <> 1 AND coords.STDistance(geography::Point(" & lat2 & ", " & lon2 & ", 4326)) <=(20 * 1609.344)"
'20 miles or less

回复收藏 0 原文

~没有更多了~