A few random totals from Denver's 2019 financials so far this year. The biggest total goes to retirement, followed by funding Denver Health hospital:
DENVER EMPLOYEES RETIREMENT PLAN = $99,873,949.24 DENVER HEALTH MEDICAL CENTER = $87,664,306.95
DENVER BOTANIC GARDENS INC = $1,341,882.36 THE DENVER CENTER FOR THE PERFORMING ARTS = $7,849,046.24 THE DENVER POST LLC = $3,105,208.45 (Newspaper - that was probably advertising buys) BOMBARDIER TRANSPORTATION HOLDING USA INC = $24,907,025.94 (Bombardier is a maker of private jets) CORRECTIONAL MANAGEMENT INC = $3,433,443.82 (Private jail. Been outside their work-release place's front door on Dahlia St; worked with some guys from the inside before) CENTURYLINK COMMUNICATIONS LLC = $3,108,654.34 (And you thought your Internet bill was high) REVISION INC = $1,538,546.09 (IT contracting, but as a sideline Tony the owner rents apartments; one I lived in for five years) ALABAMA AGRICULTURAL AND MECHANICAL UNIV = $3.00 (The city's smallest total payment, but the data contains refunds and dummy payments of one cent) CARING FOR DENVER FOUNDATION = $24,390,712.00 (The largest one-time payment the city has made, but combined payments to others ends up higher)
Across all departments, petty cash came to $27,136.72; the Police about $2,800 and Fire about $2,000.permalinkSeptember 17, 2019
Another stupid database operation.
Denver makes their payments for expenses open. Let's go see how much the city has paid auto dealership "John Elway Chevrolet" so far in 2019, and break it down by department area. Overall, Mr. Elway's Chevy dealership has gotten paid $2,547,936.69 this year. The other dealership with Elway's name on it (Jeep) did $372,309.67 in business with the city, and someone working for the city spent a total of $60 on three visits to Elway's Steak House at the airport (I'll bet on only drinks).
permalinkSeptember 17, 2019
Because I'm an idiot:
Let's look at liquor licenses. Again, the city's DB has messed-up addresses and no ZIP code, So yet another encoding and another JOIN. But this time, we can only compare the first five digits rather than the full ZIP+4. So we have 588 licensed stores and 1,969 liquor licenses. In my old apartment on Ogden Street in ZIP code 80218; after some data correction, there's 60 places with liquor licenses, broken down by category:
Next, it's kinda pointless to do a JOIN on adult and child obesity tables when the numbers are zero. I could add a WHERE clause, but I think it may be faster to just delete the records from those tables with zero values before I even do the SELECT. In the lifespan table alone there's five records with zero values.
Then there's neighborhoods that do not exist - city data sometimes had a GIS coordinate in that field. I blanked those out.permalinkSeptember 13, 2019
Email follow-up I sent to the city of Denver:
I just filed a compliment for your Denver Police. Two officers came out to Safeway to deal with a woman with mental/medical issues, and I just happened to be sitting nearby. The two officers acted awesomely. One gave me his card. John *******, badge #*****. He was kind and compassionate and extremely professional to the woman in distress and having a bad day.
If you could give him a bonus like a paid day off, that would be swell. But if that's not in your policy, when performance reviews come up, I want you to keep this email and the website thing I filed.permalinkSeptember 9, 2019
Here's what I'm good at:
Denver's Open Database has some 240 tables. Let's look at food service licensees. Their data is incomplete, but you gotta work with what you have. There's 588 records of licensees.
First, make a database and table on my server in Freemont, CA, download Denver's food license table, and import it into the MariaDB SQL server. The addresses are inconsistent, many don't have a ZIP code, and the neighborhood code is largely in the wrong place. Let's document that and make it into a publicly-visible web page. Gotta add a unique store code to make sure I match everything up.
Next, we run all the addresses to fix the missing ZIP codes. USPS has a handy API for that. Bingo! We got 100% ZIP codes fixed and addresses standardized.
Next is the neighborhood codes. Looking at the data, the problems work right-to-left. A one-pass script might work, but that's too much trouble for me. I'll do multiple passes since it's only 588 records. I got it 100% fixed with some data massaging.
Minor cleanup - some neighborhood codes are 'CBD' but I prefer 'Central Business District' because it seems easier to understand for a casual reader. Then delete some fields that nobody might care about. Then produce an output result HTML file. I could have used PHP and dynamic pages, but for a weekly, the fastest load time is static.
It's all in one script. It takes perhaps 360 seconds to run, but it should probably run only weekly to not tax Denver's or the USPS's websites too much. I timed the final web page, and it loads in under .28 seconds at 1.44mbps T1 speed.
Video of ZIP encoding and entire script running:
I started writing the whole script at about 8am today when I didn't get day labor, and I finished about noon. With a fractured shin that got infected a week later, probably two more fractures in my left foot, and maybe two more in my left hand (from a fist-fight that lasted 2 seconds); typing is hard. and a pulled left calf muscle.permalinkAugust 31, 2019
Well, a good practice workout.
I back up every database on my server every hour. What would be better if I saved each backup daily, compressed it into a ZIP, named it after the date, and saved each for four weeks, then stored each one on my laptop, USB stick, and my cloud storage, then clean it up every month. That way I can recover even three weeks after a DB crash if I have to. I can't do it via local laptop script because the place I'm living at (Comcast) blocks SQL port 3306, so I need to run a script on my server in Freemont, CA, then another laptop script a few minutes later to fetch the data, pull it down, put on laptop, copy to USB, then upload the ZIP files back to Freemont, CA.
Should take me about four hours to write the code, and I should be in bed asleep by 8pm.permalinkAugust 11, 2019
Another SQL. Download licensed Body Art companies in Denver, ZIP+4 encode the addresses (100% success!), format the business name/address/city in proper case, and sort it alphabetically by business name.
So many improvements one can do. Script the entire process, automate the upload to a cloud, add clickable links to Google Maps, add links to nearby convenience stores, Starbucks, and nearby crime data for the past six months.permalinkAugust 10, 2019