Fetching latest commit…
Cannot retrieve the latest commit at this time
|Failed to load latest commit information.|
|data||Add real-estate sales data from OPA and UCSUR|
README Tom Moertel <firstname.lastname@example.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 188.8.131.52 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 <email@example.com>.