给定方形网格坐标，如何计算属于该范围的不同表中的数据? [英] 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屋！