These commands are all run within the psql command line tool.
Create table for tax year 2013 data
CREATE TABLE tx_2013 (
Acct_Num varchar(9),
Address text,
Unit text,
Homestd_Ex text,
Prop_Cat text,
Prop_Type text,
Num_Stor text,
Mktval_13 bigint,
LandVal_13 bigint,
ImpVal_13 bigint,
Abat_Ex_13 bigint,
Latitude numeric(7, 4),
Longitude numeric(7, 4)
);
Import 2013 data from csv
COPY tx_2013 FROM '/path/to/AVI_2013_Dataset_XY.txt' WITH CSV HEADER;
Create table for tax year 2014 data
CREATE TABLE tx_2014 (
Acct_Num varchar(9),
Address text,
Unit text,
Homestd_Ex text,
Prop_Cat text,
Prop_Type text,
Num_Stor text,
Mktval_14 bigint,
LandVal_14 bigint,
ImpVal_14 bigint,
Abat_Ex_14 bigint,
Latitude numeric(7, 4),
Longitude numeric(7, 4)
);
Import 2014 data from csv
COPY tx_2014 FROM '/path/to/AVI_2014_Dataset_XY.txt' WITH CSV HEADER;
Create one table with both tax years
SELECT old.*, new.Mktval_14, new.LandVal_14, new.ImpVal_14, new.Abat_Ex_14 INTO tx_2013_2014 FROM tx_2013 old JOIN tx_2014 new ON old.Acct_Num = new.Acct_Num;
Create table with tax records for unique addresses
SELECT * INTO tx_unique FROM tx_2013_2024 WHERE address IN (SELECT address FROM tx_2013_2024 GROUP BY address HAVING count(address) = 1);
Create a table of parcels that just contains the attributes needed to build a street address, as well as the geometry column
SELECT house, stdir, stnam, stdes, geom INTO dor_parcels_address FROM dor_parcels WHERE status=1;
Add a column to that table and populate with the full street address for each property
ATLER table dor_parcels_address ADD COLUMN full_address text;
UPDATE dor_parcels_address SET full_address = (house || ' ' || stdir || ' ' || stnam || ' ' || stdes) WHERE stdir IS NOT NULL;
UPDATE dor_parcels_avi SET full_address = (house || ' ' || stnam || ' ' || stdes) WHERE stdir IS NULL;
Create a table of parcels with unique addresses, which we'll use to join to the table of assessments
SELECT full_address, geom INTO dor_parcels_unique FROM dor_parcels_address WHERE full_address IS NOT NULL AND full_address IN (SELECT full_address from dor_parcels_avi GROUP BY full_address HAVING COUNT(full_address) = 1);
Join unqiue parcels table to unique combined tax records table
SELECT p.geom, t.* INTO parcels_avi FROM parcels_unique p INNER JOIN tx_unique t ON p.address = t.address;`
Add columns for tax bills
ALTER TABLE parcels_avi ADD COLUMN tx_2013 bigint;
ALTER TABLE parcels_avi ADD COLUMN tx_2014 bigint;
Calculate new taxes
UPDATE parcels_avi SET tx_2013 = ((MKTVAL_13 - ABAT_EX_13) * .32 * 0.09771);
UPDATE parcels_avi SET tx_2014 = ((MKTVAL_14 - ABAT_EX_14 - 30000) * 0.0134) WHERE homestd_ex = 'YES';
UPDATE parcels_avi SET tx_2014 = ((MKTVAL_14 - ABAT_EX_14) * 0.0134) WHERE homestd_ex = 'NO';
Set the 2014 tax to $0 where the homestead exemption pushed properties assessments below $0
UPDATE parcels_avi SET tx_2014 = 0 WHERE tx_2014 < 0 and homestd_ex = 'YES';
Add column for percent change in taxes
ALTER TABLE parcels_avi ADD COLUMN tax_change NUMERIC(10, 3);
Calculate percent change
UPDATE parcels_avi SET tax_change = ((CAST(tx_2014 as NUMERIC) - CAST(tx_2013 as NUMERIC)) / CAST(tx_2013 as NUMERIC)) WHERE tx_2013 <> 0;
Fix up some of the tax change calculations--the decrease in taxes can't be less than -100%
UPDATE parcels_avi SET tax_change = -1 WHERE tax_change < -1 AND tx_2014 = 0 AND mktval_14 <> -1;
Create unique id column for TileMill Starts as a serial to autopopulate, then switch it to an int for TileMill compatability
ALTER TABLE parcels_avi ADD COLUMN parcelid serial;`
ALTER TABLE parcels_avi ADD PRIMARY KEY (parcelid);`
ALTER TABLE parcels_avi ALTER COLUMN parcelid TYPE INTEGER;`
Currently the parcel layer geometry is in 2272, but we want it in 4326 to work better in TileMill with the MapBox OpenStreetMap layers
SELECT AddGeometryColumn ('public','parcels_avi','geom_4326',4326,'MULTIPOLYGON', 2);
UPDATE parcels_avi SET geom_4326 = ST_Transform(ST_SetSRID(geom, 2272), 4326);