AT
Apache TinkerPop•3mo 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: 🧵
Solution:
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")....
Jump to solution
3 Replies
danielcraig23
danielcraig23OP•3mo 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
Solution
ColeGreer
ColeGreer•3mo 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.
danielcraig23
danielcraig23OP•3mo ago
Thank you, I'll try this!!

Did you find this page helpful?