AT
Apache TinkerPop•2w ago
danielcraig23

How can I use a subquery to translate airport code DAL into icao airport code KDAL, w air-routes?

I've loaded the air-routes data set, and I've added an aircraft like so: g.addV("aircraft").property("aircraftLocation", "DAL") I want to write a query that reports the location of all my aircrafts like so: [tailNumber:N12345,aircraftLocationIcao:KDAL] To accomplish this, I need to translate the IATA aircraft location "DAL" into the ICAO aircraft location "KDAL". So the .project() step is not straightforward. 
#This query doesn't work because it yields the three letter IATA airport code instead of the four letter ICAO airport code
gremlin> g.V().hasLabel("aircraft").project("tailNumber", "aircraftLocationIcao").by("tailNumber").by("aircraftLocation")
==>[tailNumber:N12345,aircraftLocationIcao:DAL]
#This query doesn't work because it yields the three letter IATA airport code instead of the four letter ICAO airport code
gremlin> g.V().hasLabel("aircraft").project("tailNumber", "aircraftLocationIcao").by("tailNumber").by("aircraftLocation")
==>[tailNumber:N12345,aircraftLocationIcao:DAL]
How can I accomplish this? Details of what I've already tried in thread: 🧵
2 Replies
danielcraig23
danielcraig23OP•2w ago
I intend to use the station properties named code and icao to do this translation: 
gremlin> g.V().hasLabel("airport").elementMap("code", "icao").limit(1)
==>[id:1,label:airport,code:ATL,icao:KATL]
gremlin> g.V().hasLabel("airport").elementMap("code", "icao").limit(1)
==>[id:1,label:airport,code:ATL,icao:KATL]
So I need to do a cross join on the aircrafts and the airports, and then pick a property from the right "table" SELECT ac.tailNumber, ap.icao FROM aircrafts ac CROSS JOIN airports ap WHERE ac.tailLocation = ap.code ^ this is a SQL pseudocode for what I'm trying to do I have this query, which uses double .V(), is this best practices? 
gremlin> g.V().hasLabel("aircraft").as("ac").V().hasLabel("airport").as("ap").where("ac", P.eq("ap")).by("aircraftLocation").by("code").select("ac", "ap").by("tailNumber").by("icao")
==>[ac:N12345,ap:KDAL]
gremlin> g.V().hasLabel("aircraft").as("ac").V().hasLabel("airport").as("ap").where("ac", P.eq("ap")).by("aircraftLocation").by("code").select("ac", "ap").by("tailNumber").by("icao")
==>[ac:N12345,ap:KDAL]
I'm using Neptune, will this query be efficient on Neptune? For background, I need this to be fast with around 850 aircraft and multiple thousands of airports
ColeGreer
ColeGreer•7d ago
Hi @danielcraig23, I might try to search for the the specific airports with the necessary iata->icao mapping instead of the "cross join then filter" approach. This is the traversal I came up with: 
g.V().
  hasLabel("aircraft").as("ac").
  values("aircraftLocation").as("iata").
  select("ac").
  project("tailNumber", "aircraftLocationIcao").
    by("tailNumber").
    by(
      V().
      hasLabel("airport").
      where(values("code").where(eq('iata'))).
      values("icao"))
g.V().
  hasLabel("aircraft").as("ac").
  values("aircraftLocation").as("iata").
  select("ac").
  project("tailNumber", "aircraftLocationIcao").
    by("tailNumber").
    by(
      V().
      hasLabel("airport").
      where(values("code").where(eq('iata'))).
      values("icao"))
In my very small scale testing I'm seeing this run about an order of magnitude faster than the "cross join then filter" approach.

Did you find this page helpful?