Efficient queries

7 months 4 weeks ago #1975 by philhill
Efficient queries was created by philhill
Hey!
I am trying to map flights of certain aircraft and want to analyze if they changed flight routes over the years! As this requires a lot of flight data to be analyzed but at the same time i don't need 100 adsb logs per minute, i was wondering if there is a possibility to decrease the entries downloaded from the historical database. My first intuition was to group the entries by minutes and then only select one per minute, but that didn't work out. another way i thought of was choosing only random entries, but i don't know how to make that work (Im working in python with pyopensky). Any tips and tricks are greatly appreciated!

greetings,
Philip

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

7 months 3 weeks ago #1976 by strohmeier
Replied by strohmeier on topic Efficient queries
In terms of queries, have you looked at Section 5 Query 1? opensky-network.org/data/impala

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

7 months 1 week ago #1992 by philhill
Replied by philhill on topic Efficient queries
Yes I did indeed, but i cant seem to figure out the right query if I am interested in a specific aircraft instead of a specific time!

Here is the query I used:

SELECT * FROM state_vectors_data4 v JOIN (SELECT QUOTIENT(time, 60) AS minute, MAX(time) AS recent, icao24 FROM state_vectors_data4 WHERE icao24='3c66a9' GROUP BY minute) AS m ON v.icao24=m.icao24 AND v.time=m.recent WHERE v.icao24='3c66a9';

it always returns no record found.

Sorry for the late reply and thanks for the help,

greetings
Philip

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!