给定方形网格坐标,如何计算属于该范围的不同表中的数据? [英] Given the square grid coordinates, how to calculate the data from a different table that falls under that range?
问题描述
我的表格中有一堆坐标 grid 具有以下架构 id,x1,x2,y1,y2,如下所示:
I have a bunch of coordinates in my table grid with the following schema id,x1,x2,y1,y2 like these:
22,910000,920000,120000,130000
67,930000,940000,170000,180000
171,980000,990000,210000,220000
Grid 表也有每个坐标元组的 ID.
Grid table also has an ID for each coordinate tuple.
在单独的表crashes中,我有关于汽车碰撞的信息,其中最后两个值是x_coordinate"和y_coordinate"' 分别.
In a separate table crashes I have an information about the automobile crashes, where the last two values are 'x_coordinate' and 'y_coordinate' respectively.
2007,2,9,4,1,1028977,202232
2004,1,1,1,4,1012600,214101
2003,1,9,1,1,958775,156149
1999,1,1,1,1,997349,175503
1-如何计算每个方格的崩溃次数?(显示 2 列:网格 ID 和与之相关的崩溃次数)
1-How could I compute the number of crashes for each square grid? (display 2 columns: Grid ID and # of crashes associated with it)
2-如果往另一个方向走,我将如何检索在以下几年内发生超过 60 次崩溃的所有方形网格"(x1、x2、y1、y2 的元组):2005、2006 和2007年?(在 HTML 中,它看起来像一个包含 3 列的表格:2005 | 2006 | 2007 和每年以下 - 满足 >=60 车祸的标准的坐标元组 x1,x2,y1,y2.
推荐答案
#1 很简单:(这原本是如何从网格表中计算出哪些车祸属于x1,x2,y1,y2坐标的方格?")
#1 is easy: (this originally was "How would I calculate which automobile accidents fall into the square grid of x1,x2,y1,y2 coordinates from the grid table?")
SELECT DISTINCT
grid.ID
FROM crashes
INNER JOIN grid
ON crashes.x_coordinate BETWEEN grid.x1 AND grid.x2
And crashes.y_coordinate BETWEEN grid.y1 AND grid.y2
#2 只是有点难:(这最初是我将如何检索在接下来的几年中发生超过 60 次崩溃的所有方形网格"(x1、x2、y1、y2 的元组)...?)
#2 is only a bit harder: (this originally was "how would I retrieve all "square grids" (tuple of x1,x2,y1,y2) that have more than 60 crashes in the following years...?")
SELECT
grid.ID, COUNT(*) AS CrashCount
FROM crashes
INNER JOIN grid
ON crashes.x_coordinate BETWEEN grid.x1 AND grid.x2
And crashes.y_coordinate BETWEEN grid.y1 AND grid.y2
WHERE crashes.yearCol IN(2005, 2006, 2007)
GROUP BY grid.ID
HAVING COUNT(*) >= 60
<小时>
修改问题的解决方案...
solutions for the revised questions ...
对于#1我如何计算每个方格的崩溃次数?",只是原始#2的简化:
For #1 "How could I compute the number of crashes for each square grid?", is just a simplification of the original #2:
SELECT
grid.ID, COUNT(*) AS CrashCount
FROM crashes
INNER JOIN grid
ON crashes.x_coordinate BETWEEN grid.x1 AND grid.x2
And crashes.y_coordinate BETWEEN grid.y1 AND grid.y2
WHERE crashes.yearCol IN(2005, 2006, 2007)
GROUP BY grid.ID
对于#2我将如何检索所有在(每个中有超过 60 次崩溃的方形网格"(x1、x2、y1、y2 的元组)>) 以下年份:2005、2006 和 2007??
For #2 "how would I retrieve all 'square grids' (tuple of x1,x2,y1,y2) that have more than 60 crashes in (each of) the following years: 2005,2006 and 2007??
SELECT *
FROM
(
SELECT grid.ID,
grid.x1, grid.x2, grid.y1, grid.y2,
( SELECT COUNT(*)
FROM crashes
WHERE yearCol = 2005
And crashes.x_coordinate BETWEEN grid.x1 AND grid.x2
And crashes.y_coordinate BETWEEN grid.y1 AND grid.y2
) As year05,
( SELECT COUNT(*)
FROM crashes
WHERE yearCol = 2006
And crashes.x_coordinate BETWEEN grid.x1 AND grid.x2
And crashes.y_coordinate BETWEEN grid.y1 AND grid.y2
) As year06,
( SELECT COUNT(*)
FROM crashes
WHERE yearCol = 2007
And crashes.x_coordinate BETWEEN grid.x1 AND grid.x2
And crashes.y_coordinate BETWEEN grid.y1 AND grid.y2
) As year07,
FROM grid
) As gridSum
WHERE year05 >= 60
And year06 >= 60
And year07 >= 60
在我们有 WITH 子句的 TSQL 中,这个更容易实现...
This one is a bit easier to do in TSQL where we have the WITH clause to work with...
这篇关于给定方形网格坐标,如何计算属于该范围的不同表中的数据?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!