README

Tom Moertel <tom@moertel.org>
July 2012


This repository contains data sets relating to the 2012/2013
reassessment of properties in Allegheny County, Pennsylvania.  The
following data sets are included:

* Assessed values of all properties, before and after the reassessment
  (Source: Allegheny County Office of Property Assessments)

* Sales history for all properties
  (Source: Allegheny County Office of Property Assessments)

* Property characteristics for properties
  (Source: Allegheny County Office of Property Assessments)

* Recent sales history for residential properties
  (Source: Pittsburgh Neighborhood and Community Information System
  at the University Center for Social and Urban Research
  http://www.ucsur.pitt.edu/thepub.php?pl=370)


ACKNOWLEDGMENTS

I would like to thank Jeremy Boren at the Pittsburgh Tribune-Review
for obtaining the first three data sets.


REQUIREMENTS

To run the analyses in this project, you will need a Unix-like
operating system (e.g., Linux or Mac OS X) and the following software:

* The R statistical computing system
  http://www.r-project.org/

* The following R packages:
  - ggplot2
  - lubridate
  - plyr
  - reshape2
  - RSQLite
  - scales

* GNU Make (probably already installed on your computer)
  http://www.gnu.org/software/make/


WHAT'S IN THE BOX?

The main contribution of this effort is a SQLite3 database that
combines the data sets mentioned earlier and augments them with
metadata tables that represent knowledge of tax districts and their
taxing formulas.  These can be used to compute estimates of
interesting quantities, such as property-level tax effects due to the
reassessment.  The database also contains views that compute, on the
fly, some of these estimates.

Here's a quick overview of the tables in the database:

  Tax districts

  tds                  -- tax districts (sd or muni)
  sds                  -- school districts
  muni_tds             -- municipal tax districts
  muni_td_wards        -- wards into which municipalites are partitioned

  Properties and assessed values

  reval                -- before/after property assessments
  bldg                 -- property characteristics
  opa_sales            -- historical property sales
  ucsur_sales          -- recent residential property sales

  Computed estimates

  county_reval (view)  -- county-level assessment effects on properties
  sd_reval (view)      -- sd-level assessment effects on properties
  muni_td_reval (view) -- muni-level assessment effects on properties
  td_ptx (view)        -- est. property taxes for each td for each property
  ptx (view)           -- est. total property taxes due for each property


GETTING A COPY OF THE DATABASE

If you downloaded the Git source-code repository for this project,
you can build the database from the raw data sources in the project
by issuing the "make" command from the project's root directory.
(The process may take several minutes, the time depending on
your computer and its disk characteristics.)

A quicker strategy would be to obtain a compressed copy of the final
database (about 130 MB).  You can download a copy from the Downloads
section of this project's GitHub repository:

    https://github.com/tmoertel/allegheny-reval/downloads


EXAMPLES

To find Mt. Lebanon's taxing districts:

  $ sqlite3 data/reval.db
  SQLite version 3.7.7.1 2011-06-28 17:39:05
  Enter ".help" for instructions
  Enter SQL statements terminated with a ";"

  sqlite> .mode line
  sqlite> select * from tds where name like '%lebanon%';

                 kind = SD
                 name = Mt Lebanon
            bldg_rate = 26.63
            land_rate = 26.63
           total_rate = 0.0
  homestead_exclusion = 0.0
        rel_asm_total = 1.29856741047805

                 kind = Muni
                 name = MT. LEBANON
            bldg_rate = 5.43
            land_rate = 5.43
           total_rate = 0.0
  homestead_exclusion = 0.0
        rel_asm_total = 1.29856741047805

Because some districts tax the land and building value of properties
at different rates, the land_rate and bldg_rate are given separately.
For districts that use the same rate for both, these can be set to
zero and the single rate given in the total_rate field instead:

  sqlite> select * from tds where kind = 'County';
                 kind = County
                 name = Allegheny
            bldg_rate = 0.0
            land_rate = 0.0
           total_rate = 5.69
  homestead_exclusion = 15000.0
        rel_asm_total = 1.37529504829668

Also, note that, if a taxing district supports the homestead
exclusion, the exclusion amount will be given, as well.

To see how these rules work out, here are the estimated taxes due
for property 0001C00037000000.  First, for each taxing district
separately:

  sqlite> select * from td_ptx where PIN = '0001C00037000000';

      PIN = 0001C00037000000
     kind = County
     name = Allegheny
  Tax2013 = 51581.7133842374
  Tax2012 = 71125.0

      PIN = 0001C00037000000
     kind = SD
     name = City Of Pittsburgh
  Tax2013 = 112637.429193824
  Tax2012 = 174000.0

      PIN = 0001C00037000000
     kind = Muni
     name = PITTSBURGH
  Tax2013 = 87270.6737220187
  Tax2012 = 135000.0

And, now, the property's total for all taxing districts:

  sqlite> select * from ptx where PIN = '0001C00037000000';

      PIN = 0001C00037000000
  Tax2013 = 251489.81630008
  Tax2012 = 380125.0
  ptx_rel = 0.66159767523862

For this property, then, the reassessment had the effect of lowering
property taxes by a third:

  sqlite> select Tax2013 / Tax2012 - 1 as ptx_inc
     ...> from ptx where PIN = '0001C00037000000';

  ptx_inc = -0.33840232476138

Note that this fact is also given by the ptx_rel value for the property,
which represents the 2013 tax burden relative to the 2012 burden.  For
this property, it's 66% (= two thirds).


QUESTIONS

If you have any questions about these data or analyses, please
direct them to Tom Moertel <tom@moertel.org>.