Exploring Property Data for Fun and (No) Profit!

Christopher Schmidt, A Better Cambridge

2020-11-07

​

Slides: http://bit.ly/abc-dc

​

Who Am I?

• Professional Software Developer for 16 years
• Working with online GIS tools since 2006
• Helped Found the OpenLayers project, an early competitor to the Google Maps Javascript
• Co-author of the initial GeoJSON spec
• Initial Housing interest: wanted to prove somebody wrong on a neighborhood mailing list.
• Wanted to look into property data to better understand various details of how Cambridge was built
• Created a dataset to support everylot bot for Cambridge

Agenda

• Simple web-based tools anyone can use (no software needed!)
• Property Database
• GIS map
• Deed Registry
• Using GIS tools (qgis)
• Finding data
• Making Simple Maps
• Buffers
• More complex data
• Loading data into a simple database
• ogr2ogr to load Geodata into sqlite
• Creating updated data and bringing it into qgis

Lots of Data is Straightforward to Access

• Most towns, especially in MA, have an online portal for the assessor’s database
• Contains information on each property, which includes owner, assessment, year built, sale date, etc.
• Usually search for “property assessor’s database” or similar
• May require search by exact addresses
• Some towns also publish all the data (updated annually)
• This is how most “lot bot” accounts on Twitter work: processing downloaded assessor data

Cambridge Property Database

• Easy to use web interface
• Doesn’t require a complete search (e.g. just search “Laurel St”)
• Also allows filtering (e.g. apartments < 10,000 square feet)
• A useful starting point to quickly look up info on any property

Property Listing: Making your way to the map

• GIS Map takes you to the Map maintained by Cambridge GIS
• This can be a quick way to get to neighboring properties
• Sometimes necessary when the address of a property doesn’t match the mailing address
• Also quick way to see various stats about a property

Exploring GIS Data

• Can click on properties to select them
• Property card takes you to the Property Database entry for a property
• To Drag, use the dropdown in the top left to pick identify/pan
• To go back to selecting properties, will need to change back to select
• The map has a lot of detail, including address numbers, distances along every lot line, etc.

​

Deed Registry

• In Massachusetts, Deeds are all available online, and searchable without too much difficulty
• Deed searches can give more information about the details of the property, and in some cases, can help determine historical information (e.g. sales price in the past).
• Site: masslandrecords.com; then find your County and click into it (each County has its own registry)

Deeds: Basic Search

• Recorded Land
• Usually want to use “Property Search” if you have an address
• Name Search lets you search by owners (sometimes interesting for finding other buildings owned by someone, or by a company name)
• Book search lets you type in an identifier

Looking up a Property by Address

• 234-236 Allston St., Cambridge
• Deeds include transfers (including nominal transfers, usually changing the names on the deed)
• Includes mortgages
• Lets you go back in time
• Recorded land is indexed (e.g. address pulled out) until ~1974 in Middlesex
• But what happens after that?
• Page/book lookups!

Deeds: A Link to the Past

• In Massachusetts, Deeds are all available online, and searchable without too much difficult
• Deeds are recorded in “Books” -- in the Before Times, these were literal books, though now most filings are electronic.
• Possible to search based on address, but sometimes a lookup based on book/page will be more helpful; addresses often mis-typed

Page/Book: Further in the past

• In Middlesex, Page/Book numbers are indexed back to 1900s
• And even earlier than that, you can look up pages in actual books scanned by genealogy groups
• Each deed includes a reference (usually at the end) to where the previous owner granted the deed

Open Data Portal

• Many towns publish their data in an “Open Data Portal”
• Cambridge publishes a lot of different types of records, including:
• Full assessment database
• All BZA cases
• Building permits
• Etc.
• https://data.cambridgema.gov/browse
• Assessor’s data download: https://data.cambridgema.gov/Assessing/Cambridge-Property-Database-FY2020/aezj-8mpy
• ​

Geographic Information Systems

• GIS systems are a useful tool for managing geographic (map) data.
• Can use the data to do things like “show a map of every tree in Cambridge”
• Can use data provided by City of Cambridge to make a map of just about anything

Downloading Data from Cambridge

• Can use GitHub GeoJSON files for browsing, and many use cases
• Cambridge provides a basic basemap (with streets, buildings, curbs, driveways, etc.)
• Also has assessment data (parcels, districts)
• And a variety of misc information (polling places, streetlights, etc.)

qgis

• Open Source GIS tool
• Can load vector data and imagery layers
• Useful tool for making simple maps with color graduated data.
• Website: qgis.org
• ​

Using processing tools on data

• Demo of using GeoProcessing Buffer
• Load up buildings layer
• Load up polling locations
• Vector -> Geoprocessing Tools -> Buffer; enter 1500’
• See map of coverage of polling places for various parts of Cambridge

Imagery Data

• Cambridge offers a bunch of aerial imagery
• (Aerial photos are big, so this may be more difficult to work with for some)
• Also has historical maps dating back to the 1860s

More Advanced Analysis Tools

The remainder will focus on a combination of command line tools and using the output in qgis.

​

• Loading Property Data into Sqlite
• Using OGR to manage data formats
• Combining Parcel data with Property Data
• Using GIS to make maps with the Property data

sqlite: lightweight database

• Dealing with 53,000 row datasets in a spreadsheet can be annoying
• Sqlite is a lightweight database tool that can make it easier to do specific tasks
• Also allows manipulation of data to compute new info

Loading Property Data into sqlite

$ wget -O ‘assess2020.csv' 'https://data.cambridgema.gov/api/views/aezj-8mpy/rows.csv?accessType=DOWNLOAD'

$ sqlite3 cambridge.db

.mode csv

.separator ','

.import assess2020.csv Properties

alter table Properties add column year_sold integer;

​

select pid, interior_bedrooms from properties where PropertyClass='SNGL-FAM-RES' and cast(condition_yearbuilt as integer)>'2015';

​

select pid, landarea, condition_yearbuilt from Properties where Interior_Bedrooms='9' and PropertyClass='SNGL-FAM-RES';

Creating per-parcel data

• Assessments are per property (e.g. one row for each condo in a building)
• But many elements of zoning are interesting at a parcel level
• Assessment data has a key for each parcel (labeled “GISID” in the data)
• So use a query grouped by gisid to get per-parcel data.

create table lots AS select min(PID) as pid,

GISID as gisid,

count(PID) as props_in_lot,

count(distinct BldgNum) as buildings,

sum(Interior_NumUnits) as units,

sum(LandArea) as lot_size,

sum(Interior_LivingArea) as living_size,

sum(Interior_Bedrooms) as bedrooms,

address as address,

min(PropertyClass) as type,

min(Owner_Name) as owner,

cast(sum(AssessedValue) as integer) as assessed_value,

cast(sum(PropertyTaxAmount) as float) as tax,

cast(CASE WHEN Condition_YearBuilt != '0' THEN min(cast(Condition_YearBuilt as integer)) ELSE -1 END as integer) as year_built,

max(cast(substr(SALEDATE, -4) as integer)) as sale_year,

cast(sum(CASE WHEN PropertyClass != 'CONDO-BLDG' THEN SalePrice ELSE 0 END) as integer) as sale_price,

max(Exterior_NumStories) as num_stories,

max(Exterior_WallHeight) as story_height,

sum(Parking_Open)+sum(Parking_Covered)+sum(Parking_Garage) as parking_spaces,

max(Zoning) as zone

from properties

group by GISID;

​

OGR: tool for processing vector data

• Command line tool for managing geographic vector data
• Can manage/interact with GeoJSON, as well as sqlite
• We can use ogr2ogr to load data into sqlite, then use database tools to create tables we care about.
• ​

$ wget -O 'parcels.geojson' 'https://github.com/cambridgegis/cambridgegis_data/raw/master/Assessing/FY2021/FY2021_Parcels/ASSESSING_ParcelsFY2021.geojson'

$ ogr2ogr -append -f sqlite \� -nln parcels \� cambridge.db parcels.geojson

Join Assessor data + Parcel Data; Export to GeoJSON

• Since the data is now all in a normal sqlite database, we can join the two tables together using SQL
• Then we can tell OGR how to read the data
• And export to GeoJSON

$ sqlite3 cambridge.db

create table meta_parcels as select * from lots LEFT JOIN parcels on lots.gisid = parcels.ml;

-- This line just tells ogr how to read the data

INSERT INTO geometry_columns VALUES ('meta_parcels', 'GEOMETRY', 0, 2, 4326, 'WKB');

​

$ ogr2ogr -f geojson meta_parcels.geojson cambridge.db meta_parcels

​

​

Load Parcel Data into qgis

• Now we have a datasource with parcel data and assessor data
• Can use that data to create maps from parcel data, like this map of FAR
• Demo of showing data
• Expression Demo
• Labeling Demo

Software installation

• In Debian-based Linux systems, you will want to use:
• $ sudo apt-get install gdal-bin sqlite3
• Scripts that combine data and create the relevant GeoJSON output available in:
• https://github.com/crschmidt/habitationi/tree/master/cambridge
• qgis: https://qgis.org/en/site/
• ​