Ah, right. I forgot that the select statement returns a list.
I modified my query:
SELECT $Source.Name AS SourceName,
$Source.@rid AS SourceVertex,
Name AS DestName,
@rid AS DestVertex,
ST_Distance_Sphere(Location, $Source.Location) AS Distance
FROM GeoGlyphWKT
LET Source = first((SELECT FROM GeoGlyphWKT WHERE Name='Hands'))
WHERE ST_Distance_Sphere(Location, $Source.Location) < 2000
AND Name <> $Source.Name
ORDER BY Distance
Which returns this:
+----+----------+------------+--------+----------+------------------+
|# |SourceName|SourceVertex|DestName|DestVertex|Distance |
+----+----------+------------+--------+----------+------------------+
|0 |Hands |#25:5 |Tree |#25:6 |92.92139427816001 |
|1 |Hands |#25:5 |Parrot |#25:10 |884.1456401482661 |
|2 |Hands |#25:5 |Spider |#25:3 |915.9349947065938 |
|3 |Hands |#25:5 |Spiral |#25:4 |1176.78244992355 |
|4 |Hands |#25:5 |Condor |#25:2 |1368.3043683473186|
+----+----------+------------+--------+----------+------------------+
So that's good. I do get an error message on the console when I run this
through studio. The first line is:
$ANSI{green {db=nazca-wkt.orientdb}} Error on getting entry against Lucene
index
com.orientechnologies.orient.core.index.OIndexEngineException: Invalid
spatial query. Missing shape field {geo_filter=distance_sphere, shape=null,
distance=2000.0}
The result returns just fine though, so I don't think it's a problem... but
I'm curious to know if this is an issue?
This search is rooted though at just one node. How would I expand this to
get me all the Source->Dest pairs of vertices that are within 2km of each
other without having to specify a specific vertex?
Thanks!
On Monday, December 12, 2016 at 10:43:46 PM UTC-7, Ivan Mainetti wrote:
>
> SELECT * FROM GeoGlyphWKT
> LET Target = (SELECT Location FROM GeoGlyphWKT WHERE Name="Hands")
> WHERE ST_Distance_Sphere(Location, $Target.Location[0]) <= 2000
>
>
>
> Il giorno martedì 13 dicembre 2016 01:57:36 UTC+1, William ha scritto:
>>
>> I'm working on a progression where my ultimate goal is to be able to
>> generate connected components of vertices that are within some proximity of
>> each other. Ideally, I'd have two sets of vertices, A and B where vertices
>> va and vb come from A and B respectively. I'd like to generate edges
>> (va,vb) if dist(va,vb) is under some threshold.
>>
>> Building up to this, the first thing I'm trying to do is identify which
>> vertices are within 2km of some named vertex.
>>
>> The dataset that I put together for this has the locations of a few of
>> the nazca lines geoglyphs in Peru that I found using Google Earth:
>>
>> Name,Location
>> Hummingbird,"POINT(-75.148892 -14.692131)"
>> Monkey,"POINT(-75.138532 -14.706940)"
>> Condor,"POINT(-75.126208 -14.697444)"
>> Spider,"POINT(-75.122381 -14.694145)"
>> Spiral,"POINT(-75.122746 -14.688277)"
>> Hands,"POINT(-75.113881 -14.694459)"
>> Tree,"POINT(-75.114520 -14.693898)"
>> Astronaut,"POINT(-75.079755 -14.745222)"
>> Dog,"POINT(-75.130788 -14.706401)"
>> Wing,"POINT(-75.100385 -14.680309)"
>> Parrot,"POINT(-75.107498 -14.689463)"
>>
>> The vertex class GeoGlyphWKT is created in this manner:
>>
>> CREATE CLASS GeoGlyphWKT EXTENDS V CLUSTERS 1
>> CREATE PROPERTY GeoGlyphWKT.Name STRING
>> CREATE PROPERTY GeoGlyphWKT.Location EMBEDDED OPoint
>> CREATE PROPERTY GeoGlyphWKT.Tag EMBEDDEDSET STRING
>> CREATE INDEX GeoGlyphWKT.idxLocation ON GeoGlyphWKT (Location) SPATIAL
>> ENGINE LUCENE
>>
>> I've looked over the help documentation
>> <http://orientdb.com/docs/2.2.x/Spatial-Index.html> and have figured out
>> do a rooted search if I directly enter the coordinates. For example looking
>> for what's within 2km of the "Hands" geoglyph:
>>
>> SELECT *, $Distance AS Distance FROM GeoGlyphWKT
>> LET Distance = ST_Distance_Sphere(Location,
>> ST_GeomFromText('POINT(-75.148892
>> -14.692131)'))
>> WHERE $Distance <= 2000
>>
>>
>> +----+-----+-----------+-----------------------+-----------+------------------+
>> |# |@RID |@CLASS |Location |Name |Distance
>> |
>>
>> +----+-----+-----------+-----------------------+-----------+------------------+
>> |0 |#25:0|GeoGlyphWKT|OPoint{coordinates:[2]}|Hummingbird|0.0
>> |
>> |1 |#25:1|GeoGlyphWKT|OPoint{coordinates:[2]}|Monkey
>> |1990.4884419468854|
>>
>> +----+-----+-----------+-----------------------+-----------+------------------+
>>
>> ... but I'd rather just do a named search where GeoGlyphWKT.Name =
>> "Hands"...
>>
>> So, I tried to follow the template that I used on the old-style indexes
>> (see (1) in the stack overflow links at the bottom). I ended up with the
>> following query which doesn't work:
>>
>> SELECT FROM GeoGlyphWKT
>> LET Source = (SELECT FROM GeoGlyphWKT WHERE Name="Hands")
>> WHERE ST_Distance_Sphere(Location, $Source.Location) < 2000
>>
>>
>> Error: com.orientechnologies.orient.core.exception.
>> OCommandExecutionException: Error on execution of command: sql.select
>> FROM GeoGlyphWKT LET Source = (SELECT FROM GeoGlyphWKT WHERE Name="Hands"
>> ) WHERE ST_Distance_Sphere(Location, $Source.Location) < 2000
>> DB name="nazca-wkt.orientdb"
>>
>>
>> Error: java.lang.NullPointerException
>>
>> Any suggestions on how to do this search would be great!
>>
>> Thanks!
>> -William
>>
>>
>> *Related stack overflow questions*
>>
>> 1. rooted spatial query with sub-select in OrientDB
>>
>> <http://stackoverflow.com/questions/40942434/rooted-spatial-query-with-sub-select-in-orientdb>
>> -
>> uses the older spatial index that omits the use of WKT.
>> 2. Importing OPoint data into OrientDB 2.2.x using ETL from a CSV file
>>
>> <http://stackoverflow.com/questions/41027600/importing-opoint-data-into-orientdb-2-2-x-using-etl-from-a-csv-file>
>> -
>> For help with loading the WKT into a graph using the oetl.sh tool.
>>
>>
--
---
You received this message because you are subscribed to the Google Groups
"OrientDB" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
For more options, visit https://groups.google.com/d/optout.
