Vardaman.org

My Github




Browse

National Register of Historic Places SQL

Let's look at some data for fun.

First, let's download the federal list of the 'National Register of Historic Places' and import into an SQL table. This data changes slowly (currently about 96,000 records), so only fetch once every three months. I'll have to write an automation script later to download only once a quarter. First, the file is in Microsoft Excel xlsx format, and I need CSV for the import. There's a python script out there I can run from the command line to do that. Second, the file name is composed partly with the date they created the spreadsheet; the current one is named 'national_register_listed_20200108.xlsx' they produced on January 8th of this year. What I'll have to do is download the index page and find the link (filename) embedded in their HTML source.

Next, let's fix HTML ampersands and leading/trailing spaces in some fields (leading spaces and leading quotes really mess up INDEXes). Next, do the best we can to fix the dates (listed date might be populated with maybe NULL but designated may exist). They have both a proper address field and a description address field, but the description address may be populated and the proper address is NULL. They have values 'NULL' and 'Unknown' in some fields, so let's just replace with empty strings. If the architect/builder is 'Unknown", blank the field, If the address is restricted, replace the addresses containing 'Address Restricted' with a blank. Copy to a final table. Take that table and calculate maximum field width, then narrow each column to that size. Add a zipcode field, then proper MySQL DATE() fields. If the 'listed date' date does not exist, but the 'designated date' does, copy the designated field over the blank. Fix both dates to the new proper DATE(); (PAD those months and days with leading zeroes if need be). Create an INDEX on 'reference number' because we'll need it later for UPDATEs. Fetch the data fresh; this time we'll exclude it to only run Denver, CO.

That's 311 records for Denver, CO. The USPS could only verify 145 records, and minus the original import and cleanup, the script took 118 seconds. To keep things small, let's only look at properties architected by someone named 'Edbrooke' (Frank Edbrooke is known as the Dean of Denver architecture, and he and his brother designed a number of prominent buildings in Denver and CO, like the Brown Palace Hotel - see History Colorado's Frank Edrooke bio). That gives us 21 records (shown in the included file). As you can see, some fields need to be deleted and some renamed.

There's some other problems. We need a new table for long state names and short abbreviations to fix 'New Mexico' vs 'NM', make the fix, and narrow the field to two characters. The old date fields need to be trashed and the new columns renamed. The 'status' field should be trashed because all 98,000+ records are listed. There's five fields for 'significance' and it seems like a good place to use only one field and use an ENUM() data type. The 'restricted' field looks like it could be reduced to a TINYINT(1) TRUE/FALSE value. Something is really messed up with street addresses with unit numbers - the USPS is spitting back the unit # with the reference number too. Not sure why yet. Next, the 'external links' have some blank values - if blank, add a Google search for the property name+city+state (for instance, they have no link for the Brown Palace, which is arguably the most famous hotel in Denver and opened in 1892). The 'reference number' could be converted to an INT(), but some values I see might cause trouble later.

I'm running the script for the whole USA, which is about 98,000+ records. Everything was speedy, but the USPS ZIP+4 goes very slow; only about 948 records per hour. It will take all night. I need to create a way for the script re-run and starts where it left off. [Later...never mind - for the addresses I could have possibly corrected, it took 340 minutes to run. I got 18,995 fixed via USPS out of 95,643 total, so I got a bit under 20%.- seems feeble, but imagine fixing them all by hand].

Before you call me crazy, here's why such stuff matters:

A) If you go on vacation and can only walk it, where are the only international monuments nearby your hotel you can visit by foot?
B) How fast can the location load on your smartphone with a standardized address?
C) Minus the ZIP code, are you in Jackson, MS or Jackson, TN?
D) The USPS, FexEx, UPS, Amazon Prime, and even your local pizza place use correct addresses and ZIP codes at some level. Unless you like cold pizza.

---------------------------------------------------------

Can't really fix some things I wanted to because they have data entry problems. They really have an Historic Place in '' and in 'Morocco'? As much as I've fixed, there's still too many problems. Well, until then a MySQL VIEW makes some sense. Let's gather what fields and conditions I want:

SELECT property_name, listed_date AS listed, addy AS address, city, LEFT(state, 2) AS st, zipcode
  FROM demos.nrhp_final
  WHERE (state = 'colorado' OR state = 'co') AND
    city = 'Denver'
 ORDER BY property_name

MySQL offers a rudimentary HTML output as a raw table, but you can augment that with your own headers/footers to add some CSS styles. Like I said, this data changes slowly (Denver last added a property in Nov 2019, and before that in 2017), so let's build a static file (for max speed) in HTML from this VIEW (see it here);

http://www.vardaman.org/denver.html

Let's speed test that web page. One site says it loads in .25 seconds; another from London says it loads in 382 milliseconds and another in Frankfurt, Germany says 511 milliseconds. I have the most terrible smartphone in the world, and it still loads under one second on that.

Thanks to the changes for Restricted/blank addresses, etc, Denver dropped from 311 total/145 Zip+4 encoded to 305 and 144.

permalinkMay 13, 2020

Fast Food Hack

A pathetic thing I've been doing for months. When you get a receipt from most FF places, they usually offer a discount on your next meal if you go to their website, enter a nearly-thirty digit long number, answer several survey questions, and write down the result code on your old receipt.

The hack is, at most FF places they never check the number, have the ability to look it up, or even care. The receipts probably just go straight into the dumpster after they toss them into the same bin as employee-discounted food. For McDonald's, Burger King, and Sonic Drive-In; write down any seven-digit number, and it works. The most they can say is 'No', and that almost never happens unless the person taking your order is a senior manager or franchise owner.

I used the seven-digit number hack tonight for two-for-one Egg McMuffiins. Tomorrow I'll probably get a McChicken and a Sausage McMuffin for lunch, and pay a total of 53 cents after tax.

McD is usually two-for-one, BK is a free Whopper if you buy a small soda and fries (still a smoking good deal at nearly $6 off).

McD receipt

permalinkDecember 14, 2019

A suggestion for the city of Denver

A suggestion for the city of Denver:

You have several ongoing problems with the 16th Street Mall. The tile you are using requires constant replacement; on both the pedestrian and shuttle bus lanes. Keep the charm of the tiles, but pave with concrete or asphalt the bus lane. That solves two problems. The tiles need more maintenance, which paving will improve. With paving, the visual perception will change so pedestrians know they can walk on the tiles but not the pavement, which will lead to less accidents between buses and people walking.

permalinkDecember 8, 2019

Laptop

My personal laptop; performance statistics and graphs.

permalinkNovember 28, 2019

Benchmark

Let's generate traffic and see what happens:

This is ApacheBench, Version 2.3 <$Revision: 1807734 $>
Copyright 1996 Adam Twiss, Zeus Technology Ltd, http://www.zeustech.net/
Licensed to The Apache Software Foundation, http://www.apache.org/

Benchmarking www.vardaman.org (be patient)


Server Software:        Apache
Server Hostname:        www.vardaman.org
Server Port:            80

Document Path:          /
Document Length:        12250 bytes

Concurrency Level:      100
Time taken for tests:   665.978 seconds
Complete requests:      100000
Failed requests:        0
Total transferred:      1247100000 bytes
HTML transferred:       1225000000 bytes
Requests per second:    150.16 [#/sec] (mean)
Time per request:       665.978 [ms] (mean)
Time per request:       6.660 [ms] (mean, across all concurrent requests)
Transfer rate:          1828.70 [Kbytes/sec] received

Connection Times (ms)
              min  mean[+/-sd] median   max
Connect:       58  214 345.9     76    4458
Processing:    61  452 438.4    244    4981
Waiting:       60  204 121.7    188    4170
Total:        123  666 539.5    398    7523

Percentage of the requests served within a certain time (ms)
  50%    398
  66%    753
  75%   1022
  80%   1074
  90%   1294
  95%   1889
  98%   2106
  99%   2436
 100%   7523 (longest request)

permalinkNovember 25, 2019

Yet Another Monitoring Tool

There was nothing I could do Sunday, so I installed one more Linux monitoring tool on my server:

Monitorix for vardaman.org

permalinkNovember 24, 2019

Bathrooms

I did finally put some touches on a stupid project. Look at it and give me as much feedback as possible.

Here in Denver we have a 1.5 mile long pedestrian mall. In central downtown, with a free bus shuttle. A problem is because it draws a lot of homeless, the bathrooms are usually restricted to buying customers. I thought it would be disruptive to have a small site where people can share bathroom codes. I know about 10 people who would contribute.

Here's the Starbucks I'm in right now:

http://16.vardaman.org/

The passcode to get into the restrooms is 98765. I added a 'hover' for both the business name and the stop name. Hover mouse over the biz_name, and you see the last date/time it was updated. There is another hover for the full USPS ZIP4 address on the shuttle stop number. ZIP+4 encoding for the 47 records takes 6.4 seconds, and the load time for the main page. takes 0.61 seconds on a 1.44 mbps T1.

My main goal was to make it worthwhile, usable, self-explanatory, and load in well under one second on a cell phone.

ZIP+4 encoding:

permalinkNovember 14, 2019

Subway

I sent this to Subway's Customer Feedback website:

-------------------

Great service, great sandwich.

Very pleasant experience. Give her some kind of bonus like an extra 15 minute break on a shift.

Subway Kudo

permalinkNovember 4, 2019

Elitch Gardens

Worked Elitch Gardens (a Denver amusement park; for my MS friends, it's a lot like a permanent State Fair) yesterday.

A few comments:

Do NOT eat their food, or food from any other similar carnival-type event. They are lax on cleanliness, do not adhere to the standard Wash/Rinse/Sanitize cycle for cleaning dishes as a matter of unofficial practice (they have no sink traps, for example). When we ran out of gloves at both food locations, food was prepared with bare hands. No policy for timing or dating or rotating food; a manager actually grabbed an open bag of already cooked fries from the day before, and threw them in with a batch of fresh fries.

I did my best, but I was required to wear a black shirt. The only one I had was unwashed from the day before where I wallowed around in dirt and horse manure for eight hours at the National Western Complex.

The food is extremely inconsistent. Depends on cook, but the guy doing fries and wings Saturday knew what he was doing. The guy I worked with on funnel cakes was a nice guy, but he generated a lot of returned food for being burnt. And the food is expensive - French fries with a sprinkle of garlic powder and Parmesan cheese for $10.95? Good grief, that's expensive.

I was waiting in line for a restroom at the labor place; one of my co-workers took a long time in the bathroom. He walked out, and quickly walked back in before I could, saying "they can't find this." Now I know what smoked Crystal Meth smells like.

If you saw the employee area, you would barf at the amount of filth they allow to lie on the floor, and the bathrooms there are horrendous.

Having worked at Sam's Club, Walmart, King Soopers/Kroger, Good Times Burgers, Broncos Stadium (Mile High), Western National Complex; I'd eat at all of them, but the winner is:

Good Times Burgers

They do pay close attention to food safety.

permalinkNovember 3, 2019

Bellco ATM

Bellco Credit Union:

Your ATM ate my debit card.

You have no idea how much damage that did to me. My rent was up today at 10:00am, and to pay it I used your ATM to get cash. But all your ATMs on the 16th Street Mall (outside; which I used, and inside as well) were malfunctioning. I asked inside; they checked the machine as best as they could, but they told me when a card doesn't get ejected, the machine destroys it. I logged into my bank's website, and there was a $357 balance and all listed transactions were only my own. I called the bank, and they said my account was fine. Your ATM swallowed my card for no legitimate reason. Because I don't know the card number, there's no way for me to access my money like Western Union or a bank transfer to my Wells Fargo account. They told me a replacement card could be sent in 7-10 business days. To expedite, I had to pay a $35 fee (effing nearly 10% of my balance) to have a replacement card shipped via FedEx, and because a person has to sign for it, I can't work tomorrow because I have to sit outside and wait for delivery.

With no ability to access my money and pay my rent, for tonight I either need to beg a friend for a roof, or sleep on a park bench in 10 degree weather and a half-foot of snow on the ground. My card is now reported lost, my access to my account via web is gone as of the 20 minute phone call I had with my bank, I'm out $35 to Fedex (who knows when they will show up tomorrow), an expected $65-$75 in lost wages (waiting for Fedex), a $6 bus pass that I otherwise didn't have to buy, and about four miles of walking I didn't have to do.

Forgive me if I spend the rest of this cold, cold evening speaking badly about your ATM on social media and places like Yelp.

Stewart Vardaman

(If you need my email address or phone number, pull that damned card out of your damned machine, call my damned bank at the 800 number on the back, give them the last four digits of the card number [3077], and spend a few damned minutes on hold).

permalinkOctober 30, 2019

Western Stock Show

Western Stock Show - just a horse trick exhibit. Nice gig - concession stand; not hard work, only half-full crowd, got free food, they paid us for 9.5 hours. All I did was prep nachos, wash dishes, swept floor. I ate a bacon cheeseburger, two slices of pizza, a half-order of nachos and cheese, and a few spoonfuls of beef brisket.

I will tell you one thing about gigs like this: they are not playing around with cleanliness. I must have washed my hands and changed gloves 40 times that night. The boss insisted that even if I rubbed my brow; wash and new gloves. I adhered to that. The sanitary levels at these events is to higher standards than what I've seen in Fast Food.

A few things I would change:

* Weigh pizza ingredients (a woman got yelled at for using too much cheese during the shift I worked).

* They use NCR cash registers, and use a dedicated person to transmit orders from the NCR to the kitchen. How hard is it to use terminals and a touch button to do that? I could set up hardware and write software for that in ONE DAY, for under $1,000.

* You have to use analytics. Get the gate ticket sales and figure out why you don't have to dump 8 pizzas and ten trays of chips each night.

* Inventory - Running low on Jalapeno peppers for nachos? There's a world of under-employed people who can tell you how many peppers to order each week.

permalinkOctober 30, 2019

Weather Widget

Added a Weather Widget to my info panel. Updates every three hours; takes under one second to update:

Weather Widget

permalinkOctober 28, 2019

Denver spending 2019 above $10 million

Denver spending so far in 2019, by program/department/category where the category total payments exceeded $10 million. It didn't make the $10m cutoff, but Denver spent $5,125,713.53 so far this year on Snow Removal at the airport; $2,229,896.87 on golf and golf course-related expenses. The City Council has spent nearly $4,800 at Turin Cycles (on the NE corner of 7th Ave and Lincoln St). The Denver Central Library paid a total of $24.35 this year out of their Petty Cash Account for Office Supplies and Fixtures.

BTW, shout to Turin Bikes: I love the Specialized Bike I got a couple of years ago from you. Sweet deal for $900.

Your website sucks.

The person who created it should be forced to write on a blackboard 500 times 'I do not know what I am doing -- all I am is a copy-and-paste HTML thief; a mimicker of actual talent, and causing serious harm to clients like the Bike Shop.'

+-----------------------+---------------------------------+--------------------------------+-----------------+
| program               | department                      | category                       | amount          |
+-----------------------+---------------------------------+--------------------------------+-----------------+
| Airport               | Airport                         | Construction Services          | $146,575,109.51 |
| Airport               | Airport                         | Professional Services          | $142,811,581.94 |
| General Government    | Career Service Authority        | Payroll Liabilities            | $58,459,938.64  |
| Public Works          | Wastewater Management Division  | Construction Services          | $57,242,847.68  |
| Public Works          | Public Works                    | Construction Services          | $54,372,045.56  |
| Public Works          | Public Works                    | Professional Services          | $47,260,527.56  |
| Cultural Activities   | Arts and Venues Division        | Professional Services          | $21,363,807.51  |
| Public Works          | Public Works                    | Repair and Maintenance of Road | $21,300,349.65  |
| General Government    | Department of Finance           | Professional Services          | $20,963,124.58  |
| Airport               | Airport                         | Repair and Maintenance of Equi | $20,585,882.90  |
| General Government    | Department of General Services  | Utilities                      | $18,447,126.61  |
| General Government    | Department of Finance           | Payroll Liabilities            | $17,999,999.82  |
| General Government    | Technology Services             | Maintenance Agreements and Lic | $17,989,403.21  |
| Airport               | Airport                         | Utilities                      | $17,969,368.89  |
| Public Works          | Public Works                    | Vehicles                       | $16,939,488.05  |
| General Government    | Department of General Services  | Professional Services          | $15,479,901.38  |
| Public Safety         | Safety Administration           | Professional Services          | $13,812,310.88  |
| Community Development | Housing & Neighborhood Division | Business Housing and Construct | $12,339,978.36  |
| Public Works          | Wastewater Management Division  | Professional Services          | $12,278,331.69  |
| General Government    | Technology Services             | Professional Services          | $11,968,246.18  |
| Public Safety         | Undersheriff                    | Professional Services          | $11,516,338.88  |
| Airport               | Airport                         | Maintenance Agreements and Lic | $10,935,803.63  |
| Airport               | Airport                         | Travel and Transportation - No | $10,097,887.77  |
+-----------------------+---------------------------------+--------------------------------+-----------------+
permalinkOctober 19, 2019

Denver Library Spending

Just rambling through Denver's 2019 checkbook for what the city paid for the Denver Public Library (DPL); amount totals only over $50,000. So far in 2019 they spent (some samples):

Century Link  = $76,854.42
Comcast Cable = $92,626.96
Dell Computer = $209,627.16
Staples (Office Supplies) = $68,846.97

DPL uses overdrive.com for some of their digital content; they spent $984,000.00 with overdrive.com so far. DPL operates library trucks that they apparently lease from Transwest Trucks and Trailers for $88,832.00 so far in 2019. They spent $51,062.76 on elevators and escalators this year, and $117,837.19 on security guards.

Here is what drives me nuts: get a library card, get their app on your phone, and play around with their site and app. I can write the website and app to a presentable level in two weeks for under $5,000.

permalinkOctober 18, 2019

Denver payments to Goodyear in 2019

Denver payments to Goodyear in 2019:

SELECT c_name AS company, c_program_area AS program, c_exp_categ AS expense_category, 
    CONCAT('$', FORMAT(SUM(c_amount), 2)) AS total
  FROM food.chex
  WHERE c_name LIKE 'Goodyear%' AND c_year = 2019
  GROUP BY 1, 2, 3
  ORDER BY SUM(c_amount) DESC

+-----------------------------+--------------+--------------------------------+-------------+
| company                     | program      | expense_category               | total       |
+-----------------------------+--------------+--------------------------------+-------------+
| GOODYEAR TIRE AND RUBBER CO | Public Works | Auto Supplies and Materials    | $689,916.32 |
| GOODYEAR TIRE AND RUBBER CO | Airport      | Consumable Inventory           | $22,358.06  |
| GOODYEAR TIRE AND RUBBER CO | Public Works | Repair and Maintenance of Equi | $665.00     |
| GOODYEAR TIRE AND RUBBER CO | Airport      | Auto Supplies and Materials    | $330.20     |
| GOODYEAR TIRE AND RUBBER CO | Airport      | Administrative Expense         | $59.79      |
| GOODYEAR TIRE AND RUBBER CO | Public Works | Professional Services          | $2.20       |
+-----------------------------+--------------+--------------------------------+-------------+

Here is something worth mentioning: MySQL/MariaDB cannot use an INDEX when you use a LIKE clause. It's imperceptible by eyeballing on our sample query because the record set is so small (121,659 rows). The LIKE for 'Goodyear' is fine at (0.00477872) seconds, but explicitly spelling out 'GOODYEAR TIRE AND RUBBER CO' and using EQUAL vs LIKE is (0.00417095) seconds because it can use the INDEX on company name. Six thousands of a second might seem trivial, but think about the scale if you're dealing with US Census data on about 330 million people against a JOIN on 300 million cell phone numbers and another JOIN against the USPS's massive address database (maybe 170 million?), and total pieces of mail north of 40 billion.

permalinkOctober 17, 2019

Show more posts...

Elsewhere

Cowboy Junkies
Reason Magazine
Scientology
Web Site Optimization
JD Hodges
Cato Institute
Field of Schemes
Oleg Volk
vardaman.net
vardaman.com