Newbie Seeking Advice on Project

2 years 5 months ago #1751 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!


Please Log in or Create an account to join the conversation.

2 years 5 months ago #1752 by strohmeier
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:

The geofencing isn't difficult with those tools but also on the shell directly. See useful query examples here:

To get info on the planes, you can use 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.

Powered by Kunena Forum
This website uses cookies to offer you the best experience of our services. By using this website you agree to our privacy policy!