如何在SQL server数据库表中使用X和Y网格坐标选择数据循环?

This is a fun one, apologies for the lack of information but I don't have a clue about how to approach this.

这是一个很有趣的问题，对信息的缺乏表示歉意，但是我不知道如何处理这个问题。

I have a SQL Server database which is storing map coordinates against post area codes. For example postal area code BD6 is located at 414300m along the X axis and 429800m along Y axis. These values are in one table, in three separate columns, e.g:

我有一个SQL Server数据库，它根据邮政区域代码存储地图坐标。例如，邮政区号BD6位于X轴414300米，Y轴429800m。这些值在一个表中，在三个独立的列中，例如:

PostAreaCode    AlongXAxis    AlongYAxis
BD6               414300       429800

I need to select all post area codes within a 161km radius of a particular post area code. So for example, the coordinates for BD6 are in the middle of the circle, and the edge of the circle is 161km away.

我需要选择所有邮递区号在一个特定邮递区号的161公里半径内。例如，BD6的坐标在圆的中间，圆的边缘在161千米之外。

I can create a square around BD6 by using the BETWEEN keyword and doing something like...

我可以使用BETWEEN关键字创建一个围绕BD6的正方形，并做一些类似的事情……

SELECT PostAreaCode
FROM PostAreaCodeTable
WHERE AlongXAxis IS BETWEEN (414300-161000) AND (414300+161000)
AND AlongYAxis IS BETWEEN (429800-161000) AND (429800+161000)

...but I have no idea how to make it a circle. I consider myself to be pretty good at SQL but this is completely beyond me. Any help is much appreciated! Please explain mathsy answers as much as possible because maths is not my strong point. Thanks!

…但是我不知道怎么把它变成一个圆。我认为自己非常擅长SQL，但这完全超出了我的能力。非常感谢您的帮助!请尽可能多地解释数学答案，因为数学不是我的强项。谢谢!

2 个解决方案

#1

You can use the formula (x-a)^2 + (y-b)^2 <= r^2 as your where clause. a and b are the co-ordinates of the center and x and y are the co-ordinates of the point you want to check for and r is radius. So, in your case, a is the X co-ordinate of center and b is Y co-ordinate of center, while x and y are the column names for the X and Y axis co-ordinates of the other points.

您可以使用公式(x)^ 2 +(得到)^ 2 < = r ^ 2作为where子句。a和b是中心的坐标，x和y是点的坐标，r是半径。在你的例子中，a是中心的X坐标，b是中心的Y坐标，而X和Y是其他点的X和Y轴坐标的列名。

SQL would be:

SQL将:

SELECT PostAreaCode
FROM PostAreaCodeTable
WHERE ((AlongXAxis - 414300)^2 + (AlongYAxis - 429800)^2) <= (161000)^2

#2

You can also take advantage of STDistance function if you're able to convert your points into coordinates.

如果你能将你的点转换成坐标，你也可以利用STDistance函数。

I wrote the following assuming you cannot edit your actual table to include a coordinate column.

假设您无法编辑实际的表以包含一个坐标列，我编写了以下内容。

1st Create a temporary table and convert your x,y to Geometry Coordinates:

创建一个临时表，将x,y转换为几何坐标:

CREATE TABLE #PostAreaCodeTable2 (ID INT, AlongXAxis FLOAT, AlongYAxis FLOAT,  Coordinates GEOMETRY)

INSERT INTO #PostAreaCodeTable2
        ( ID ,
          AlongXAxis ,
          AlongYAxis ,
          Coordinates
        )
SELECT  ID ,
        AlongXAxis ,
        AlongYAxis,
        geometry::STGeomFromText('POINT(' + CAST( AlongXAxis AS VARCHAR(10)) + ' ' + CAST( AlongYAxis AS VARCHAR(10)) +')', 4326) 
FROM PostAreaCodeTable

Next join your normal table to the temp table with a where clause of the STDistance function:

接下来，将您的普通表与STDistance函数的where子句连接到临时表:

DECLARE @source  GEOMETRY = geometry::STGeomFromText('POINT(414300  429800)',4326) --All Points are measured from X 414300 and from Y 429800

SELECT *, Coordinates.STAsText() AS Target,@source.STDistance(Coordinates) AS Distance
FROM PostAreaCodeTable PACT
JOIN #PostAreaCodeTable2  PACT2 on PACT.ID = PACT2.ID
WHERE @source.STDistance(Coordinates) <= 161000 --coordinates in table are less than or equal too 161000 from source

#1