- Posts: 2
- Thank you received: 0
Newbie Seeking Advice on Project
2 years 2 months ago #1751
by jkaplan
Newbie Seeking Advice on Project was created by jkaplan
Hi all - I'm new to Opensky and am just getting my feet wet with issuing SQL commands through the Impala shell interface in a Terminal window on my Mac. I'd appreciate any advice on how to tackle my project - which tables, tools, etc. would be best for me to use, and any prior work that might be relevant so I don't reinvent the wheel...
My initial goal: To get a list of aircraft performing touch and gos (and count of touch and gos) at a particular local airport (for instance KHAF) in a given time frame (i.e. a day or month). The final work product could look something like this:
Tail number; timestamp of first touch and go; timestamp of last touch and go; # of touch and gos performed.
To get to this, the "flights_data4" table doesn't seem to do the trick, so I'm looking at "state_vectors_data4". My thinking is to:
* Extract all records within 3 miles of KHAF under 700 ft altitude and a non-zero vertrate during the time frame. (Pattern altitude is 1000 ft.)
* Import to Excel. Sort by timestamp within icao24 (aircraft identifier).
* Look for sequential (in time) pairs of records for a given aircraft identifier with a negative vertrate followed by a positive vertrate, and timestamp no more than 2 minutes apart.
My theory is that each such pair of records represents a touch and go.
Does this sound like a viable plan? Are there higher level tools or interfaces that would be easier to deal with? (I'm familiar with Python.)
My immediate roadblock is I haven't figured out how to do the geofencing - i.e. data records within 3 miles radius (or a square surrounding) a given airport. A search of the forum on "geofence" didn't turn up anything.
My second roadblock is how to convert icao24 into a tail number or some other useful identifier.
Any advice on this would be greatly appreciated!
Jerry
My initial goal: To get a list of aircraft performing touch and gos (and count of touch and gos) at a particular local airport (for instance KHAF) in a given time frame (i.e. a day or month). The final work product could look something like this:
Tail number; timestamp of first touch and go; timestamp of last touch and go; # of touch and gos performed.
To get to this, the "flights_data4" table doesn't seem to do the trick, so I'm looking at "state_vectors_data4". My thinking is to:
* Extract all records within 3 miles of KHAF under 700 ft altitude and a non-zero vertrate during the time frame. (Pattern altitude is 1000 ft.)
* Import to Excel. Sort by timestamp within icao24 (aircraft identifier).
* Look for sequential (in time) pairs of records for a given aircraft identifier with a negative vertrate followed by a positive vertrate, and timestamp no more than 2 minutes apart.
My theory is that each such pair of records represents a touch and go.
Does this sound like a viable plan? Are there higher level tools or interfaces that would be easier to deal with? (I'm familiar with Python.)
My immediate roadblock is I haven't figured out how to do the geofencing - i.e. data records within 3 miles radius (or a square surrounding) a given airport. A search of the forum on "geofence" didn't turn up anything.
My second roadblock is how to convert icao24 into a tail number or some other useful identifier.
Any advice on this would be greatly appreciated!
Jerry
Please Log in or Create an account to join the conversation.
- strohmeier
- Offline
- Administrator
-
Less
More
- Posts: 372
- Thank you received: 91
2 years 2 months ago #1752
by strohmeier
Replied by strohmeier on topic Newbie Seeking Advice on Project
Without being an expert for touch and goes, I would strongly recommend using one of the toolboxes available and a real programming language, typically Python or R, for your evaluation. They can extract flights and trajectories and visualize it. Check them out here: opensky-network.org/data/data-tools
The geofencing isn't difficult with those tools but also on the shell directly. See useful query examples here: opensky-network.org/data/impala
To get info on the planes, you can use opensky-network.org/aircraft-database or the callsigns which are also available.
The geofencing isn't difficult with those tools but also on the shell directly. See useful query examples here: opensky-network.org/data/impala
To get info on the planes, you can use opensky-network.org/aircraft-database or the callsigns which are also available.
The following user(s) said Thank You: jkaplan
Please Log in or Create an account to join the conversation.
