需要专家的帮助来解决空间数据查询中的细微变化 [英] Need expert's help to solve minor change in spatial data query
问题描述
我有以下查询可访问给定纬度附近的最近位置. 我关注了 Ollie先生的博客最近的位置查找器MySQL 使用Haversine公式查找给定纬度附近的最近位置.
但是由于缺乏空间数据查询的丰富知识，我无法正确执行它，因此寻求专家的意见来解决此问题.
这是我的查询
SELECT z.id,
p.distance_unit
* DEGREES(ACOS(COS(RADIANS(p.latpoint))
* COS(RADIANS(z.(x(property))))
* COS(RADIANS(p.longpoint) - RADIANS(z.(y(property))))
+ SIN(RADIANS(p.latpoint))
* SIN(RADIANS(z.(x(property)))))) AS distance_in_km
FROM mytable AS z
JOIN ( /* these are the query parameters */
SELECT 12.00 AS latpoint, 77.00 AS longpoint,
20.0 AS radius, 111.045 AS distance_unit
) AS p
WHERE z.(x(property))
BETWEEN p.latpoint - (p.radius / p.distance_unit)
AND p.latpoint + (p.radius / p.distance_unit)
AND z.(y(property)
BETWEEN p.longpoint - (p.radius / (p.distance_unit * COS(RADIANS(p.latpoint))))
AND p.longpoint + (p.radius / (p.distance_unit * COS(RADIANS(p.latpoint))))
ORDER BY distance_in_km
LIMIT 15;
当我运行此查询时，我收到错误消息
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(x(property)))) * COS(RADIANS(p.longpoint) - RADIANS(z.(y(geo' at line 1
我也尝试过z.(GeomFromText(x.property)))
这是我的表 desc
+-----------+----------------+
| Field | Type |
+-----------+----------------+
| id | Int(10) |
| property | geometry |
+-----------+----------------+
select x(property) from mytable; //gives me lat
select y(property) from mytable; //gives me lan
我要去哪里错了? 这是实现这一目标的方法吗?
请提出建议.
在我看来，您假设一旦在查询中选择了z.id，就可以直接访问x(property)， y(property)
(除了-这些名称中确实有括号吗?)
在我看来，您应该替换类似的东西
* COS(RADIANS(z.(x(property))))
类似
* COS(RADIANS( select x(property) from mytable where id = z.id ))
但是，进一步考虑之后，我认为您的mytable没有所需的结构.通过查看链接，我相信您的mytable应该具有类似于以下的结构:
+-----------+----------------+
| Field | Type |
+-----------+----------------+
| id | Int(10) |
| latitude | Float |
| longitude | Float |
+-----------+----------------+
这样您就可以做类似的事情
* COS(RADIANS(z.latitude))
注意
以上内容基于我不了解MySQL支持空间数据类型(我不知道如何使用)
更新
我只是进行了一次谷歌搜索来了解空间类型，并发现了这一点:
如何您如何使用MySQL空间查询来查找X半径内的所有记录? [关闭]
这表明您不能对mysql中的空间数据类型做您想做的事情.因此，这使您回到使用非最佳方式在mutable
但是，在重新阅读该链接时，对答案的注释表明您现在可以使用空间数据类型. (我告诉过您这里没有任何线索)，这意味着用ST_Distance(g1,g2)之类的东西替换查询代码，这实际上意味着完全重写该示例.
换一种说法
您给出的示例假设空间数据类型和测试 几何在MySQL中不存在.但是，既然它们确实存在，它们 使这组示例代码无关紧要，并且您将 如果您尝试将两种形式的分析结合起来，就会感到非常痛苦.
更新2
您可以遵循以下三种路径:
-
否认MySQL中存在空间数据类型，并使用具有显式列
lat和long的表，并使用该博客最初编写的示例代码. -
拥抱MySQL空间数据类型(疣和所有)，看看类似答案的东西 https://stackoverflow.com/a/21231960/31326 似乎可以直接使用空间数据类型来完成您想要的事情，但是正如该答案所指出的那样，有一些警告.
-
使用空间类型保存数据，并使用
pre-query提取lat和long，然后再将其传递到原始示例代码中.
i have the following query to access the nearest locations around the given lat-lon. I followed Mr.Ollie's blog Nearest-location finder for MySQL to find nearest locations around given lat-long using haversine formula.
But due to lack of much knowledge in spatial data query i failed to execute it properly, so looking for an expert's advice to solve this.
Here is my query
SELECT z.id,
p.distance_unit
* DEGREES(ACOS(COS(RADIANS(p.latpoint))
* COS(RADIANS(z.(x(property))))
* COS(RADIANS(p.longpoint) - RADIANS(z.(y(property))))
+ SIN(RADIANS(p.latpoint))
* SIN(RADIANS(z.(x(property)))))) AS distance_in_km
FROM mytable AS z
JOIN ( /* these are the query parameters */
SELECT 12.00 AS latpoint, 77.00 AS longpoint,
20.0 AS radius, 111.045 AS distance_unit
) AS p
WHERE z.(x(property))
BETWEEN p.latpoint - (p.radius / p.distance_unit)
AND p.latpoint + (p.radius / p.distance_unit)
AND z.(y(property)
BETWEEN p.longpoint - (p.radius / (p.distance_unit * COS(RADIANS(p.latpoint))))
AND p.longpoint + (p.radius / (p.distance_unit * COS(RADIANS(p.latpoint))))
ORDER BY distance_in_km
LIMIT 15;
when i run this query i'm getting error as
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(x(property)))) * COS(RADIANS(p.longpoint) - RADIANS(z.(y(geo' at line 1
i also tried z.(GeomFromText(x.property)))
this is my table desc
+-----------+----------------+
| Field | Type |
+-----------+----------------+
| id | Int(10) |
| property | geometry |
+-----------+----------------+
select x(property) from mytable; //gives me lat
select y(property) from mytable; //gives me lan
Where am i going wrong? is this the way to achieve this.?
Please suggest.
It seems to me that you are assuming that once you have selected z.id in the query, that this gives you you direct access to the x(property) and y(property)
(Aside - do those names really have parentheses in them?)
So to me it looks like you should replace things like
* COS(RADIANS(z.(x(property))))
with something like
* COS(RADIANS( select x(property) from mytable where id = z.id ))
However on further thinking about it, I think that your mytable doesn't have the required structure. From looking at the link, I believe that your mytable should have a structure more like:
+-----------+----------------+
| Field | Type |
+-----------+----------------+
| id | Int(10) |
| latitude | Float |
| longitude | Float |
+-----------+----------------+
So that you can do something like
* COS(RADIANS(z.latitude))
NOTE
The above was based on me not understanding that MySQL supports spatial data types (for which I have no idea how to use)
Update
I just did some googling to understand the spatial types and found this:
How do you use MySQL spatial queries to find all records in X radius? [closed]
which suggests that you can't do what you want to do with spatial data types in mysql. Which thus brings you back to using a non-optimal way of storing data in mutable
However in re-reading that link, the comments to the answer suggest that you may now be able to use spatial data types. (I told you I didn't have a clue here) This would mean replacing the query code with things like ST_Distance(g1,g2), which effectively means totally rewriting the example.
To put it another way
The example you gave presumes that spatial data types and testing of geometries do not exist in MySQL. But now that they do exist, they make this set of example code irrelevant, and that you are in for a world of hurt if you try and combine the two forms of analysis.
update 2
There are three paths you can follow:
Deny that spatial data types exists in MySQL and use a table that has explicit columns for
latandlong, and use the sample code as originally written on that blog.Embrace the MySQL spatial data types (warts and all) and take a look at things like this answer https://stackoverflow.com/a/21231960/31326 that seem to do what you want directly with spatial data types, but as noted in that answer there are some caveats.
Use a spatial type to hold your data, and use a
pre-queryto extractlatandlongbefore passing it into the original sample code.
这篇关于需要专家的帮助来解决空间数据查询中的细微变化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
