ST_Distance_Sphere rooted search question

[William]

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 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 - 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 - For help with loading the WKT into a graph using the oetl.sh tool.
   

[Ivan Mainetti]

SELECT * FROM GeoGlyphWKT

LET Target = (SELECT Location FROM GeoGlyphWKT WHERE Name="Hands")

WHERE ST_Distance_Sphere(Location, $Target.Location[0]) <= 2000

[William]

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!

[Ivan Mainetti]

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

[William]

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?