Background

Over the years, we have collected a ton of data - mostly from spreadsheets or PDF files, or retrieved from the Town, Vision or the Grafton County Register of Deeds. For example all the following files are in my Github repository.

• Lyme's Old-to-New values from the MS-1 reports
• Data "scraped" from Vision site
• Grafton County Register of Deeds

All these files have a similar format: a bunch of rows (usually representing parcels) with interesting data in the columns.

For example, I scanned all the Old-to-New documents for 2021 to 2024; they're in the "OldToNewValues" spreadsheet. If you look at that spreadsheet, you'll see these columns:

PID
	Map/Block/Unit
	Location (street address)
	Owner
	Code
	OldValue
	NewValue
	Ratio
	Difference
	Year
	

Or look at the "Scraped Data" file. From time to time, I run a script that loads every page from Vision and plucks up the values for each property. It produces these columns:

PID
	Owner
	Street Address
	Map/Block/Unit
	Book/Page
	Assessment
	Appraisal
	Lot size,
	...
	

The Grafton County Register of Deeds file includes columns for:

ID
	Date&Time
	Date
	Time
	Type
	Book&Page
	Book
	Page
	Party1 (seller)
	Party2 (Buyer)
	...
	

The good side of spreadsheets

Spreadsheets are a very convenient way to view data. Everyone knows how to open and manipulate them. And lots of interesting data is reported in a spreadsheet.

You could consider putting each of the above into a separate tab of a spreadsheet. And if you got another source of data, you could add a new tab (with its columns and rows) to the spreadsheet.

The downsides of spreadsheets

But spreadsheets come with downsides, primarily because modifying a spreadsheet is (mostly) irreversible - it's very hard to get it back to its original state. For example:

• To see the difference or ratio between old and new values, you could create a new column. But each time there's a new column, the "underlying data" gets changed.

• Or sort by a column. But the act of sorting changes the underlying data of the spreadsheet. And putting the data back to its original state gets really hard.

• Or, you don't particularly care about a particular column, say last year's assessed land value: that column just gets in the way. You could delete it - but maybe you'll want it later on.

What if there were a way that you can select only the columns you want, and create new columns with their own calculations, and sort every which way?

Using a database

That's where SQL databases come in. Each of those spreadsheets becomes a table in the database. The table's columns and rows exactly match the spreadsheet's columns and rows. When you use a database, you create queries that produce new results without changing the underlying data.

The fundamental rule of databases (their "super power") is that every operation starts with a table and creates a new (modified) table. Subsequent operations make further modifications to create yet another table. That new table doesn't get "added to the database". Instead, the database program just displays the final result, without changing the underlying tables.

And finally, a database query is simply a series of instructions that tell how to manipulate the tables. That query can be saved and re-executed at any time, or modified to answer different questions.

qStudio demonstration

qStudio is an application that lets you view and manipulate the tables of a SQL database. It's the first tool that I think is good enough to criticize - that is, all the prior ones were just too hard to use, with too many steps to get useful results. I like qStudio because it:

• Runs on Windows, Mac, Linux.
• Provides a good enough user interface. (Although qStudio's interface has some some sharp edges, they're easy to recognize and work around.)
• Uses PRQL (pronounced "Prequel") to make it MUCH simpler to write queries (SQL is the pits, you won't need to know it)

Playing with qStudio

Lesson #2 tells how to install qStudio, download and open the _Property_in_Lyme_ SQLite database, and then start making your own queries.

1. Install qStudio

   On a Mac:

   • Download the Mac version of qStudio from: https://randomneuronsfiring.com/wp-content/uploads/qStudio.zip
   • Unzip the result to get the qStudio.app application
   • Double-click the application. You will see a "unverified developer" (or similar) warning. Use System Preferences -> Privacy and Security to indicate that you trust the application. Click "Open anyway" if necessary. qStudio opens.

   On a Windows machine:

   • Download the Installer for Windows from the qStudio Downloads page: https://www.timestored.com/qstudio/download
   • Run the installer in the usual way
   • Using the command line, enter winget install prqlc to install the PRQL compiler.
   • Open qStudio

2. Register for a free qStudio license When qStudio starts up, it requires you to register for a free account. Provide your email address and you will be taken to a page that displays a license code (a long string). Copy that string and paste it into the qStudio window.

3. Download the "Property in Lyme" SQLite database The latest copy of the database is always available from: https://raw.githubusercontent.com/TaxFairness/TaxFairness/refs/heads/main/Property_In_Lyme.sqlite

4. Open the "Property in Lyme" SQLite database in qStudio. To do this, drag the database's icon into the qStudio window (top left corner, but the bottom half of that pane).

5. (You might also download the Chinook database that is frequently used for examples. You can also drag the Chinook database to qStudio - it will work properly when both are opened.)

Playing with qStudio

qStudio is now running. Here are some hints for trying its features.

View the tables

Click any of the names of tables at the top-left of the window. The columns and rows appear at the bottom of the window. Interesting tables are:

• CleanScrapedData - a cleaned-up list of every parcel in Lyme, gleaned from information from the Vision database along with easement and frontage information.
• GraftonCtyRoD - all records from Grafton County Register of Deeds back to April 2019.
• LymeOldToNew - data from the (original) 2024 MS-1. I will update when we are permitted to view the info.

Create and edit queries

The basic process is:

1. Create a new file in qStudio (File -> New File)
2. Save as qStudioTest.prql (".prql" suffix is important) using File -> Save As... I recommend you save it to a new folder on the Desktop so it's easy to find later.
3. Enter a query. Start simple with from LymeOldToNew - this statement just displays all the rows and columns from the LymeOldToNew table.
4. Save the file with Cmd-S on Mac; Ctl-S on Windows
5. Execute the query (Cmd-E / Ctl-E)
6. You may be prompted with a request to install the "SQLite driver". Allow it to happen.
7. The result from the query appears in the bottom "Result" pane. If the Result pane is not visible, choose Windows -> Result
8. Edit the query by typing in the window. Save and Execute it as above.

Enhancing your query

Here are examples taken from the first lesson. You can experiment with qStudio by modifying the queries as shown below:

Note - these examples use the PRQL language (pronounced "Prequel"), not SQL. The PRQL Reference Book gives full details on creating queries.

Select two columns from the LymeOldToNew table, ignoring the rest

Do this by adding a select statement at the bottom. Note that the select statement requires a list enclosed within { ... } that has column names ("LO_Location" and "LO_Owner") separated by ",".

from LymeOldToNew
	select {
	   LO_Location,
	   LO_Owner,
	}
	

Here's the way to think about the query above. The statement from LymeOldToNew starts a "pipeline" with the contents of that table. It passes it to the next statement (select ...) that modifies the table, by retaining only the two named columns (with all the rows) that could be passed along to any subsequent statement.

Note that PRQL doesn't care about line breaks. A statement can be on one or many lines: the following is equivalent to the above: select { LO_Location, LO_Owner }

Select a couple more columns

Add more column names between the { ... }, separated by commas. Trailing commas are allowed, even if no other column name appears afterwards.

from LymeOldToNew
	select {
	   LO_Location,
	   LO_Owner,
	   LO_OldValue,
	   LO_NewValue,
	   LO_Year,
	}
	

The qStudio result now shows five columns, the first two plus the three new ones. The Result pane shows them in the order they are listed in the query.

Create a new column named "difference" of the new to old values

To do this, add a new line with the column name ("difference") followed by =, and then the calculation.

from LymeOldToNew
	select {
	   LO_Location,
	   LO_Owner,
	   LO_OldValue,
	   LO_NewValue,
	   LO_Year,
	   difference = LO_NewValue - LO_OldValue,
	}
	

_Note that the new "difference" column in the result is just as "real" as any of the original "LO_xxx" columns._

Note too, that the comma at the end of the line is optional, but recommended because you can comment out any line (see below) without hassle.

OK. That's all very interesting, but which property had the biggest change? Let's sort by the "difference" field. Add this line to the end, then re-execute the query:

sort {difference}
	

This sorts the rows by the value in the "difference" column. To reverse the sort, use sort { -difference }.

Oh, but that shows many different years. Let's just look at 2024. Add this line to the end:

filter (LO_Year == 2024)
	

_This includes any rows where the LO_Year is equal (==) to 2024. (The double-equal is required.) To exclude the year 2024, you can use "not-equal" !=. And the >, >=, <, and <= operators work as expected._

Commenting out lines

Sometimes you want to temporarily (or permanently) remove a line or group of lines from a query. PRQL uses # as the comment character. When it appears, the remainder of that line will be ignored. There is a keyboard shortcut - Cmd-/ (Mac) or Ctl-/ (Windows) - that comments out/uncomments the lines that are selected.

Export to Excel

One cool feature of qStudio is that you can export the results directly to Excel. The Result pane opens directly in a new workbook. To do this, click the small Excel icon in the top-right of the window. It's the second icon on the right side of the image below.

Further observations

1. PRQL statements "transform" the table they receive by modifing it in some way and passing it along to the next statement. This is the power feature of PRQL: the statements can be "stacked" to form a pipeline that continally modifies the data as it passes through to produce a final result.

2. The filter statement could have been placed much earlier in the query. In fact, it could have immediately followed the initial from ... That would have removed all the other years prior to passing them to the first select - and in the end, the result would have been identical.

3. Similarly, the sort ... statement could have been placed anywhere in this pipeline. From that point in the pipeline, all the rows would have remained in the same order.

4. Finally, a bit of "PRQL lingo":

   • Since PRQL statements "transform" a table, they're also called transforms.
   • Tables are also called relations.
   • A tuple is the PRQL term for column names that are enclosed in { ... }. For example, it's proper to say the select transform requires a "tuple" (instead of requiring a "list").