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 orient-database+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.