Steve: added wonky property filter as an example

2021-11-23T18:15:24Z

added wonky property filter as an example

New page

===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 <code>residential_parcels</code> is a filtered version of the [https://data.wprdc.org/dataset/allegheny-county-parcel-boundaries1 Allegheny County Property Assessment Data] ( <code>SELECT * FROM assessment_data WHERE class = 'R'</code>)

The table <code>allegheny_county_parcel_boundaries</code> is sourced from [https://data.wprdc.org/dataset/allegheny-county-parcel-boundaries1 its own dataset]. <syntaxhighlight lang="postgresql">
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
</syntaxhighlight>

Useful Parcel Queries - Revision history

Steve: added wonky property filter as an example