Estimating Owner-Occupied Properties

The Allegheny County Property Assessments dataset provides two primary signs of a parcel being owner-occupied.

1. Whether or not the property receives a homestead exemption. (i.e. when thehomesteadflag column is HOM)
2. Whether or not the property address matches the owner's address (changenoticeaddress[1-4])

Examples

Simple example that filters the assessment dataset to

SELECT *
FROM allegheny_county_parcel_assessments
WHERE homesteadflag = 'HOM'
  --- you need to concat the fields like this to get compatible address formats between the two
  AND propertyaddress || ' ' || propertycity || ' ' || propertystate || ' ' || propertyzip =
      changenoticeaddress1 || ' ' || changenoticeaddress3 || ' ' || changenoticeaddress4

Example SQL from a map that joins a subset of the Assessments dataset ('residential parcels' below) to geogrpahic data for display in a map

SELECT PB.cartodb_id,
       PB.the_geom,
       PB.the_geom_webmercator,
       PB.pin,
       HO.propaddrfull,
       HO.owneraddrfull,
       HO.usedesc
FROM (SELECT parid,
             usecode,
             usedesc,
             address1,
             address3,
             address4,
             changenoticeaddress1,
             changenoticeaddress3,
             changenoticeaddress4,
             homesteadflag,
             address1 || ' ' || address3 || '' || address4 as propaddrfull,
             changenoticeaddress1 || ' ' || changenoticeaddress3 || '' ||
             changenoticeaddress4                          as owneraddrfull
      FROM (
               SELECT *,
                      propertyhousenum || ' ' || propertyfraction || ' ' ||
                      propertyaddress                      as address1,
                      propertycity || ' ' || propertystate as address3,
                      propertyzip                          as address4
               FROM wprdc.assessments
               WHERE class = 'R'
           ) residential_parcels
      WHERE changenoticeaddress1 LIKE address1 || '%'
         OR homesteadflag = 'HOM') HO
         JOIN wprdc.allegheny_county_parcel_boundaries PB
              ON PB.pin = HO.parid