在OrientDB中进行子选择的植根空间查询 [英] rooted spatial query with sub-select in OrientDB
问题描述
我正在尝试将一个查询组合在一起,以查找距图形中某个节点2公里以内的节点.假设我有一个数据集,该数据集标记了纳斯卡线条中的一些地理字形:
I am trying to put together a query to find nodes that are within 2km of a node in my graph. Say I have a dataset that marks some geoglyphs from the nazca lines:
Name,Latitude,Longitude
Hummingbird,-14.692131,-75.148892
Monkey,-14.706940,-75.138532
Condor,-14.697444,-75.126208
Spider,-14.694145,-75.122381
Spiral,-14.688277,-75.122746
Hands,-14.694459,-75.113881
Tree,-14.693898,-75.114520
Astronaut,-14.745222,-75.079755
Dog,-14.706401,-75.130788
Wing,-14.680309,-75.100385
Parrot,-14.689463,-75.107498
我有一个使用以下方法创建的空间索引:
I have a spatial index created using:
CREATE INDEX GeoGlyph.index.Location
ON GeoGlyph(Latitude,Longitude) SPATIAL ENGINE LUCENE
现在,我想在"Hands"字形的2公里内找到节点,我可以通过手动输入纬度/经度坐标来输入此查询:
Now, I want to find the nodes within 2km of the "Hands" glyph, I can enter in this query by manually putting in the Lat/Long coordinates:
SELECT Name, Latitude, Longitude, $distance AS Distance
FROM GeoGlyph
WHERE [Latitude,Longitude,$spatial]
NEAR [-14.694459,-75.113884,{"maxDistance":2}]
ORDER BY Distance
我得到结果:
+----+------+----------+----------+--------------------+
|# |Name |Latitude |Longitude |Distance |
+----+------+----------+----------+--------------------+
|0 |Hands |-14.694459|-75.113884|5.230883384236603E-6|
|1 |Tree |-14.693897|-75.11446 |0.08836486627516459 |
|2 |Spider|-14.694363|-75.12358 |1.0442063409276094 |
|3 |Spiral|-14.688309|-75.12276 |1.1754176535538237 |
|4 |Condor|-14.698346|-75.128334|1.6149944044266815 |
+----+------+----------+----------+--------------------+
到目前为止,很好.
由于键入坐标有些麻烦,因此,我宁愿使用名称"字段"Hands"来查找2公里内的字形.
Since it's a bit of a pain to type in the coordinates, I'd much rather just look for glyphs within 2km using the Name field "Hands".
这是我目前遇到的问题.我认为我应该可以使用 LET块得到我想要的东西...但是到目前为止我没有尝试过:
This is where I'm currently stuck. I think I should be able to use LET block to get what I want... but what I've tried so far isn't working:
SELECT *,$distance AS Distance
FROM GeoGlyph
LET $temp = (SELECT * FROM GeoGlyph WHERE Name = "Hands")
WHERE [Latitude,Longitude,$spatial]
NEAR [$temp.Latitude, $temp.Longitude,{"maxDistance":2}]
ORDER BY Distance
有什么建议吗?
推荐答案
我想出了一种方法...如果可以安全地假设字段GeoGlyph.Name
是唯一的,则可以在NEAR子句:
I figured out the way to do it... if it's safe to assume that the field GeoGlyph.Name
is unique, I can use first()
in the NEAR clause:
SELECT *,$distance AS Distance
FROM GeoGlyph
LET $temp = (SELECT * FROM GeoGlyph WHERE Name = "Hands")
WHERE [Latitude,Longitude,$spatial]
NEAR [first($temp).Latitude, first($temp).Longitude,{"maxDistance":2}]
ORDER BY Distance
这似乎可以解决问题.
orientdb {db=nazca.orientdb}> SELECT *,$distance AS Distance FROM GeoGlyph LET $temp = (SELECT * FROM GeoGlyph WHERE Name = "Hands") WHERE [Latitude,Longitude,$spatial] NEAR [first($temp).Latitude, first($temp).Longitude,{"maxDistance":2}] ORDER BY Distance
+----+-----+--------+----------+----------+------+-------------------+
|# |@RID |@CLASS |Latitude |Longitude |Name |Distance |
+----+-----+--------+----------+----------+------+-------------------+
|0 |#25:5|GeoGlyph|-14.694459|-75.113884|Hands |0.0 |
|1 |#25:6|GeoGlyph|-14.693897|-75.11446 |Tree |0.08836394983673491|
|2 |#25:3|GeoGlyph|-14.694363|-75.12358 |Spider|1.0442092937404572 |
|3 |#25:4|GeoGlyph|-14.688309|-75.12276 |Spiral|1.1754175925032648 |
|4 |#25:2|GeoGlyph|-14.698346|-75.128334|Condor|1.614998440581846 |
+----+-----+--------+----------+----------+------+-------------------+
如果我不能依靠Name
字段的唯一性,我仍然不确定该怎么做.例如,如果我想计算彼此之间2公里之内的所有成对的地理标志,那么...
I'm still not sure exactly how I'd do this if I couldn't rely on uniqueness of the Name
field though. For example if I wanted to compute all the pairs of geoglyphs within 2km of each other...
这篇关于在OrientDB中进行子选择的植根空间查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!