Building Permit Data for Roofing, Solar and HVAC Leads

Real Socrata queries for roofing, solar and HVAC permit leads: per-city trade columns, the null-date and dedupe traps, and honest 90-day lead counts.

The obvious way to pull roofing, solar and HVAC leads out of a city permit dataset is to grep the description field for "roof", "solar" or "HVAC". On some portals that quietly costs you most of the market. In Austin, filtering on the structured column permittype = 'MP' (Mechanical) returns 3,098 permits issued in the last 90 days. Searching upper(description) LIKE '%HVAC%' over the same window returns 455. Same dataset, same day, 6.8x difference.

This is a working guide to pulling trade leads out of municipal open data portals. Every number below was re-verified against the live endpoints on 2026-07-20, and every query is one you can paste and re-run. Counts will drift as the portals refresh; the shapes and the traps will not.

The landscape: Socrata and SoQL

Many large US cities publish permits on Socrata. The pattern is always https://{portal}/resource/{datasetId}.json with SoQL query parameters. These ten datasets hold about 6.59M rows between them:

• Austin data.austintexas.gov/3syk-w9eu (2,366,139)
• San Francisco data.sfgov.org/i98e-djp9 (1,292,339)
• NYC DOB NOW Approved Permits data.cityofnewyork.us/rbx6-tga4 (970,971)
• Chicago data.cityofchicago.org/ydr8-5enu (841,641)
• Los Angeles data.lacity.org/pi9x-tg5x (402,055)
• Seattle data.seattle.gov/76t5-zqzr (191,598)
• Cincinnati data.cincinnati-oh.gov/uhjb-xac9 (178,175), Mesa citydata.mesaaz.gov/dzpk-hxfb (155,461), Baton Rouge data.brla.gov/7fq7-8j7r (142,228), Marin County data.marincounty.gov/mkbn-caye (50,131)

Before you write a line of code, read the schema: /api/views/{datasetId}/columns.json gives you every field name and, critically, its dataTypeName. That one call prevents most of the failures below.

Find the structured trade column, not the keyword

The biggest lever is that several cities already classify the trade for you.

These flags are not supersets, so do not treat them as drop-in replacements for the keyword. San Francisco, last 90 days: reroof='Y' returns 751, upper(description) LIKE '%ROOF%' returns 1,153, the intersection is 750, the union is 1,154. Austin is the same story in reverse: 7,288 all-time permits mention SOLAR in the description but are not work_class = 'Auxiliary Power'. The correct selector is the OR of both.

One more San Francisco detail: reroof is never 'N'. It is 'Y' on 117,127 rows and absent on the other 1,175,212, so a reroof != 'N' filter matches everything.

Trap: $order DESC puts the empty rows first

Socrata sorts NULLs first on a DESC order. San Francisco has 64,091 rows with a NULL issued_date. Request $order=issued_date DESC&$limit=1000 with no WHERE clause and you get 1,000 rows, every single one of which has no issue date. The first dated row never appears. A naive "give me the newest 1,000 SF permits" pipeline returns zero usable leads and no error.

The fix is an explicit null guard in $where, not just a date floor:

$where=issued_date IS NOT NULL AND issued_date >= '2026-06-20'

NYC has 278 NULL issue dates, so it degrades rather than fails outright. Add the guard everywhere; it costs nothing.

Trap: permit number is not a primary key

The instinct is to dedupe on permit number plus city. That is wrong in 6 of these 10 datasets. Verified count(x) vs count(distinct x):

• NYC work_permit: 970,759 non-null / 585,351 distinct (about 40% repeats)
• SF permit_number: 1,292,339 / 1,146,791
• Cincinnati permitnum: 178,175 / 160,644
• Marin permit_number: 49,958 / 47,508
• Baton Rouge permitnumber: 142,228 / 141,650
• Mesa permit_number: 155,461 / 155,370
• Genuinely unique: Chicago permit_, Austin permit_number, Seattle permitnum, LA permit_nbr

Use the real surrogate key where one exists: SF record_id, Mesa rowid and Marin unique_id are each fully distinct across every row. NYC has none. Even work_permit || '-' || sequence_number only yields 845,439 distinct values out of 970,971 rows, so hash the whole record or accept the revisions as separate events.

Trap: valuation columns are text, and mostly empty

SF revised_cost and estimated_cost, LA valuation, NYC estimated_job_costs and Cincinnati estprojectcostdec are all declared dataTypeName=text in the column metadata. Sending revised_cost > 50000 returns HTTP 400 with errorCode: query.soql.type-mismatch. The usual workaround is to pull everything and filter in memory. You do not have to, because the cast works server-side:

$where=(valuation::number) > 50000

That returns HTTP 200 and 70,994 rows on LA. The same cast on SF gives 137,386. Raw values in these columns are plain numeric strings with no dollar signs or commas, so the cast is safe.

The bigger problem is that a valuation filter can destroy your lead list. Austin's last 90 days: 14,488 permits, of which 12,080 (83.4%) have a NULL total_job_valuation and another 2,118 are 1 dollar or less. Only about 2% carry a meaningful dollar figure. Of the 447 permits in that window whose description mentions ROOF, 78 are valued at 1 dollar or less. San Francisco has 224,796 all-time rows where (revised_cost::number) <= 1. Any minimum-valuation filter that also drops nulls throws away most of the market.

Trap: a documented field that carries no signal

LA publishes a dedicated solar column. Group by it across all 402,055 rows and you get exactly one bucket: {"solar":"N","count":"402055"}. Every row is N. A solar pipeline built on that column returns nothing, forever. Its ev column is barely better: 43 rows are Y and 402,012 are N. Always run a $select=column,count(*)&$group=column before you trust a flag.

Keyword choice is not a detail

SoQL like is case-sensitive, so wrap the column in upper(). Beyond that, small wording choices move the count by orders of magnitude. Last-90-day LA counts on work_desc: %ROOF% 3,504, %RE-ROOF% 3,051, %REROOF% 4. Drop the hyphen and you lose almost everything. Chicago on work_description: %MECHANICAL% 275, %HVAC% 91, %FURNACE% 37, %AIR COND% 2. Search method matters too: on Seattle's full history, upper(description) LIKE '%SOLAR%' returns 161, the case-sensitive '%solar%' returns 143, and full-text $q=solar returns 150.

%ROOF% also has a false-positive problem: it matches waterproofing, fireproofing and soundproofing, because "proof" contains "roof". In San Francisco's last 90 days, 113 of the 1,153 %ROOF% matches also match %PROOF%, leaving 1,040 clean. NYC has 114 such rows in the same window, LA 28, Austin 10, Seattle 7, Chicago 5. Add AND upper(description) NOT LIKE '%PROOF%' and re-check by hand.

Also know when a city is a dead end. Seattle's dataset is building permits only. Its permittypedesc values are Addition/Alteration (110,840), New (31,865), Demolition (9,332) and similar, with no trade permits at all, and both solar and HVAC return 0 for the last 90 days. Honest 90-day description-keyword yields:

Where the contact data actually lives

Normalizing every city to a lowest-common-denominator schema discards the highest-value columns. Non-null counts verified today: Austin contractor_phone 1,356,460 of 2,366,139 rows (57%), plus contractor_company_name 1,184,927 and applicant_phone 241,572. Mesa carries contractor_phone 28,429, contractor_email 26,719 and a contractor_license column. NYC has owner_name on 959,990 of 970,971 rows (99%) with owner_street_address, owner_city, owner_state and owner_zip_code. Baton Rouge has ownername on 100,031 of 142,228.

Chicago is a special case. Contacts live in slot pairs contact_1_type/contact_1_name through contact_15_*, each with city, state and zipcode fields as well. A loop that stops at slot 12 misses 73 contractor records sitting in slots 13 and 14 (slot 15 has none). Worse, a /contractor/i match over contact_N_type also catches "OWNER AS GENERAL CONTRACTOR" (59,467 rows in slot 1 alone) and "SIGN CONTRACTOR" (29,902), so you end up labelling owner-builders and sign installers as contractors. For a roofing or solar seller the owner-builder may well be the best lead in the file, but classify it as owner-builder, not contractor.

A query you can run right now

curl -sG 'https://data.sfgov.org/resource/i98e-djp9.json' \
  --data-urlencode '$select=record_id,permit_number,issued_date,zipcode,description,revised_cost,reroof' \
  --data-urlencode "\$where=issued_date IS NOT NULL AND issued_date >= '2026-06-20' AND (reroof = 'Y' OR upper(description) LIKE '%ROOF%')" \
  --data-urlencode '$order=issued_date DESC' \
  --data-urlencode '$limit=50000'

Note the escaped \$where: inside double quotes the shell would otherwise expand $where to an empty string. That command returns HTTP 200. Its top row today is a deck rebuild with window waterproofing, not a roof at all, which is the %PROOF% trap in action. Narrow the same query to reroof = 'Y' and the top row looks like this (street address omitted here, it is present in the response):

{
  "record_id": "1753473524537",
  "permit_number": "202606304257",
  "issued_date": "2026-07-17T11:12:35.000",
  "zipcode": "94134",
  "revised_cost": "44000.0",
  "reroof": "Y"
}

The Austin HVAC equivalent swaps the trade clause for permittype = 'MP' and adds contractor_company_name, contractor_phone to $select. The NYC solar equivalent is work_type = 'Solar' with owner_name and the owner mailing address fields.

Tokens, paging and freshness

$limit is not capped at 1,000. Omitting it defaults to 1,000, which is why so many clients page at that size. Socrata's docs cap 2.0 endpoints at 50,000 and set no maximum on 2.1 and 3.0. In practice $limit=50000 and $limit=100000 both returned HTTP 200 with the full row count on Chicago, SF, NYC, Austin and LA. $offset=500000 works fine too. In an overlap test on Chicago using a deliberately non-unique $order key, two adjacent 5,000-row pages shared zero permit numbers, so the classic unstable-pagination failure did not reproduce. Adding a unique tiebreaker to $order still costs nothing.

Rate limits are looser than folklore, but get a token anyway. Forty simultaneous unauthenticated requests to Chicago returned forty HTTP 200s and zero 429s. Socrata exposes no X-RateLimit-* response headers, so client-side budgeting is guesswork. The docs say untokenized IPs may be subject to throttling, and that requests using an application token are not currently throttled unless they are determined to be abusive or malicious. Validate the token before you rely on it: a header of X-App-Token: TOTALLY_INVALID returns HTTP 403 permission_denied on every request, so one typo takes down all ten cities at once.

Portal freshness is not permit freshness. All ten datasets reported a rowsUpdatedAt within hours today, but the newest issue date lags by 0 to 3 days: Austin 2026-07-20, Chicago 07-19, Seattle, LA, Baton Rouge, Mesa and Marin 07-18, SF, NYC and Cincinnati 07-17. Measure it yourself rather than trusting anyone's real-time claim:

curl -s 'https://data.austintexas.gov/resource/3syk-w9eu.json?$select=max(issue_date)'

One last cursor gotcha: SF and Marin declare their issue date as calendar_date but carry a real time component (SF max 2026-07-17T16:45:23, Marin 2026-07-18T17:13:16), and NYC has 358 rows with a non-midnight timestamp. Chicago, Austin, Seattle and LA are strictly midnight. An incremental "since last run" cursor that assumes one shape will either re-pull or skip a day.

Legal and operational reality

All ten datasets carry provenance: official in their view metadata, need no login, and served every request above without any anti-bot friction. Licences differ, though, and you should check the one shown on each dataset page: Austin, Seattle, Baton Rouge and Cincinnati are marked public domain, SF is ODC-PDDL, Marin is ODbL, Chicago and Mesa point at their own terms of use, and LA and NYC list no licence at all.

Separately, permit records being public does not make outreach unrestricted. Owner names and mailing addresses pulled from these files still sit under the FTC's Telemarketing Sales Rule and the National Do Not Call Registry, CAN-SPAM for email, and state telemarketing statutes that are often stricter than the federal baseline. Public availability is not consent to call. None of this is legal advice.

If you would rather not maintain this

None of the above is hard, but it is ten schemas, ten sets of quirks, and a per-city dedupe strategy that drifts whenever a portal changes a column. If you want the normalized output without owning that maintenance, the US building permits scraper on Apify covers these same ten datasets and emits one common schema. If you only need one city, the curl above is genuinely all you need, and you should just go build it.