Recent content on Peter Boone
I have a 1987 Honda Elite 150 (CH 150) “Spacey” moped that I love to cruise around on. I struggled to find a service manual online. So I bought a copy on Ebay and scaned all of the pages. I then ran it through OCRmyPDF to make it easier to search. That PDF can be downloaded here. If you want the raw scanned pdf it can be downloaded here.
I enjoyed this one! Windowing functions worked nicely for this. WITH raw_input AS ( SELECT '''30373 25512 65332 33549 35390''' AS raw_text ), raw_rows AS ( SELECT o, raw_row FROM ( SELECT SPLIT(raw_text, '\n') raw_row_array FROM raw_input ), UNNEST(raw_row_array) raw_row WITH OFFSET o ), split_rows AS ( SELECT SPLIT(raw_row, '') as split_row, raw_row, o FROM raw_rows ), unnested_rows AS ( SELECT…
I have fallen behind since day 6, but am attempting to make up some ground. Day 7 proved to be pretty tough. I kept track of the “current” directory by appending all of the cd commands. I then removed the directory before the .., ie one/dir/.. -> one. I attempted to use a recursive regex for this, but unfortunatly BigQuery uses the re2 regex library which does not support recursive…
Okay, hope in SQL has been temporarily restored. This problem fits SQL a bit better so I did not have to do anything too wild. For part 2, I decided to copy/past LAG(4) - LAG(13). There might be a better way to do it but I decided to keep it simple for myself. WITH raw_input AS ( SELECT 'mjqjpqmgbljsphdztnvjfqwrcgsmlb' AS raw_row ), points as ( SELECT TO_CODE_POINTS(raw_row) p FROM raw_input ),…
Oof, this one was rough. This may be the start of the end for my SQL Advent of Code journey. I tried a few different approaches before “cheating” and falling back on a Javascript UDF. This code is a mess and I would definitly not recommend reading it. I don’t have the stomach to go back and read it myself to clean it up. CREATE TEMP FUNCTION move_box(directions…
This year, I am be taking a page out of Felipe Hoffa’s book and will be attempting to complete the Advent of Code using BigQuery SQL. Until I reach the point where I can’t solve the puzzles with SQL, I’ll keep track of things here. Day 1 WITH raw_input AS ( /* To start, I just copy/paste the input as a raw text string, and then parse that string into rows.
The 2022 ARVO conference is taking place in Denver, CO. I am excited to be there networking and learning from the scientists in the field. Here are a few of the Choroideremia related posters and talks that I will be checking out: Sunday Cone inner-segment area and circularity in chorioderemia assessed with split-detection adaptive optics scanning light ophthalmoscopy - Peiluo Xu CHM cones are…
This is a living document of BigQuery resources that I have found useful (with some comments about their usefulness). “Under the hood” articles Anatomy of a BigQuery Query: Why is BigQuery cool? BigQuery under the hood: High-level descriptioon of how BigQuery is able to be so fast. In memory query execution: If you like map reduce you should read this article. It talks about what is…
I have been playing wordle recently (like a lot of people). I decided to check out how Josh gets the wordle of the day, so I inspected the source. Turns out, it’s all done client-side based on todays date! So a little bit of copy/paste and we can get the wordle for tomorrow! var potentialWords = ['cigar', 'rebut', 'sissy', 'humph', 'awake', 'blush', 'focal', 'evade', 'naval', 'serve',…
In part 1 I dove into Spotify Codes and explained the general technical concepts of how they work. If you haven’t read that post you should check it out. I shared it on reddit where it generated a lot of interesting discussion. Ludvig Strigeus, a key early developer at Spotify and the person who invented Spotify Codes, even stopped by and shared some more information on their creation and…
We’ve come a long way since the Human Genome Project. This effort to sequence the human genome for the first time was a tremendous achievement that lasted 13 (ish) years and cost $3 billion dollars. Thanks to this pioneering work and major advancements over the years it’s now possible to get your own genome sequenced for $300 in a few months. That’s a 10,000x decrease in cost! If…
When in doubt, NULL means “we don’t know the state of this thing so we can’t perform an operation on it.” Keeping track of how NULL values are handled in different SQL dialects can be tricky. This post will serve as a living document where I keep track of how BigQuery does this. NULL and STRING comparison Comparison always returns NULL. SELECT CAST(NULL AS STRING) = '', --…
Google’s Dremel paper is an interesting read that explains some of the concepts that underlie BigQuery. I am still processing the paper and have noticed a few things about repetition and definition levels that are relevant to the every day use of BigQuery. Columnar Data and Records The underlying storage format for BigQuery is columnar. One of the first pieces of advice given to people using…
I was reading a post about BigQuery cost optimization which stated this: Also remember you are charged for bytes processed in the first stage of query execution. Avoid creating a complex multistage query just to optimize for bytes processed in the intermediate stages, since there are no cost implications anyway (though you may achieve performance gains). I was curious about this, because I have…
Disclaimer: None of this should be taken as medical advice. Consult with your doctor. A question was posted in the Choroideremia (CHM) Facebook group about supplements: Hello to all my fellow group mates. Just wanted some input as to what vitamins or supplements you are taking to help with CHM. I have been taking the Bausch and Lomb preservision and would like to know if there is something better…
Background Earlier this year I made my way through Fullstack Open, a course designed to teach modern Javascript-based web development. In their words: The main focus is on building single page applications with ReactJS that use REST APIs built with Node.js. The course also contains a section on GraphQL, a modern alternative to REST APIs. The course covers testing, configuration and environment…
Spotify Codes are QR-like codes that can be generated to easily share Spotify songs, artists, playlists, and users. I set out to figure out how they worked, which led me on a winding journey through barcode history, patents, packet sniffing, error correction, and Gray tables. Spotify URIs Let’s start with Spotify URIs (Uniform Resource Identifiers). Different pieces of media (artists,…
The article: Association of Messenger RNA Level With Phenotype in Patients With Choroideremia: Potential Implications for Gene Therapy Dose Background The central dogma of biology is simply that DNA makes RNA makes protein. Messenger RNA (mRNA) transfers the information from DNA and is used to create protein. The question this study ultimately seeks to answer is “How much mRNA is needed to…
Washington, DC - Nov. 6, 2019 The American Society of Gene and Cell Therapy held a 3 day summit to discuss policy and other issues around gene editing and gene therapy. Day 1 addressed regulatory issues in gene therapy development Day 2 dealt with payment policies around gene therapy Day 3 (the day I attended) was focused on the ethical, societal, and policy issues around germline gene editing…
Panorama view from Whistler Mountain. These notes from Thursday May 2, the last day of ARVO 2019. The conference had thinned out a bit as some people had to leave, but there were still some presentations of interest. You can read a summary or my notes from the other days here: Friday, Sunday, Monday, Tuesday, and Wednesday. Papers Subfoveal gene augmentation therapy for choroideremia: One-year…
“Digital Orca” sculpture outside of the Vancouver Convention Center. These notes from May 1 are the continuation of a few articles about my time at ARVO. You can read a summary or my notes from the other days here: Friday, Sunday, Monday, Tuesday. Papers AAV2-hCHM Subretinal Delivery to the Macula in Choroideremia: 2 year Results of an Ongoing Phase I/II Gene Therapy Trial - Thomas…
ARVO 2019 took place at the Vancouver Convention Center from April 28 to May 2 and featured novel work, continued efforts, and updates on clinical trials. On the Friday before ARVO the Foundation Fighting Blindness and the Casey Eye Institute hosted a summit featuring gene therapy, stem cell therapies, and non-viral therapies. Randy Wheelock had been organizing a special interest group on Sunday…
ARVO poster area B. Roughly 1,000 new posters are displayed every day of ARVO. See my notes from Sunday and Monday. Patient Engagement in Ophthalmology Research This special interest group discussed current methods of patient involvement in research and explored new ways this could be improved. A special emphasis was put on international policies, specifically in Europe and Canada. BC SUPPORT is…
The Vancouver Convention Center’s East Building These are my notes from the second day at ARVO 2019. Check out Sunday’s notes if you missed them. Changing What it Means to be Blind: We’re All in This Together This session took place late in the evening and was put together by Kristin Smedley from the Curing Retinal Blindness Foundation. Smedley began the session by playing a TEDx…
The Vancouver Convention Center’s West Building A few words Similar to my article about the FFB Innovation Summit this post is a gathering of my notes from ARVO 2019. I will not be explaining topics, instead this is meant to be a reference for myself and others within the CRF. However I want these notes to be available to everyone since I think it is vitally important for those affected by…
Retinal Cell and Gene Therapy Innovation Summit The 6th annual Innovation Summit organized by the Foundation Fighting Blindness and the Casey Eye Institute at OHSU was packed with exciting research and over 300 attendees. The summit took place in Vancuover, CA on April 26 just before ARVO. I will highlight some research that was relevant to Choroideremia and a few notes on the work. Gene Therapy…
The second article I wrote about ARVO 2018. CRF Special Interest Group at ARVO 2018 The CRF organized and hosted a special session at the annual meeting of the Association for Research in Vision and Ophthalmology (ARVO) to discuss Choroideremia. This special session, organized by CRF president Dr. Chris Moen, was attended by over 100 researchers and clinicians. The discussion featured a panel…
Preface I will be attending ARVO 2019 shortly, so here is my experience from the 2018 conference that I wrote last year. You can also read this article for more detailed notes on the special interest group the CRF sponsored at ARVO. ARVO 2018 The annual meeting of ARVO (the Association for Research in Vision and Ophthalmology) took place in Honolulu, Hawaii from April 28 to May 3, and was attended…
archive
Senior Software Engineer with a background in molecular biology, excelling in data engineering, team building, and solving data modeling challenges. Skilled at leveraging cutting-edge technologies and tools to drive innovation and tackle complex problems. Experience BenchSci Tech Lead - Senior Software Engineer, Data • Jan 2023 - Present Led a data engineering team to extract insights from…