Useful Parcel Queries

Filter by likely owner-occupied and join to parcel boundary table for geographies.

This example is from our Carto account, but the source tables are pulled from open data.

The table residential_parcels is a filtered version of the Allegheny County Property Assessment Data ( SELECT * FROM assessment_data WHERE class = 'R')

The table allegheny_county_parcel_boundaries is sourced from its own dataset.

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