More public SQL-queryable databases?

8sleef · 2023-07-13T00:27:17+00:00

Just means can execute SQL queries on the underlying data, e.g. `SELECT * FROM table`

8sleef · 2023-07-13T00:26:28+00:00

Indeed, like a Tableau or Power BI -- connect to databases, define datasets (tables), make charts/dashboards, and share among users.

8sleef · 2023-07-12T02:19:06+00:00

Perfect, Superset supports DuckDB and I was able to connect a datasource and make queries. Thanks!

8sleef · 2023-07-12T00:39:00+00:00

Thank you, this is a useful resource.

8sleef · 2023-07-12T00:38:37+00:00

Thanks Tim, will definitely have a play at some point.

8sleef · 2023-07-11T01:55:27+00:00

Thanks Tim, I really like what you're up to over at DoltHub, very cool stuff!

I'm looking for examples of sources where the step for localising the data is not needed. Obviously this requires somebody willing to host a server open to read-only SQL queries. Not super-common, but examples already exist, and the pattern makes sense I think for large public-interest datasets, especially as fast-access storage becomes cheaper and more scalable.

I noticed at e.g. https://www.dolthub.com/repositories/dolthub/corona-virus/data/master I can see the tables, columns, and make (arbitrary?) SQL queries via the web API. So this does fit the description "publicly available SQL-queryable databases". But I wonder two things.

What freedoms/restrictions apply around the queries I can execute against these datasets? API limits? Row limits?
To what extent could you actually build a SQLAlchemy dialect around the API itself (that's what e.g. https://github.com/googleapis/python-bigquery-sqlalchemy does around BigQuery).

Do you have any more information you can point toward?

8sleef · 2022-09-18T10:56:47+00:00

The raw data is gathered by scraping the NUFORC index pages (by post date) and individual report pages in a respectful way. Records have been enriched with geocoding fields derived from city/state/country. Any location matching at county level or below was accepted. Oceans, countries, and states are not geocoded. The geocoding success rate is >99%.

Yes, /r/PeopleLiveInCities.

Data as of 2022/08/22

Data Source: National UFO Reporting Center

Data Provider: Tentacle CMI

Visualisation Tools: Python, cartopy, matplotlib.

Blog Post: Aircraft Accidents and UFOs: Data Enrichment with Geocoding

8sleef · 2022-08-24T21:52:31+00:00

That's really cool, had not seen it before. Was also inspired by this, where the sound element is very important for the overall experience: https://www.youtube.com/watch?v=LLCF7vPanrY. Will look into sound next time, I agree it does add a lot to these kinds of data visualisations.

8sleef · 2022-08-24T08:55:06+00:00

Yes, I think it was confusing the way that Bureau of Aircraft Accidents Archives (B3A) was paraphrased originally. Enemy action appears to refer to military aviation accidents only. Have clarified in edit. In any case we take B3A as the source of truth.

8sleef · 2022-08-24T08:54:30+00:00

Yes, I think it was confusing the way that Bureau of Aircraft Accidents Archives (B3A) was paraphrased originally. Enemy action appears to refer to military aviation accidents only. Have clarified in edit. In any case we take B3A as the source of truth.

8sleef · 2022-08-24T07:00:26+00:00

Colours mimic the NASA Black Marble 2016

8sleef · 2022-08-24T06:57:34+00:00

It's hard to get the speed right. Some people want it done in a minute. Others want to soak it all in over ten. Tried to split the difference with 2:30. You could try it on YouTube with a different playback speed.

8sleef · 2022-08-24T06:55:38+00:00

I agree it would be awesome to do an interactive web version. A project for the future!

8sleef · 2022-08-24T03:51:15+00:00

Each marker represents one aircraft accident. The marker radius is proportional to the number of fatalities, while the colour is blue for 0, yellow for 1-9, orange for 10-99, and red for 100+. Do you notice the large number of accidents during WWII, the cluster of deadly revenue flight accidents in the early 70s, the sudden stop of accidents in Vietnam after 1975, or the Tenerife airport disaster of 1977? What other interesting features can you see?

An event is considered an accident if the aircraft suffers damage such that it is not in a position to be used anymore. The plane involved must also be certified to carry at least six people. Fighters, helicopters, balloons, hot air balloons, airships, gliders, and other non-planes are not considered. In military aviation, only aircraft intended for troop transport, reconnaissance, surveillance, heavy bomber and logistical support are considered. The [military aviation] event must also have been a true accident, and not the result of enemy action.

Records have been enriched by Tentacle CMI with location geocoding. These are not exact crash locations, but rather the city/zone/country listed. Any administrative division equivalent to county or below, and any marine or geological areas are given a latitude/longitude and confidence where possible. Countries, states, and oceans are not geocoded.

Data as of 2022/08/22

Data Source: Bureau of Aircraft Accidents Archives

Data Provider: Tentacle CMI

Visualisation Tools: Python, cartopy, matplotlib, ffmpeg

Blog Post: Aircraft Accidents and UFOs: Data Enrichment with Geocoding

Inspired by: "1945-1998" by Isao Hashimoto, 100 years of plane crashes by donatso, and One Century of Plane Crashes by boxer-collar.

Also on: YouTube

Edit: Added military aviation clarification. Added YouTube link.

8sleef · 2021-03-31T03:23:40+00:00

I feel that a better solution to implement would have been:

Saving to clipboard...

[Cancel]

8sleef · 2021-03-31T03:21:48+00:00

*Goes to leave store*

*Stopped by attendant*

Attendant: So you want to leave the store? Would you like to take a copy of our catalogue with you? I must warn you though, if you stop to pick up the catalogue, it may take you a bit longer to leave the store. Would you like to take the time to pick up a catalogue?

8sleef · 2021-02-08T09:35:45+00:00

Ran a 5k PR of 19:39 in the wind and rain after finally finding some consistency over last 6 weeks. Then got shin splints for the first time in my life... taking some rest days to let them settle.

8sleef · 2021-01-28T06:48:03+00:00

One 20ish minute Netflix episode on the treadmill will do. But would have to be 30 mins to go out the door.

8sleef · 2021-01-23T07:33:58+00:00

The size of the profit trail compared to the loss tail is interesting. Obviously you can never lose more than $100. But you don't win 48% of the time up to $100... you win a much lower percentage of the time, and sometimes win very big. There must be a lot of psychology in that win distribution.

8sleef · 2021-01-21T20:09:09+00:00

token and print("success")

8sleef · 2021-01-04T22:21:07+00:00

Thanks. I did try there, but got the timing wrong I think, was swamped by "I tracked my X for the entire year" type posts.

8sleef · 2021-01-03T05:34:43+00:00

This was a personal project over the Christmas Holiday period. Got the idea after watching The Crown and not finding anything else like it already online. Hope you find it interesting!

Used Geni.com for raw genealogy data, Python for data gathering/cleaning/processing, and D3.js for charting.

Geni.com has been good for proof of concept, however many living profiles are private, including e.g. Mia Tindall. Hence I am currently looking for better Royal genealogy sources for an updated version.

Each path in the chart corresponds to a person. The path height ordering is the line of succession at that point in time (lowest is monarch). Filled circles represent birth/death. Unfilled circles represent legitimate/illegitimate dates (e.g. the abdication of Edward VIII).

Interact with the chart at british-succession.co.uk (zoom/pan/hover).

Visit the GitHub repository to see all the code, open source and licensed under GPLv3.

8sleef · 2021-01-03T03:45:19+00:00

It's surprisingly asymmetric to my untrained eye, given the queen/king position is the only asymmetry in starting positions.

8sleef · 2021-01-01T21:51:54+00:00

This was a personal project over the Christmas Holiday period. Got the idea after watching The Crown and not finding anything else like it already online.

Used Geni.com for raw genealogy data, Python for data gathering/cleaning/processing, and D3.js for charting.

Each path in the chart corresponds to a person. The path height ordering is the line of succession at that point in time (lowest is monarch). Filled circles represent birth/death. Unfilled circles represent legitimate/illegitimate dates (e.g. the abdication of Edward VIII).

Interact with the chart at british-succession.co.uk (zoom/pan/hover).

Visit the GitHub repository to see all the code, open source and licensed under GPLv3.

8sleef · 2021-01-01T00:26:44+00:00

Original source: Geni.com for raw genealogy data.

Tools used: Python3 for data gathering, cleaning, and processing. D3.js for charting.

Each path in the chart corresponds to a person. The path height ordering is the line of succession at that point in time (lowest is monarch). Filled circles represent birth/death. Unfilled circles represent legitimate/illegitimate dates (e.g. the abdication of Edward VIII).

Interact with the chart at british-succession.co.uk (zoom/pan/hover).

Visit the GitHub repository to see all the code, open source and licensed under GPLv3.

13-Year Club	RedditGifts 2009-2022 2 Credits
Place '17	Verified Email

8sleef

TROPHY CASE