在sql查询中计算距离(毕达哥拉斯)和运行计数

2022-01-14 00:00:00 ms-access sql database geolocation mysql

我正在尝试在 SQL 中构建一个相当复杂的查询，作为初学者，我非常感谢构建它的一些帮助.

I am trying to build a rather complicated query in SQL, and being a beginner i would immensely appreciate some help to build it.

我正在努力实现以下目标:

I am trying to achieve the following:

1/使用毕达哥拉斯计算使用笛卡尔纬度和经度坐标的 target_postcodes 表中的邮政编码(例如 E1 1AA)和 population_postcodes 表中的所有邮政编码之间的距离:

1/ Calculate the distance between a postcode in the target_postcodes table - say E1 1AA - and all the postcodes in the the population_postcodes table using Cartesian latitude and longitude coordinates using Pythagoras:

SQRT( POW(MY_Y_AXIS - Y_AXIS, 2) + POW(MY_X_AXIS-X_AXIS, 2) )

2/使用这些 distance 值创建一个新列，

2/ Create a new column with those distance values,

not sure how to do that step

2-bis/根据我们得到的distance值对population_postcodes中的邮编进行排序，

2-bis/ Sort postcodes in the population_postcodes by the distance value we obtained,

not sure how to do that step

3/从最近的邮政编码开始，将人口列中的值添加到 E1 1AA 的 UNTIL running_count > Number_of_beds 列中,

3/ Beginning with the closest postcode, add the value in the population column to a running_count column UNTIL running_count > Number_of_beds of E1 1AA,

提出的运行计数查询 - 但缺少上述中断条件:

proposed query for running count - but missing the above breaking condition:

SELECT distance, Population,
 (SELECT sum(population_postcodes.Population)) AS Total

FROM population_postcodes
WHERE population_postcodes.distance <= T1.distance) AS Total

FROM population_postcodes AS T1

4/创建一个新表，其中包含邮政编码 E1 1AA (target_postcode) 和添加到我们运行计数中的最后一个邮政编码的距离值.

4/ Create a new table that contains the postcode E1 1AA (target_postcode) and the distance value of the last postcode added to our running count.

最后，我需要在整个 target_postcodes 表中循环这个查询.

Finally, i would need to loop this query over the whole target_postcodes table.

非常感谢您帮助新手！

推荐答案

1., 2. 要将表放在一起并在它们之间执行操作，您需要使用Joinhttp://dev.mysql.com/doc/refman/5.0/en/join.html否则你的公式是正确的.要将其创建为查询中的列，只需将其写入投影(选择)部分.示例:

1., 2. To bring tables together and perform operations between them, you need to use Join http://dev.mysql.com/doc/refman/5.0/en/join.html otherwise your formula is correct. To create it as a column in your query, just write it in the projection(select) part. Example:

select 
population_postcodes.*, 
target_postcodes.*, 
SQRT( POW(population_postcodes.longitude- target_postcodes.longitude, 2) + POW(population_postcodes.latitude-target_postcodes.latitude, 2) ) as distance
from population_postcodes JOIN target_postcodes

第 2 点之二.以按 column_name asc/desc 排序结束http://dev.mysql.com/doc/refman/5.0/zh/sorting-rows.html

points 2 bis. End with Order by column_name asc/desc http://dev.mysql.com/doc/refman/5.0/en/sorting-rows.html

第 3 点.将所有内容编写为 子查询，并在顶部查询中仅选择您需要的内容.还要看看 HAVINGhttp://dev.mysql.com/doc/refman/5.0/en/subqueries.htmlhttp://dev.mysql.com/doc/refman/5.0/en/group-by-extensions.html

point 3. Write everything as a sub-query, and select only what you need in the top query. Also look at HAVING http://dev.mysql.com/doc/refman/5.0/en/subqueries.html http://dev.mysql.com/doc/refman/5.0/en/group-by-extensions.html

第 4 点.查看创建表格的方法并应用您所接近的内容

point 4. look at ways to create tables and apply what you nearned 

create table mytablename
select ... my projection columns
from ...

http://dev.mysql.com/doc/refman/5.1/en/create-table.html

相关文章