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