Vardaman.org

My Github




Browse

Scripting

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

Denver Body Art

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.

http://www.vardaman.org/body.php

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

Restrooms

Denver's Open Database lists restrooms in the public parks. There are 57 listed, but a few only show they exist at all. Let's look for the ones that actually have info, which are 53 records:

SELECT *
  FROM prop.park_restrooms
  WHERE (year_built > 0 OR year_rehab > 0 OR notes <> '')

+--------------------------+--------+------------+------------+-------------------------------------+
| location                 | heated | year_built | year_rehab | notes                               |
+--------------------------+--------+------------+------------+-------------------------------------+
| Barnum East              | No     |       2015 |          0 |                                     |
| Bear Creek Park          | No     |          0 |       2008 |                                     |
| Berkeley Lake Park       | No     |          0 |       2009 | East                                |
| Berkeley Lake Park       | No     |       2011 |          0 | West                                |
| Bible Park               | No     |       2011 |          0 | South                               |
| Bible Park               | Yes    |          0 |       2010 | North                               |
| Centennial Park          | Yes    |          0 |          0 | Permitted use only                  |
| Central Park             | Yes    |          0 |          0 | West                                |
| Central Park             | Yes    |          0 |          0 | East                                |
| Chaffee                  | No     |          0 |       2009 |                                     |
| City of Axum             | No     |          0 |       2002 |                                     |
| City Park                | Yes    |          0 |          0 | City Park Pavilion                  |
| City Park                | No     |       2010 |          0 | Museum                              |
| City Park                | No     |          0 |       2009 | Tennis                              |
| Commons Park             | No     |       2000 |          0 |                                     |
| Congress Park            | No     |          0 |       2011 |                                     |
| Cook Park                | No     |          0 |       2010 |                                     |
| Crestmoor Park           | No     |          0 |       2010 |                                     |
| Eisenhower Park          | No     |          0 |       2008 |                                     |
| Garfield Lake Park       | No     |       2010 |          0 |                                     |
| Garland Park             | No     |          0 |       2009 | West                                |
| Garland Park             | No     |       2011 |          0 | East                                |
| Great Lawn               | Yes    |       2008 |          0 |                                     |
| Greenway Park            | Yes    |       2003 |          0 |                                     |
| Harvey Park              | No     |          0 |       2008 |                                     |
| Jackie Robinson Fields   | Yes    |          0 |          0 | In press box. Permitted use only.   |
| Jefferson Park           | No     |          0 |       2009 |                                     |
| Kennedy Ballfields       | No     |          0 |       2010 | In press box. Permitted use only.   |
| Lowry Sports Complex     | Yes    |       2008 |          0 | Synthetic Fields                    |
| Lowry Sports Complex     | Yes    |       2008 |          0 | Tennis/Basketball                   |
| McWilliams Park          | No     |          0 |       2010 |                                     |
| Montbello Central        | No     |          0 |       2009 | In press box. Permitted use only.   |
| Observatory Park         | No     |          0 |       2010 |                                     |
| Parkfield                | No     |       2011 |          0 |                                     |
| Pferdesteller            | No     |          0 |       2009 |                                     |
| Pulaski Park             | No     |          0 |       2010 |                                     |
| Robinson Park            | No     |          0 |       2010 |                                     |
| Rocky Mountain Lake Park | No     |          0 |          0 | 2 seperate structures for men/women |
| Rosamond Park            | No     |       2011 |          0 |                                     |
| Ruby Hill Park           | Yes    |       2017 |          0 | Levitt Pavilion                     |
| Ruby Hill Park           | No     |          0 |       2010 | Ballfields                          |
| Ruby Hill Park           | Yes    |       2011 |          0 | Ruby Hill Pavilion                  |
| Rude Park                | No     |          0 |       2009 |                                     |
| Silverman Park           | No     |          0 |       2009 |                                     |
| Sloan's Lake Park        | Yes    |       2003 |          0 | South Playground                    |
| Sloan's Lake Park        | Yes    |          0 |       2011 | Boat House                          |
| Sloan's Lake Park        | No     |       2011 |          0 | North Tennis Courts                 |
| Town Center              | No     |       2008 |          0 |                                     |
| Vanderbilt Park          | No     |       2011 |          0 |                                     |
| Veterans Park            | No     |          0 |       2010 |                                     |
| Washington Park          | Yes    |          0 |          0 | Boat House                          |
| Washington Park          | No     |          0 |       2010 | Central                             |
| Washington Park          | Yes    |          0 |       2010 | South Tennis Courts                 |
+--------------------------+--------+------------+------------+-------------------------------------+

Somebody on their data entry team does not know how to spell 'separate.'

permalinkJune 4, 2019

More SQL

Well, I imported 'Athletic Fields' from Denver's Open Data Catalog, and let's just say I want to see some baseball but for night-time games lighting I think is important (maybe for my kid or my own eyesight). After some database cleanup, I got this:

SELECT *
  FROM prop.ath 
  WHERE feature = 'Baseball'
  ORDER BY lights DESC, location;

+----------+-----------------------------+-----------------+--------+--------+----------------------------+
| feature  | location                    | surface_type    | lights | fenced | notes                      |
+----------+-----------------------------+-----------------+--------+--------+----------------------------+
| Baseball | Argo Park                   | Skinned Infield | Yes    | Yes    |                            |
| Baseball | Barnum East Park            | Skinned Infield | Yes    | Yes    |                            |
| Baseball | Garland Park                | Skinned Infield | Yes    | Yes    | Lacrosse & Rugby in Spring |
| Baseball | Jackie Robinson Fields      | Skinned Infield | Yes    | Yes    | Youth Only under 10        |
| Baseball | La Alma / Lincoln Park      | Skinned Infield | Yes    | Yes    |                            |
| Baseball | Northfield Athletic Complex | Skinned Infield | Yes    | Yes    |                            |
| Baseball | Parkfield                   | Skinned Infield | Yes    | No     | Denver PAL                 |
| Baseball | Ruby Hill Park              | Skinned Infield | Yes    | Yes    | Carberry                   |
| Baseball | 46th & Pecos Park           | Skinned Infield | No     | No     |                            |
| Baseball | Bible (James A) Park        | Skinned Infield | No     | Yes    | Lacrosse in Spring         |
| Baseball | Ciancio Park                | Skinned Infield | No     | No     |                            |
| Baseball | City of Cuernavaca Park     | Skinned Infield | No     | No     |                            |
| Baseball | City Park                   | Skinned Infield | No     | No     |                            |
| Baseball | Congress Park               | Skinned Infield | No     | No     |                            |
| Baseball | Garfield Lake Park          | Skinned Infield | No     | No     |                            |
| Baseball | Martinez Park               | Skinned Infield | No     | No     | Youth Only                 |
| Baseball | Montbello Central Park      | Skinned Infield | No     | Yes    |                            |
| Baseball | Rocky Mountain Lake Park    | Skinned Infield | No     | No     |                            |
| Baseball | Schafer (Martin J) Park     | Skinned Infield | No     | No     |                            |
| Baseball | Town Center                 | Skinned Infield | No     | No     |                            |
+----------+-----------------------------+-----------------+--------+--------+----------------------------+
20 rows in set (0.01 sec)

Let us see what sports Denver lists in their public Parks and Recreation DB:

SELECT feature, COUNT(feature) AS fcount
    -> FROM prop.ath
    -> GROUP BY feature
    -> ORDER BY fcount DESC;

+------------+--------+
| feature    | fcount |
+------------+--------+
| Softball   |     74 |
| Mixed-use  |     71 |
| Baseball   |     20 |
| Football   |      9 |
| Soccer     |      8 |
| Volleyball |      4 |
| Rugby      |      3 |
| Cricket    |      1 |
+------------+--------+
8 rows in set (0.00 sec)

I have a start with 'location' Let me write a script to get actual addresses. Then we can use GPS mapping to do graphs of where every public baseball field in Denver is located.

permalinkJune 2, 2019

Denver Liquor Licenses

Here's another sample problem:

Denver has 2,468 active liquor licenses. They have a FULL_ADDRESS field that can be empty, but also a ADDRESS_LINE1 and ADDRESS_LINE2, and ADDRESS_LINE2 that never seems to be populated. They also have CITY/STATE/ZIP, but the ZIP codes are all missing. And the FULL_ADDRESS for places like 'Bonefish Grill #6602' are missing. We can't do a perfect full cleanup, but what we can do in SQL is to find ones where the FULL_ADDRESS is blank, replace it with ADDRESS_LINE1, then run the USPS API to fix the as best as we can: Sorry, a stupid typo wiped out the queries, but you can probably guess.

382 (blank) records.

Let's see how many we can fix with the LINE1 addresses. By UPDATE where the first address is blank. Run the USPS API and you end up with 36 missing records. But some are at the same address. That cut it down to 17 records.We can hand-fix that about as quickly as we can post a job listing for a college kid to fix. The dupes at only Civic Center are like:

* American Wounded Veterans Foundation
* Colorado Firefighter Calendar, Inc
* Civic Center Conservancy
* The American Alpine Club
* Newsed Comunity Development Corporation
* Bands For Lands
* Casa De Paz (comment: probably a food truck)

Can nearly come to 100% accuracy via USPS standards.

The total is actually smaller, because of dupes

1,780 rows

Next,that table has X/Y GPS info. So we can have some map fun

permalinkMay 31, 2019

Security Guards

Stupid project: Denver has a database with every licensed security guard in the city (2,366 people as of Friday). I don't care much about individual names, I just want a count of employees at each company, and only if they are the Top Ten employers.

After the import, I noticed a broad range of mixed-case spelling of company names. Let's fix that:

DROP FUNCTION IF EXISTS proper;
SET GLOBAL log_bin_trust_function_creators=TRUE;
DELIMITER |
CREATE FUNCTION proper( str VARCHAR(128) )
RETURNS VARCHAR(128)
BEGIN
DECLARE c CHAR(1);
DECLARE s VARCHAR(128);
DECLARE i INT DEFAULT 1;
DECLARE bool INT DEFAULT 1;
DECLARE punct CHAR(17) DEFAULT ' ()[]{},.-_!@;:?/';
SET s = LCASE( str );
WHILE i <= LENGTH( str ) DO
BEGIN
SET c = SUBSTRING( s, i, 1 );
IF LOCATE( c, punct ) > 0 THEN
SET bool = 1;
ELSEIF bool=1 THEN
BEGIN
IF c >= 'a' AND c <= 'z' THEN
BEGIN
SET s = CONCAT(LEFT(s,i-1),UCASE(c),SUBSTRING(s,i+1));
SET bool = 0;
END;
ELSEIF c >= '0' AND c <= '9' THEN
SET bool = 0;
END IF;
END;
END IF;
SET i = i+1;
END;
END WHILE;
RETURN s;
END;
|
DELIMITER;

We have our function, but just for the sake of future query speed, let's make the naming change permanent:

UPDATE sec.security SET company_name = proper(company_name);

May as well set the security guard's name in proper case:

UPDATE sec.security SET secname = proper(secname);

The Top Ten security companies by employee count in Denver are:

SELECT company_name, COUNT(company_name) AS employees
    -> FROM sec.security
    -> GROUP BY company_name
    -> ORDER BY employees DESC, company_name ASC
    -> LIMIT 10;
+----------------------------------+-----------+
| company_name                     | employees |
+----------------------------------+-----------+
| Universal Protection Service, Lp |       509 |
| Hss Inc.                         |       321 |
| Securitas Security Services      |       173 |
| Advantage Security, Inc.         |       127 |
| Hss                              |        91 |
| Advantage Security, Inc          |        70 |
| G4s Secure Solutions Usa. Inc.   |        69 |
| Deco Inc                         |        68 |
| Argus Event Staffing, Llc        |        54 |
| Aquila Services Corporation      |        50 |
+----------------------------------+-----------+
10 rows in set (0.00 sec)

Guards named "Steven":

SELECT * FROM sec.security WHERE secname LIKE '%steven%' ORDER BY secname;
+----------------------------------+-----------------+
| company_name                     | secname         |
+----------------------------------+-----------------+
| Hss Inc.                         | Steven Anderson |
| Universal Protection Service, Lp | Steven Brandner |
| Deco Inc                         | Steven Chavez   |
| Deco Inc                         | Steven Flett    |
| Blackstone Consulting Inc        | Steven Houston  |
| Universal Protection Service, Lp | Steven Kyzar    |
| Advantage Security, Inc.         | Steven Melby    |
| Regis University Campus Safety   | Steven Ortega   |
| Grand Hyatt Denver               | Steven Rhyne    |
| Universal Protection Service, Lp | Steven Stone    |
| Advantage Security, Inc.         | Steven Wilhelmi |
| Hyatt Regency Denver             | Steven Wilson   |
+----------------------------------+-----------------+
12 rows in set (0.00 sec)

Fixed for typos in their data. 'Advantage Security, Inc.' actually has nearly 200 employees, not 127 (notice the dot).

+----------------------------------+-----------+
| company_name                     | employees |
+----------------------------------+-----------+
| Universal Protection Service, LP |       509 |
| HSS Inc.                         |       412 |
| Advantage Security, Inc.         |       197 |
| Securitas Security Services      |       173 |
| G4S Secure Solutions USA. Inc.   |        83 |
| Deco Inc                         |        68 |
| Argus Event Staffing, LLC        |        54 |
| Aquila Services Corporation      |        50 |
| Security Consultants, LLC        |        46 |
| Tcsec, Inc.                      |        39 |
+----------------------------------+-----------+
10 rows in set (0.00 sec)
permalinkMay 27, 2019

Denver Museum Of Nature And Science

You bunch of idiots. Stuff like this drives me insane.

So I apply for a job at the Denver Museum Of Nature And Science. I should have gotten an email auto-reply, but here's what my mail server shows:

May 26 12:17:54 vardaman postfix/smtpd[4672]: NOQUEUE: 
reject: RCPT from chrelay04.taleo.net[68.233.77.18]: 450 4.1.8 
<DMNSNoReply@hr.dmns.org>: 
Sender address rejected: Domain not found; 
from=DMNSNoReply@hr.dmns.org> to=<vardaman@[redacted].org> 
proto=ESMTP helo=<relay04.taleo.net>

It's a common anti-spam tactic to reject emails from domains that do NOT exist. "dmns.org" is their website and it exits, but "hr.dmns.org" does not. So a simple email spam filter kicks it. All they need to do is remove the "hr" from the sending address and the mail goes through.

Bugs me even more that their DNS isn't right, and the source code for their website is terrible. They need a competent IT person more than they need a table server.

permalinkMay 26, 2019

Show more posts...

Elsewhere

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