Wprdc

WPRDC - Pittsburgh Regional Data Center

Query 363+ datasets from the Western PA Regional Data Center. Property assessments, air quality, 311 requests, jail census, overdose data, parking, permits, violations — all queryable via SQL without downloading.

Quick Start

# Search for datasets
<skill>/wprdc.py search "property sales"
<skill>/wprdc.py search "air quality" --org allegheny-county

# Get dataset info
<skill>/wprdc.py info property-assessments

# List resources (tables) in a dataset
<skill>/wprdc.py resources property-assessments

# See field schema
<skill>/wprdc.py fields assessments

# SQL query (the killer feature!)
<skill>/wprdc.py query 'SELECT "PARID", "PROPERTYADDRESS" FROM @assessments WHERE "PROPERTYCITY"='"'"'PITTSBURGH'"'"' LIMIT 5'

# Quick parcel lookup
<skill>/wprdc.py parcel 0028F00194000000

# Download a dataset
<skill>/wprdc.py download property-assessments --format csv

Commands

search <query>

Search for datasets by keyword.

Options:

• --org <name> — Filter by organization (e.g., allegheny-county, city-of-pittsburgh)
• --group <name> — Filter by topic group (e.g., health, housing-properties)
• --limit <n> — Max results (default: 10)
• --json — Raw JSON output

info <dataset>

Get detailed information about a dataset, including description, resources, and metadata.

resources <dataset>

List all resources (tables/files) in a dataset with their IDs and queryability status.

fields <resource>

Show the field schema for a resource. Use shortcut names or resource IDs.

query <sql>

Execute SQL queries against live data. This is the power feature!

Important: Column names must be double-quoted because PostgreSQL is case-sensitive:

SELECT "PARID", "PROPERTYADDRESS" FROM @assessments WHERE "PROPERTYCITY"='PITTSBURGH' LIMIT 5

Use @shortcut notation for common tables (see Shortcuts below).

Options:

• --json — Raw JSON output
• --table — Format as ASCII table

parcel <pin>

Quick property lookup by parcel ID. Returns address, assessments, building info, and last sale.

<skill>/wprdc.py parcel 0028F00194000000

download <dataset>

Download a resource to a file.

Options:

• --resource <id|name> — Specific resource
• --format <csv|json|geojson> — Preferred format
• --output <path> — Output filename

orgs

List all organizations publishing data.

groups

List all topic groups (categories).

shortcuts

Show available query shortcuts.

Query Shortcuts

Use @shortcut in SQL queries instead of long resource IDs:

Example:

<skill>/wprdc.py query 'SELECT * FROM @overdoses WHERE "death_year"=2024 LIMIT 10'

Before Querying, Ask Yourself

1. Scope: Is this City of Pittsburgh only, or all of Allegheny County?

   • PLI violations, 311, permits → City of Pittsburgh only (90 neighborhoods)
   • Property assessments, overdoses, jail → All of Allegheny County (130 municipalities)

2. Freshness: When was this dataset last updated? Run info <dataset> first.

3. Fields: What columns exist? Run fields <resource> before writing SQL.

4. Size: How many records? Start with LIMIT 10, expand once you know it works.

NEVER Do

• NEVER use CAST(), ROUND(), AVG(), or other SQL functions — WPRDC blocks them. You'll get "Access denied: Not authorized to call function". Do aggregation client-side.

• NEVER query without LIMIT on large tables — Assessments has 584K rows. Queries timeout. Always add LIMIT.

• NEVER assume county-wide coverage for City datasets — PLI violations, 311, permits are City of Pittsburgh only. Aspinwall, Fox Chapel, Mt. Lebanon = separate municipalities, not in the data.

• NEVER trust "under maintenance" datasets — County plumbing inspections, housing inspections, food facilities are currently broken. Check info first.

• NEVER forget column quoting — UPPERCASE columns need double quotes ("PARID"), lowercase don't (case_year). Wrong quoting = cryptic "column does not exist" errors.

• NEVER use wildcards on unindexed text — LIKE '%something%' on large tables will timeout. Be specific.

Common Errors

SQL Tips

1. Quote UPPERCASE column names — PostgreSQL is case-sensitive:

   SELECT "PROPERTYADDRESS" FROM @assessments  -- ✓ uppercase needs quotes
   SELECT case_year FROM @overdoses            -- ✓ lowercase works without quotes
   

2. GROUP BY works, but not aggregate functions:

   SELECT "PROPERTYCITY", COUNT(*) as cnt 
   FROM @assessments 
   GROUP BY "PROPERTYCITY" 
   ORDER BY cnt DESC 
   LIMIT 10
   

3. Check fields first — Column names vary wildly between datasets

Coverage Map (Critical!)

If someone asks about Fox Chapel, Aspinwall, Mt. Lebanon, etc. → Only county-wide datasets apply. No PLI/311 data for suburbs.

Organizations & Topics

Use orgs and groups commands to explore. Major publishers:

• allegheny-county (143 datasets) — assessments, health, jail
• city-of-pittsburgh (126 datasets) — 311, permits, violations

Example Queries

# Property lookup by parcel ID
<skill>/wprdc.py parcel 0028F00194000000

# Search by address (use SQL)
<skill>/wprdc.py query 'SELECT * FROM @assessments WHERE "PROPERTYHOUSENUM"='"'"'251'"'"' AND "PROPERTYADDRESS" LIKE '"'"'%PASADENA%'"'"''

# Overdose trends by year
<skill>/wprdc.py query 'SELECT case_year, COUNT(*) as deaths FROM @overdoses GROUP BY case_year ORDER BY case_year'

# Filter by neighborhood (City of Pittsburgh only)
<skill>/wprdc.py query 'SELECT "VIOLATION", COUNT(*) FROM @violations WHERE "NEIGHBORHOOD"='"'"'Hazelwood'"'"' GROUP BY "VIOLATION" ORDER BY COUNT(*) DESC LIMIT 10'

# Cross-tab query
<skill>/wprdc.py query 'SELECT combined_od1, race, COUNT(*) FROM @overdoses GROUP BY combined_od1, race ORDER BY COUNT(*) DESC LIMIT 20'

Known Issues (as of Jan 2026)

• 311 Data stopped updating Feb 2025 — new system transition
• County Plumbing Inspections — under maintenance
• County Housing Inspections — under maintenance
• County Food Facilities — under maintenance

Always run info <dataset> to check last update date before relying on data.