Date: 2015-07-24
Tags: gis real-estate
The Allegheny County Offered by the Office of Property Assessments offers a CD with information for all of the properties in the county that is updated quarterly. The CD contains a single 242MB file, currently in xlsx format.
To convert the Microsoft format to a format which can be consumed by PostgreSQL, namely CSV, LibreOffice can be used:
libreoffice --headless --convert-to csv ALLEGHENY_COUNTY_MASTER_FILE_07022015.xlsx
We can now create a table to import the CSV directly into. I created this schema by looking at the data found in the csv and correcting any errors found on import. PostgreSQL is very nice in that it won’t truncate data and will complain if invalid data is inserted. If you choose to use MySQL, please be aware that it can truncate and coerce data.
CREATE TABLE tax_assessments (
parid character(16),
propertyowner character varying(255),
propertyhousenum character varying(255),
propertyfraction character varying(255),
propertyaddress character varying(255),
propertycity character varying(255),
propertystate character varying(255),
propertyunit character varying(255),
propertylocation2 character varying(255),
propertyzip character(5),
municode character varying(4),
munidesc character varying(255),
schoolcode character varying(4),
schooldesc character varying(255),
neighcode character varying(50),
neighdesc character varying(255),
taxcode character(1),
taxdesc character varying(255),
ownercode character(2),
ownerdesc character varying(255),
statecode character(1),
statedesc character varying(255),
usecode character(3),
usedesc character varying(255),
lotarea integer,
homesteadflag character(3),
farmsteadflag character(3),
saledate character(8),
saleprice integer,
salecode character(2),
saledesc character varying(255),
deedbook character varying(50),
deedpage character varying(50),
mabt character(1),
agent character varying(255),
taxfulladdress1 character varying(255),
taxfulladdress2 character varying(255),
taxfulladdress3 character varying(255),
taxfulladdress4 character(5),
changenoticeaddress1 character varying(255),
changenoticeaddress2 character varying(255),
changenoticeaddress3 character varying(255),
changenoticeaddress4 character(5),
countybuilding integer,
countyland integer,
countytotal integer,
countyexemptbldg integer,
localbuilding integer,
localland integer,
localtotal integer,
fairmarketbuilding integer,
fairmarketland integer,
fairmarkettotal integer,
style character(2),
styledesc character varying(255),
stories numeric,
yearblt character(4),
exteriorfinish character(1),
extfinish_desc character varying(255),
roof character(1),
roofdesc character varying(255),
basement character(1),
basementdesc character varying(255),
grade character varying(3),
gradedesc character varying(255),
condition character(1),
conditiondesc character varying(255),
totalrooms integer,
bedrooms integer,
fullbaths integer,
halfbaths integer,
heatingcooling character(1),
heatingcoolingdesc character varying(255),
fireplaces integer,
attachedgarages integer,
finishedlivingarea integer,
cardnumber integer,
alt_id character varying(255),
taxsubcode_desc character varying(255),
taxsubcode character(1)
);
CREATE INDEX parid_idx ON tax_assessments ( parid );
From there we can import the CSV:
COPY tax_assessments FROM 'ALLEGHENY_COUNTY_MASTER_FILE_07022015.csv' WITH CSV HEADER
Here’s an assorted set of some of the fields. You’ll note that these are sorted lexically, not numerically and that the fields are character fields (see above). This is intentional because since I didn’t create this data, I can’t always guarentee there won’t be a change in the various codes later on. See heatingcooling. There is no reason you couldn’t make them integer fields in your own import as applicable.
| ownercode |
ownerdesc |
| 10 |
REGULAR |
| 11 |
REGULAR-ETAL |
| 12 |
REGULAR-ETUX OR ET VIR |
| 13 |
REGULAR-ESTATE |
| 16 |
REGULAR-ETAL & ETUX |
| 18 |
REGULAR-UNFINISHED |
| 20 |
CORPORATION |
| 21 |
CORPORATION-RAILROAD |
| 23 |
CORPORATION-RIGHT-OF-WAY |
| 28 |
CORPORATION-UNFINISHED |
| schoolcode |
schooldesc |
| 1 |
Allegheny Valley |
| 10 |
Clairton City |
| 11 |
Cornell |
| 12 |
Deer Lakes |
| 13 |
Duquesne City |
| 14 |
East Allegheny |
| 16 |
Elizabeth Forward |
| 17 |
Fox Chapel Area |
| 18 |
Monroeville Gateway |
| 2 |
Avonworth |
| 20 |
Hampton Township |
| 21 |
Highlands |
| 22 |
Keystone Oaks |
| 23 |
McKeesport Area |
| 24 |
Montour |
| 25 |
Moon Area |
| 26 |
Mt Lebanon |
| 27 |
North Allegheny |
| 28 |
North Hills |
| 29 |
Northgate |
| 3 |
Pine-Richland |
| 30 |
Penn Hills Twp |
| 31 |
Plum Boro |
| 32 |
Quaker Valley |
| 33 |
Riverview |
| 34 |
Shaler Area |
| 35 |
South Allegheny |
| 36 |
South Fayette Twp |
| 37 |
South Park |
| 38 |
Steel Valley |
| 39 |
Sto-Rox |
| 4 |
Baldwin Whitehall |
| 42 |
Upper St Clair |
| 43 |
West Allegheny |
| 44 |
West Jefferson |
| 45 |
West Mifflin Area |
| 46 |
Wilkinsburg Boro |
| 47 |
City Of Pittsburgh |
| 48 |
McDonald |
| 49 |
Trafford |
| 5 |
Bethel Park |
| 50 |
Norwin |
| 6 |
Brentwood Boro |
| 7 |
Carlynton |
| 8 |
Chartiers Valley |
| 9 |
Woodland Hills |
| usecode |
usedesc |
| 1 |
RES SKELETON RECORD |
| 10 |
SINGLE FAMILY |
| 100 |
VACANT LAND |
| 101 |
GENERAL FARM |
| 102 |
LIVE STOCK FARM |
| 103 |
DAIRY FARM |
| 104 |
POULTRY FARM |
| 105 |
FRUIT & NUT FARM |
| 106 |
VEGETABLE FARM |
| 108 |
NURSERY |
| 109 |
GREENHOUSES, VEG & FLORACULTURE |
| 110 |
>10 ACRES VACANT |
| 111 |
BUILDERS LOT |
| 112 |
LIVESTOCK O/T D & P-CAUV |
| 115 |
FRUIT & NUT FARM – CAUV |
| 118 |
CONDOMINIUM COMMON PROPERTY |
| 120 |
TIMBER OR FOREST LAND |
| 130 |
RIGHT OF WAY – RESIDENTIAL |
| 131 |
RETENTION POND – RESIDENTIAL |
| 190 |
OTHER |
| 199 |
OTHER AGRICULTURAL – CAUV |
| 2 |
COM SKELETON RECORD |
| 20 |
TWO FAMILY |
| 210 |
COAL LAND, SURFACE RIGHTS |
| 220 |
COAL RIGHTS, WORKING INTERESTS |
| 230 |
COAL RIGHTS SEP. ROYALTY INTEREST |
| 240 |
OIL & GAS RIGHTS WORKING INTEREST |
| 260 |
OTHER MINERALS |
| 30 |
THREE FAMILY |
| 300 |
VACANT INDUSTRIAL LAND |
| 310 |
FOOD & DRINK PROCESSING |
| 317 |
FORESTRY WITH BUILDING |
| 320 |
HEAVY MANUFACTURING |
| 330 |
MEDIUM MANUFACTURING |
| 340 |
LIGHT MANUFACTURING |
| 341 |
RECYCLING/SCRAP YARDS |
| 345 |
BULK TRANSFER TERMINAL |
| 350 |
WAREHOUSE |
| 351 |
WAREHOUSE/MULTI-TENANT |
| 352 |
MINI WAREHOUSE |
| 353 |
DISTRIBUTION WAREHOUSE |
| 360 |
INDUSTRIAL TRUCK TERM |
| 370 |
SMALL SHOP |
| 380 |
MINES AND QUARRIES |
| 389 |
INDUSTRIAL/UTILITY |
| 399 |
OTHER |
| 40 |
FOUR FAMILY |
| 400 |
VACANT COMMERCIAL LAND |
| 401 |
APART: 5-19 UNITS |
| 402 |
APART:20-39 UNITS |
| 403 |
APART:40+ UNITS |
| 404 |
RETL/APT’S OVER |
| 405 |
RETL/OFF OVER |
| 406 |
RETL/STOR OVER |
| 409 |
BED & BREAKFAST |
| 410 |
MOTEL & TOURIST CABINS |
| 411 |
HOTELS |
| 412 |
NURSING HOME/PRIVATE HOS |
| 413 |
INDEPENDENT LIVING (SENIORS) |
| 415 |
MOBILE HOMES/TRAILER PKS |
| 416 |
CAMPGROUNDS |
| 418 |
DAYCARE/PRIVATE SCHOOL |
| 419 |
OTHER COMMERCIAL HOUSING |
| 420 |
SMALL DETACHED RET |
| 421 |
SUPERMARKETS |
| 422 |
DISCOUNT STORE |
| 423 |
PHARMACY (CHAIN) |
| 424 |
DEPARTMENT STORE |
| 425 |
NEIGH SHOP CENTER |
| 426 |
COMMUNITY SHOPPING CENTER |
| 427 |
REGIONAL SHOPPING CENTER |
| 429 |
OTHER RETAIL STRUCTURES |
| 430 |
RESTAURANT, CAFET AND/OR BAR |
| 431 |
OFFICE/APARTMENTS OVER |
| 432 |
OFFICE/RETAIL OVER |
| 433 |
OFFICE/STORAGE OVER |
| 434 |
BARS |
| 435 |
DRIVE IN REST OR FOOD SERVICE |
| 437 |
FAST FOOD/DRIVE THRU WINDOW |
| 439 |
OTHER FOOD SERVICE |
| 440 |
DRY CLEANING PLANTS/LAUNDRIES |
| 441 |
FUNERAL HOMES |
| 442 |
MEDICAL CLINICS/OFFICES |
| 444 |
BANK |
| 445 |
SAVINGS AND LOANS |
| 447 |
OFFICE – 1-2 STORIES |
| 448 |
OFFICE-WALKUP -3 + STORIES |
| 449 |
OFFICE-ELEVATOR -3 + STORIES |
| 450 |
CONDOMINIUM OFFICE BUILDING |
| 451 |
FIRE DEPARTMENT/EMS |
| 452 |
AUTO SERV STATION |
| 453 |
CAR WASH |
| 454 |
AUTO SALES & SERVICE |
| 455 |
COMMERCIAL GARAGE |
| 456 |
PARKING GARAGE/LOTS |
| 458 |
GAS STATION KIOSK |
| 460 |
THEATER |
| 461 |
COUNTRY CLUBS |
| 462 |
GOLF DRIVING RANGE/MINIATURE |
| 463 |
GOLF COURSES (PUBLIC) |
| 464 |
BOWLING ALLEYS/REC FACILITY |
| 465 |
LODGE HALL/AMUSEMENT PARK |
| 470 |
DWG USED AS OFFICE |
| 471 |
DWG USED AS RETAIL |
| 472 |
DWG APT CONVERSION |
| 473 |
GROUP HOME |
| 474 |
HEAVY EQUIPMENT SALES/RENTAL |
| 480 |
OFFICE/WAREHOUSE |
| 481 |
OTHER COMMERCIAL |
| 482 |
COMMERCIAL TRUCK TERMINAL |
| 488 |
AIR RIGHTS |
| 489 |
COMMERCIAL/UTILITY |
| 490 |
MARINE SERV FACILITY |
| 491 |
CONVENIENCE STORE |
| 492 |
CONVENIENCE STORE/GAS |
| 493 |
CONVENIENCE STORE GAS/REPAIRS |
| 494 |
BIG BOX RETAIL |
| 496 |
MARINA |
| 499 |
COMM AUX BUILDING |
| 50 |
CONDOMINIUM |
| 500 |
RESIDENTIAL VACANT LAND |
| 501 |
VACANT LAND 0-9 ACRES |
| 517 |
FORESTRY W/BUILDINGS |
| 530 |
RIGHT OF WAY – COMMERCIAL |
| 531 |
RETENTION POND – COMMERCIAL |
| 55 |
COMMON AREA |
| 550 |
CONDOMINIUM UNIT |
| 553 |
H.O.A RECREATIONS AREA |
| 556 |
COMMON AREA OR GREENBELT |
| 557 |
COMM APRTM CONDOS 5-19 UNITS |
| 558 |
COMM APRTM CONDOS 20-39 UNITS |
| 559 |
COMM APRTM CONDOS 40+ UNITS |
| 56 |
CONDO DEVELOPMENTAL LAND |
| 57 |
CONDO GARAGE UNITS |
| 599 |
OTHER RESIDENTIAL STRUCTURE |
| 60 |
TOWNHOUSE |
| 600 |
FEDERAL GOVERNMENT |
| 601 |
HUD PROJ #202 |
| 602 |
HUD PROJ #207/223 |
| 603 |
HUD PROJ #213 |
| 604 |
HUD PROJ #220 |
| 605 |
HUD PROJ #221 |
| 606 |
HUD PROJ #223 |
| 607 |
HUD PROJ #232 |
| 609 |
HUD PROJ #236 |
| 610 |
STATE GOVERNMENT |
| 620 |
COUNTY GOVERNMENT |
| 630 |
TOWNSHIP GOVERNMENT |
| 640 |
MUNICIPAL GOVERNMENT |
| 645 |
OWNED BY METRO HOUSING AU |
| 650 |
OWNED BY BOARD OF EDUCATION |
| 660 |
PUBLIC PARK |
| 670 |
OWNED BY COLLEGE/UNIV/ACADEMY |
| 680 |
CHARITABLE EXEMPTION/HOS/HOMES |
| 685 |
CHURCHES, PUBLIC WORSHIP |
| 690 |
CEMETERY/MONUMENTS |
| 70 |
ROWHOUSE |
| 700 |
COMMUNITY URBAN RENEWAL |
| 710 |
COMMUNITY REINVESTMENT |
| 720 |
MUNICIPAL IMPROVEMENT |
| 730 |
MUNICIPAL URBAN RENEWAL |
| 740 |
OTHER |
| 750 |
CASINO |
| 777 |
INCOME PRODUCING PARKING LOT |
| 80 |
MOBILE HOME |
| 800 |
AGR LAND |
| 810 |
MINERAL LAND |
| 820 |
INDUSTRIAL LAND |
| 830 |
COMMERCIAL LAND |
| 840 |
R.R. – USED IN OPERATION |
| 850 |
R.R. – NOT USED IN OPERATION |
| 860 |
RR-PP – USED IN OPERATION |
| 880 |
P.P. – P.U. – OTHER THAN R.R. |
| 90 |
MOBILE HOME (IN PARK) |
| 96 |
MINOR FIRE DAMAGE |
| 97 |
TOTAL/MAJOR FIRE DAMAGE |
| 98 |
CONDEMNED/BOARDED-UP |
| 99 |
RES AUX BUILDING (NO HOUSE) |
| 996 |
MINOR FIRE DAMAGE – COMM |
| 997 |
TOTAL/MAJOR FIRE DAMAGE – COMM |
| 998 |
TOTAL/MAJOR FIRE DAMAGE – COMM |
| 999 |
UNLOCATED PARCEL |
| style |
styledesc |
| 1 |
RANCH |
| 10 |
MODULAR HOME |
| 11 |
ROW END |
| 12 |
ROW INTERIOR |
| 13 |
MULTI-FAMILY |
| 14 |
VICTORIAN |
| 15 |
OTHER |
| 16 |
OLD STYLE |
| 17 |
LOG CABIN |
| 18 |
BUNGALOW |
| 19 |
TUDOR |
| 2 |
SPLIT LEVEL |
| 20 |
SEMI DETACHED |
| 21 |
CONDO HR |
| 22 |
CONDO GRDN |
| 23 |
CONDO SINGLE |
| 24 |
CONDO END |
| 25 |
CONDO INT |
| 26 |
CONDO PAT/CARG |
| 27 |
CONDO CONV |
| 3 |
BI-LEVEL |
| 4 |
COLONIAL |
| 5 |
CAPE COD |
| 6 |
CONVENTIONAL |
| 7 |
CONTEMPORARY |
| 8 |
CONDO |
| 9 |
TOWNHOUSE |
| M1 |
MANUFACTURED |
| M2 |
MANUFACTURED |
| exteriorfinish |
extfinish_desc |
| 1 |
Frame |
| 2 |
Brick |
| 3 |
Stone |
| 4 |
Stucco |
| 5 |
Concrete Block |
| 6 |
Masonry FRAME |
| 7 |
Concrete |
| 8 |
Log |
| roof |
roofdesc |
| 1 |
SHINGLE |
| 2 |
SLATE |
| 3 |
METAL |
| 4 |
ROLL |
| 5 |
TILE |
| 6 |
RUBBER |
| basement |
basementdesc |
| 1 |
None |
| 2 |
Slab/Piers |
| 3 |
Crawl |
| 4 |
Part |
| 5 |
Full |
| heatingcooling |
heatingcoolingdesc |
| 1 |
None |
| 2 |
Central Heat |
| 3 |
Wall Furnace |
| 4 |
Electric |
| 5 |
Unit Heat |
| 6 |
Heat Pump |
| 7 |
Floor Furnace |
| 8 |
Other |
| A |
No Heat but with AC |
| B |
Central Heat with AC |
| C |
Wall Furnace with AC |
| D |
Electric Heat with AC |
| E |
Unit Heat with AC |
| F |
Heat Pump with AC |
| G |
Floor Furnace with AC |
| H |
Other |
