This data is about 10 GB, available on S3, and is really easy to query from duckdb. If you have fast internet you can even do a trial query without downloading anything:
SELECT
COUNT(*)
FROM
's3://fsq-os-places-us-east-1/release/dt=2024-11-19/places/parquet/places-*.snappy.parquet'
WHERE
name = 'Wendy''s';
This takes less than a minute with fast home internet.
Curious what parts of the planet have the highest density of fast food restaurants? Download the files so you have local copies, then make sure you have the H3 extension installed:
INSTALL h3 FROM community; LOAD h3;
Now:
SELECT
printf('%x', cell) as h3,
COUNT(*) AS count
FROM (
SELECT
name,
H3_LATLNG_TO_CELL(latitude, longitude, 4) AS cell
FROM 'places-*.snappy.parquet'
WHERE array_contains(fsq_category_labels, 'Dining and Drinking > Restaurant > Fast Food Restaurant')
)
GROUP BY
cell
ORDER BY
count DESC;
The top H3 cell will have ID 841ec91ffffffff; Go to https://observablehq.com/@nrabinowitz/h3-index-inspector and paste it in and you'll see that it's… Istanbul! Write another query to find out what the most common fast food places are in Istanbul… (Burger King and McDonald's, and it's not even close).
Install the spatial extension and you can find all venues within 100 meters of you. Find all the Taco Bell/Pizza Hut combos. Go crazy.
Well now I feel dumb:P In my defense, that requires finding that option (the readme doesn't discuss options) and knowing that .srt/.vtt are subtitle formats. This is great, thanks!
One reason I put charts for both absolute numbers of flights and percentage change is to help understand the larger context. Those relative upticks happened just before CrowdStrike hit, so around midnight Eastern time, when traffic was already very low. So a 25% increase might be 12 extra flights taking off in the U.S. in an hour. There's plenty of noise, for sure, and looking at absolute numbers and percent change together can help give you a sense of what was going on. Looking at two days worth of data is probably enough to give you the main themes of the CrowdStrike impact, but not enough to explain every variation.
It's like writing webgl shaders for ADS-B. In SQL.
Incredible work, and I hope this kicks off a lot of innovation in the world of aircraft traffic analysis & visualization, which I think has been kind of stuck in a rut for a while.
No one said complying was too hard. This is one of the coolest things anyone's done with your data, they made a good faith effort to comply, and you've done nothing but act like a jerk and project your issues all over this thread. If I ran an ADS-B aggregator that was based on the same software as a half-dozen others, which hadn't seen any significant innovation in years, sitting on tons of data with absolutely amazing potential, I might consider praising the person who just revolutionized the world of ADS-B analysis & visualization, and possibly begging them to help me. And "P.S., could you throw in [specific attribution text]."
Fighters often don't have ADS-B, but there are lots of other military aircraft that routinely use it (transports, etc.). Go to https://globe.adsbexchange.com and press U to show only military.
My town, north of Dayton, OH was recently overflown by a group of fighters (I believe F-18's). I was surprised that they didn't appear on the ADS-B tracker site I ran inside and looked at. I guess it makes sense.
I'm just a couple of miles from DAY and see a lot of traffic every day. It would be interesting to know how military aircraft like that coordinate with civilian air traffic control.
(I like to listen to Dayton approach while watching an ADS-B tracker site. I enjoy seeing the traffic fly over my neighborhood. I find it oddly amusing to look up at a plane I just heard getting clearance to land knowing that I just heard the voice of somebody up there thru my speakers. I don't know why it's so pleasing...)
Just speaking from experience, in Colorado no military has it on except for the trainers. Having a chinook pass under you and be totally invisible on the iPad is a weird feeling.
I made a version of whatsoverhead.com but for satellites: You could ask it, "Hey, Siri, what's overhead…in space?" and it would tell you what the nearest satellite to you was. It was an attempt to help situate yourself in the invisible world of spacecraft flying overhead all the time. The thing is, 70-90% of the time the answer is "STARLINK-1234". It was shocking to me. The app achieved the goal I intended, and the answer was a surprise–it was a more visceral way of understanding the fact that there are a lot of Starlink satellites, for sure.
Part of that is because starlink shells are lower down than many other satellites. You are never going to have a GPS satellite or a GEO stationary one be the nearest to you because there is always going to be lower satellites nearer.
Yes, I eventually had it give answers for each of LEO, MEO, and GEO just because it got extremely boring having "STARLINK" almost always be the answer.
Curious what parts of the planet have the highest density of fast food restaurants? Download the files so you have local copies, then make sure you have the H3 extension installed:
Now: The top H3 cell will have ID 841ec91ffffffff; Go to https://observablehq.com/@nrabinowitz/h3-index-inspector and paste it in and you'll see that it's… Istanbul! Write another query to find out what the most common fast food places are in Istanbul… (Burger King and McDonald's, and it's not even close).Install the spatial extension and you can find all venues within 100 meters of you. Find all the Taco Bell/Pizza Hut combos. Go crazy.
[Also see https://simonwillison.net/2024/Nov/20/foursquare-open-source...]
reply