Category: Information

Avoid “VARCHAR” columns in PostgreSQL [PGSQL Phriday #006]

February 28, 2023 / / 1 Comment

This is a response to Grant.

This is one thing I wish I knew while learning PostgreSQL: Don’t ever use the VARCHAR column type. I have never encountered a situation where that would have been superior to using TEXT.

The problem with VARCHAR column type is that it can be a fixed-width column that doesn’t allow text longer than a certain number of characters. This maximum character length can be introduced by the person who creates the table and adds the column.

The maximum character length can also be created by a GIS program (specifically, QGIS and the ogr2ogr command line tool) as well as some online tools that can convert spreadsheets to SQL and even write the table definition and “CREATE TABLE” statement. In both cases, the program tried to guess the column type and seemed to default to VARCHAR(y character length) instead of TEXT.

The maximum character length is a problem because trying to insert more text than allowed into that field will encounter an error and the INSERT or UPDATE statement will fail. I think it’s also a bad idea to create these columns because it makes it expensive to change your mind later about what length of text will go into that field.

Other things I wish I knew earlier

  • Using transactions to test your query and its affect on your data so that you can easily undo any changes.
  • How and when to use indexes beyond the “btree” index type (for example, “gin” for array and JSONB columns).
  • Add “time_created_at” and “time_updated_at” columns to basically every dataset. This makes it easy to sort things later; sometimes when creating a new dataset it’s not always obvious how you might want to display or manipulate the data until days, weeks, or months later. Sorting data by “when was the information changed last” is a common use case for me.

Chicago’s massive parking footprint – as measured on December 30, 2022

December 31, 2022 / / 0 Comments

It’s been three years since I last measured how much of Chicago’s land area is occupied by parking lots and parking garages. On December 25, 2019, using data drawn into OpenStreetMap by volunteers including myself, 2.5 percent of Chicago was for car parking.

Based on additional data since then, the land area of Chicago occupied by already-mapped parking lots and garages is 176,973,866.57 square feet, or about 2.7 percent of Chicago’s area.

This means that 0.52 additional square miles have been drawn into OpenStreetMap. If it hasn’t been drawn there, we can’t measure it. This means this number is a *minimum* of the land area devoted to car parking in Chicago.

Between 2019 and 2022, 173 more of these parking lots were drawn into OpenStreetMap in Chicago. There are still so many more parking areas that are not mapped!

That converts to:

  • 4,063.32 acres
  • 7.08 mi^2 (square miles)
  • 15.93km^2 (square kilometers)
  • 2.7% area of Chicago is parking (Chicago’s land area is ~589.56 km^2 )

There are some future parking -> building conversions coming soon. The buildings will be providing parking, but it will be integrated into a mixed-use development. The parking lot in the image, for example, is slated to become an office tower.

Automatically post your Flickr photos to your Mastodon account

December 24, 2022 / / 0 Comments

One process I’ve relied on in the past to keep my Twitter postings fresh is automatically cross-posting photos that I upload to Flickr to Twitter. This is done through Zapier and inspired by this answer in their forums.

Zapier currently doesn’t have any Mastodon integrations, but it’s possible to use Mastodon’s API and Zapier’s webhook function to notice your newly-uploaded photos from Zapier’s Flickr integration and “toot” them to your Mastodon account.

Note: I originally set this up as an RSS feed to Mastodon Zap before realizing that Zapier already has a Flickr integration.

First, to prepare for creating a Zap later, you’ll need to create an app in your Mastodon server. You can create an app by going to edit your profile and then opening the “Development” tab.

Select the “New application” button, give it a name, change the privileges, and use “http://zapier.com” as the only Redirect URI. Then check the appropriate boxes so that only a single privilege is checked: “write:statuses”.

Give your application a name, enter https://zapier.com as the Redirect URI, and check only the box next to “write:statuses”.

Want to get more advanced? Read the Mastodon API docs.

Mastodon will create a token for you to insert at the end of this URL that Zapier needs: https://mastodon.social/api/v1/statuses?access_token=INSERT_YOUR_ACCESS_TOKEN

If your Mastodon account is on a different server, then replace “mastodon.social” with the domain name of the server where your Mastodon account is.

Secondly, go to your Zapier account and create a new Zap. Search for the trigger app “Flickr” and then connect Zapier to your Flickr account if not already connected. The trigger should be something like what you see in the screenshot below.

When you make a new zap, use these settings: “Connect this app…Flickr…with this one…Webhooks by Zapier”. Then, “When this happens…New Photo…then do this! POST”. Zapier will automatically give it a name that you can try later and then you can select the “Try it” button to get stated and customize.


You’ll need to customize the action that Zapier takes each time there’s a new photo. Until there’s an integration with Mastodon there’s a little complexity to defining the webhook action that Zapier will do.

The type of webhook you’ll create is a “POST” and you only need to add one field to the payload that’s sent from Zapier to your Mastodon: “status”. The value of “status” can be whatever combination of text and fields that Zapier pulls from your Flickr.

The minimum field to insert is the link to the Flickr photo page. Mastodon will need this to generate a rich media preview to add to the Toot (since it’s not possible to send image attachments).

You can start your Zap now! And follow me on Mastodon.

Want to use RSS instead?

Here are some loose instructions on how to set up the RSS feed that your Flickr account produces.

Insert your Flickr ID (not your username or email, find it here) into this URL which will be the RSS URL for Zapier to check: 

https://www.flickr.com/services/feeds/photos_public.gne?id=INSERT_FLICKR_ID&lang=en-us&format=atom
Adanet measuring CO2 concentration in a Target store

I ran errands and measured the CO2 concentration everywhere I went

December 24, 2022 / / 0 Comments

I am starting to take my Adanet CO2 concentration monitor everywhere because I want to see which stores, restaurants, and offices have “fresher” air. The other day on December 20, 2022, I visited a BMO Harris bank branch and a Target store, and I took note of the measurements in three locations within my apartment building.

My goal is to take two readings in each location and photograph the second reading. The photograph provides the proof of the reading in the location I specified as well as a timestamp and GPS that only I can see.

I also took an outdoor reading to establish what the ambient level was that day:
421 parts per million (ppm), which is exactly what the global ambient level is!

Keep in mind that a typical reading in my studio apartment is around 650 ppm.

An outdoor reading of 421 ppm, for reference.

BMO Harris bank branch – 115 S LaSalle St

This is a large bank branch with half a dozen teller stations and a significant business banking area. There were two tellers, a handful of other staff, and myself and another customer – the person density was very low.

Reading: 614 parts per million (ppm)

The CO2 reading was 614 ppm at a BMO Harris bank branch in downtown Chicago.

Target – 1 S State St

A busy department store is where I was most excited to take several readings. I took four readings, all on the second floor.

  1. Men’s clothing department, three minutes after entering the store: 646 ppm
  2. Another area in the men’s clothing department, three minutes later: 785 ppm
  3. A dressing room, six minutes after the previous reading: 913 ppm
  4. Automotive accessories aisle, 15 minutes after: 961 ppm
Photos from inside Target follow the list order above, from left to right.

To give you another reference point, the readings have regularly exceeded 800 ppm – and have exceeded 1,000 ppm if I burn some food – when I’m cooking in my studio apartment. As I write this from there, the reading is 623 ppm.

I was pleased with these numbers at Target; I’m not an expert on assessing air quality but the Centers for Disease Control writes “that indoor CO2 concentrations no greater than 700 parts per million (ppm) above outdoor CO2 concentrations will satisfy a substantial majority (about 80%) of occupants” in office environments – or about 1,121 ppm.

My apartment building

I took three readings in my apartment building:

  1. One of the two bike rooms: 619 ppm
  2. An elevator (I had to visit a lot of floors to wait until I could get a second reading, which also meant the door opened a lot): 788 ppm
  3. Gym (which has two rooms, and I took the reading in the larger room that had fewer people at the time): 596 ppm
Photos from inside my apartment building follow the list order above, from left to right.
Photo of the Adanet sitting on my desk

Say hello to Adanet, my new CO2 concentration monitor

December 16, 2022 / / 0 Comments

I acquired a homemade and open source Adanet carbon dioxide (CO2) monitor from a friend in Chicago and tested it on a short trip on the Brown Line ‘L’. The Adanet monitors the concentration of CO2 in the air, in parts per million, which is a proxy for how “fresh” the surrounding air is.

Monitoring CO2 concentration became a more common activity and point of discussion since the COVID-19 pandemic began. A key way to reduce risk of transmission is to have “fresher” air. I’ll establish that “fresher” air is replacing air that has people’s outgoing CO2 with air that has less CO2, namely outdoor air.

(Another reason to monitor CO2? Excessive CO2 can lead to a decline in cognitive ability and sleep quality.)

I conducted an unscientific test of the “freshness” of the air inside a single Brown Line car on my trip between the Western and Belmont stations. I took five readings, which was the most I could take given that the Adanet refreshes every three minutes.

Map showing gray markers indicating where readings were taken. The trip started at the Western Brown Line station and the last reading was taking just before the train pulled into the Belmont station.

The ambient global measurement of CO2 is 421 ppm, measured in May 2022.

On the transit trip, the lowest reading was 475 ppm, which was taken while the device was in my coat pocket before I boarded the train at the outdoor station.

The highest reading was 680 ppm, when the train car had the most people on it during my short trip.

I have been checking the Adanet since getting home two hours ago.

  • I left it in the hallway outside my apartment and a single reading was 556 ppm.
  • Inside my studio readings have been around 650±20 ppm.
  • The highest reading since I got home has been 830 ppm and this is because I partially burned a quesadilla, releasing additional carbon into the air (my standalone air filter also turned on automatically to deal with the reduction in air quality).
  • I opened the balcony door to let fresh air in and 15 minutes later the reading dropped to between 671 and 692 ppm (the more readings the better).