How do I get just the record id as a string from a query?

36 views
Skip to first unread message

William

unread,
Dec 6, 2016, 12:36:35 PM12/6/16
to OrientDB
I have a simple dataset that I'm playing with to test out the geospatial capabilities of the orientdb-spatial module.

The input is just a simple table of a few of the nazca line geoglyphs.

Name,Latitude,Longitude
Hummingbird,-14.692131,-75.148892
Monkey,-14.7067274,-75.1475391
Condor,-14.6983457,-75.1283374
Spider,-14.694363,-75.1235815
Spiral,-14.688309,-75.122757
Hands,-14.694459,-75.113881
Tree,-14.693897,-75.114467
Astronaut,-14.745222,-75.079755
Dog,-14.706401,-75.130788

Ultimately, I'd like to put together a query that can generate edges in the graph from nodes to the neighbors that are within N km.  For a threshold of 2km, I'd end up with edges that are something like this:


but it doesn't appear that I could do this with some kind of CREATE EDGES query directly, so I'm left with native Java or a Javascript server-side function.  I'm working on a function to do this, so the first part is to generate a table containing the SourceNode, DestNode, and Distance which I can then use to create the edges.

Thus far, I've only been able to get a query like this to work, which can only find my features that are within some distance of a specific node.

SELECT $temp.@rid as SourceRID,
       $temp
.Name AS SourceName,
       
@rid AS DestRID,
       
Name AS DestName,
       $distance
.format("%.4f") AS Distance
FROM
GeoGlyph
LET $temp
= first((SELECT * FROM GeoGlyph WHERE Name = "<GEOGLYPH NAME>"))
WHERE
[Latitude,Longitude,$spatial] NEAR [$temp.Latitude, $temp.Longitude,{"maxDistance":2}]
  AND
Name != "<GEOGLYPH NAME>"
ORDER BY
Distance

I can run this inside a loop over all the nodes in my graph to build a list of all the pairs, which can then be used to generate my edges.  The javascript function that I'm mucking around with to explore this looks like the following:

var g = orient.getGraph();

// set max distance threshold
var distance = 2;

// get the list of all GeoGlyph vertices
var v = g.command('sql', 'SELECT FROM GeoGlyph');

var edges = [];
var seen  = {};

for(i=0; i<v.length; i++)
{
 
var name  = v[i].getRecord().field('Name');
 
var srcid = v[i].getRecord().field('@rid');
  seen
[name] = true;
   
  query
= 'SELECT $temp.Name AS SourceName, @rid AS DestRID, Name AS DestName, $distance.format("%.4f") AS Distance FROM GeoGlyph LET $temp = first((SELECT * FROM GeoGlyph WHERE Name = "'+name+'")) WHERE [Latitude,Longitude,$spatial] NEAR [$temp.Latitude, $temp.Longitude,{"maxDistance":'+distance+'}] AND Name != "'+name+'" ORDER BY Distance';

 
// Get a list of geoglyphs that are within 2km of the current geoglyph.  
 
var p = g.command('sql', query);
 
 
for(j=0; j<p.length; j++)
 
{      
   
var destName = p[j].getRecord().field('DestName');    
   
if(destName in seen)
   
{
     
continue;
   
}
   
var dstid = p[j].getRecord().field('DestRID');
   
var dist  = p[j].getRecord().field('Distance');    
   
var row   = [srcid, dstid, name, destName, dist];            
    edges
.push(row);
 
}  
}

// Loop over the data and print out the @rid field for source and dest..
for(i=0; i<edges.length; i++)
{
 
print(edges[i][0] +"     "+ edges[i][1]);
}

The output I get from the print statement in the final loop is this:

#25:0     GeoGlyph#25:1{Latitude:-14.706727,Longitude:-75.14754,Name:Monkey} v1
#25:1     GeoGlyph#25:8{Latitude:-14.706401,Longitude:-75.13079,Name:Dog} v1
#25:2     GeoGlyph#25:3{Latitude:-14.694363,Longitude:-75.12358,Name:Spider} v1
...

So, I can get the source RID just fine, but the dest RID isn't just a "#nn:nn" style format... it's the document.  

How can I modify the SELECT statement in my query to get the RID as just a string?

alessand...@gmail.com

unread,
Dec 7, 2016, 6:47:39 AM12/7/16
to OrientDB
Hi ,

have you a small dataset to reproduce quickly the problem ?

Alessandro

William

unread,
Dec 7, 2016, 12:26:58 PM12/7/16
to OrientDB
I think I got it... I had to modify the line:

var dstid = p[j].getRecord().field('DestRID');

to this:

var dstid = p[j].getRecord().field('DestRID').getRecord().field('@rid');

Which got it to work.

I'm still curious to know if there's a way in the query itself to just get the record id as a text string rather than the full entry. 

-William
Reply all
Reply to author
Forward
0 new messages