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

[William]

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]

Hi ,

have you a small dataset to reproduce quickly the problem ?

Alessandro

[William]

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