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 [email protected].
For more options, visit https://groups.google.com/d/optout.
