Philadelphia has one of the better-kept property datasets in the country, and a feature almost no other city offers: the assessment records and the parcel polygons live in the same queryable database. You can join them on the server and get finished geometry back in a single HTTP request.
That matters because the raw assessment table is points. Points are fine for a dot map and bad for everything else — you cannot colour a lot, and a point that lands in a back yard matches no building at all. Everywhere else we work, fixing that means two requests and a join in your own code. In Philadelphia it is one line of SQL.
| Table | What it holds |
|---|---|
opa_properties_public |
583,680 assessment records as points, with
year_built, category_code_description and
parcel_number. 540,073 carry a usable year — about
93%. |
pwd_parcels |
547,413 parcel polygons from the Water Department, keyed by
brt_id. |
The join key is the same number under two names: OPA's
parcel_number is PWD's brt_id. It matches well —
544,506 of the 547,413 parcels find an assessment record, about 99.5%.
Both tables sit behind Philadelphia's Carto SQL API, which takes ordinary PostGIS:
https://phl.carto.com/api/v2/sql?q=
SELECT o.year_built,
o.category_code_description AS cat,
ST_AsGeoJSON(p.the_geom) AS g
FROM pwd_parcels p
JOIN opa_properties_public o
ON p.brt_id = o.parcel_number
WHERE p.the_geom && ST_MakeEnvelope(-75.17, 39.94, -75.14, 39.96, 4326)
LIMIT 5000That returns parcel polygons with a year and a use description already attached. Around 3,000 Center City parcels come back in under two seconds, and 93.4% of them carry a year.
Two refinements worth adding:
ST_Simplify(p.the_geom, 0.00002) roughly halves the payload.
Keep the tolerance small — Philadelphia row lots are narrow, and generalising
at 9 m distorts them enough that points stop landing inside. We measured that
exact failure on a comparable grid: matching fell from 77% to 61%.LIMIT … OFFSET … for paging. The &&
operator is a bounding-box overlap test that uses the spatial index, which is
why this stays fast; ST_Intersects is stricter and slower, and
for a rectangle you rarely need it.category_code_description needs no lookup table:
| Category | Records |
|---|---|
| SINGLE FAMILY | 462,956 |
| MULTI FAMILY | 41,327 |
| VACANT LAND | 39,893 |
| MIXED USE | 14,163 |
| COMMERCIAL | 12,505 |
| APARTMENTS > 4 UNITS | 4,052 |
| INDUSTRIAL | 3,842 |
| VACANT LAND - RESIDENTIAL | 2,231 |
| GARAGE - RESIDENTIAL | 1,158 |
| OFFICES | 909 |
| VACANT LAND - NON-RESIDENTIAL | 204 |
| GARAGE - COMMERCIAL | 126 |
| HOTEL | 109 |
| SPECIAL PURPOSE | 102 |
Philadelphia is a rowhouse city and the numbers say so: single family alone is 79% of all records. If you colour by category without grouping, you get a monochrome drawing.
A warning from our own mistake. We first built the category mapping from a
Center City sample, saw twelve values, and shipped it. The two that the sample
missed — VACANT LAND - RESIDENTIAL and
GARAGE - RESIDENTIAL, 3,389 records between them — silently fell
through as "unknown" until we counted the full table. Enumerate categories
from the whole dataset, not from the neighbourhood you happen to be testing.
year_built runs from 1700 to 2028 in the current table.
The upper end is either a permit-date artefact or a typo, but either way a
building completed two years from now will stretch a colour ramp built on
min/max. Clamp to a plausible window and treat the rest as unknown.
Measured on our own database inside a half-mile circle on Center City, 88.4% of buildings carry a year built and 92.3% carry a use — a strong result, and one that took less code than any other city because of the server-side join.
If you want the finished drawing instead, sitedia runs this query for you and returns building age and land use alongside footprints, heights, green space, transit, terrain and aerial imagery, as SVG, PNG, DXF or 3D. The equivalent walkthroughs for other cities are NYC PLUTO and MassGIS parcels, and every dataset with its measured coverage is listed on the data sources page.