- Posts: 2
- Thank you received: 0
Efficient queries
2 years 5 months 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
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
- strohmeier
- Offline
- Administrator
-
Less
More
- Posts: 393
- Thank you received: 93
2 years 5 months 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
2 years 4 months 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
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
