Thanks! That worked :) I'm still working on removing the requirement of having the search rooted at a specific vertex. I'd love to change that subselect to just
SELECT FROM GeoGlyphWKT but that won't work... is there any way to have a query basically iterate through the results of a subselect? On Tuesday, December 13, 2016 at 10:47:34 PM UTC-7, Ivan Mainetti wrote: > > Hi William, > > apparently the exception is thrown while calculation of distance in the > WHERE condition. > (you can verify by removing it: > 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 Name <> $Source.Name > ORDER BY Distance > ). > > I've tried using something like WHERE Distance <2000 AND Name <> > $Source.Name but apparently doesn't work. > So here's my solution (I do not see any drop in performance): > SELECT FROM( > 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 Name <> $Source.Name > ORDER BY Distance) WHERE Distance < 2000 > > > Let me know if this works for you. > > bye, > Ivan > > > Il giorno martedì 13 dicembre 2016 19:37:11 UTC+1, William ha scritto: >> >> 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.