transcript.md
↑ ContentsState Planner chat · https://stateplanner.com
- State: Ohio (OH)
- Started: October 27, 2026, 1:35 PM UTC
- Runtime: 36 sec
- Status: completed
Prompt
ODOT Geotechnical Bore Hole Locations by County
Model: Anthropic: Claude Opus 5.5 (anthropic/claude-opus-5.5, via openrouter)
Response
The county name field is inconsistent (mixed case, abbreviations, typos), so I'll assign each boring to a county by its location instead.
ODOT geotechnical bore holes by county
The table holds 277,379 ODOT test borings, going back to the 1920s. Its county name field is messy, with mixed case, abbreviations like "FRA" and "HAM", and typos such as "HAMITON". To get clean counts, I placed each boring in a county by where it sits on the map, using ODOT's county outlines. That covers 277,204 borings. The other 175 fall just outside the state line, mostly along the Ohio River and Lake Erie.
Map legend: each county is shaded by its number of borings. The darkest purple is 8,000 or more, then 4,000–7,999, 2,500–3,999, 1,500–2,499, and the lightest pink is under 1,500. Click a county to see its name, ODOT district, count and borings per square mile.
Top 15 counties by number of bore holes
| County | ODOT District | Bore holes | Per sq mi |
|---|---|---|---|
| Franklin | 6 | 13,772 | 25.3 |
| Hamilton | 8 | 11,554 | 28.0 |
| Summit | 4 | 11,388 | 27.1 |
| Cuyahoga | 12 | 9,490 | 20.7 |
| Montgomery | 7 | 7,108 | 15.3 |
| Lucas | 2 | 6,040 | 17.4 |
| Guernsey | 5 | 5,551 | 10.5 |
| Athens | 10 | 5,540 | 10.9 |
| Belmont | 11 | 5,475 | 10.1 |
| Licking | 5 | 5,408 | 7.9 |
| Stark | 4 | 5,372 | 9.3 |
| Muskingum | 5 | 5,296 | 7.9 |
| Mahoning | 4 | 5,057 | 11.9 |
| Washington | 10 | 4,866 | 7.6 |
| Portage | 4 | 4,522 | 9.0 |
What the counts show
- Big urban counties have the most borings. Franklin, Hamilton, Summit and Cuyahoga each have about 9,500–13,800 borings, or 20–28 per square mile. Lake County is small but also dense, at 19.4 per square mile.
- Several hilly southeastern counties also rank high. Guernsey, Athens, Belmont, Muskingum and Washington each have about 4,900–5,600.
- Northwest and west-central Ohio have the fewest. Fulton has 250 (0.6 per square mile), Putnam 647 and Carroll 729. Most of the flat western counties have about 1,100–1,600.
- Middle of the list: half the counties have about 2,000–3,600 borings. You can click any county on the map for its figures, or I can list all 88.
Notes
- A boring here is a recorded drilling location. Many projects drilled several borings close together, so the counts show how much drilling was done, not how many separate projects there were.
- Each record has links to its project information, boring log, plan and profile documents. I can map the individual borings for one county or along one route if you want to look at a specific site.
Exploratory Queries
Query 1 — Count bore holes by county as recorded.
SELECT county,
county_cd,
odot_district,
count(*) n
FROM oh_geotech_bore_hole_locations
GROUP BY 1,
2,
3
ORDER BY n DESC
| county | county_cd | odot_district | n |
|---|---|---|---|
| FRANKLIN | FRA | 06 | 13502 |
| SUMMIT | SUM | 04 | 11272 |
| HAMILTON | HAM | 08 | 11253 |
| CUYAHOGA | CUY | 12 | 9473 |
| MONTGOMERY | MOT | 07 | 6940 |
| LUCAS | LUC | 02 | 5972 |
| ATHENS | ATH | 10 | 5495 |
| GUERNSEY | GUE | 05 | 5410 |
| LICKING | LIC | 05 | 5402 |
| BELMONT | BEL | 11 | 5359 |
| STARK | STA | 04 | 5345 |
| MUSKINGUM | MUS | 05 | 5260 |
| MAHONING | MAH | 04 | 5025 |
| WASHINGTON | WAS | 10 | 4849 |
| LAKE | LAK | 12 | 4493 |
| PORTAGE | POR | 04 | 4478 |
| RICHLAND | RIC | 03 | 4419 |
| TRUMBULL | TRU | 04 | 4405 |
| SCIOTO | SCI | 09 | 4287 |
| TUSCARAWAS | TUS | 11 | 4274 |
| LORAIN | LOR | 03 | 4008 |
| MEDINA | MED | 03 | 4007 |
| CLARK | CLA | 07 | 3662 |
| BUTLER | BUT | 08 | 3650 |
| ASHTABULA | ATB | 04 | 3620 |
25 out of 244 are listed here.
Query 2 — Count bore holes inside each county polygon, with density.
WITH c AS (
SELECT c.county,
c.odot_district,
c.area_sqmi,
s.n
FROM oh_county c
CROSS JOIN LATERAL (
SELECT count(*) n
FROM oh_geotech_bore_hole_locations b
WHERE ST_Intersects(b.geom, c.geom)
) s
)
SELECT county,
odot_district,
n,
round((n / area_sqmi)::numeric, 1) per_sqmi,
(
SELECT count(*)
FROM oh_geotech_bore_hole_locations
) total,
(
SELECT count(*)
FROM oh_geotech_bore_hole_locations
WHERE geom IS NULL
) nullgeom,
sum(n) OVER () assigned
FROM c
ORDER BY n DESC
| county | odot_district | n | per_sqmi | total | nullgeom | assigned |
|---|---|---|---|---|---|---|
| FRANKLIN | 6 | 13772 | 25.3 | 277379 | 0 | 277204 |
| HAMILTON | 8 | 11554 | 28.0 | 277379 | 0 | 277204 |
| SUMMIT | 4 | 11388 | 27.1 | 277379 | 0 | 277204 |
| CUYAHOGA | 12 | 9490 | 20.7 | 277379 | 0 | 277204 |
| MONTGOMERY | 7 | 7108 | 15.3 | 277379 | 0 | 277204 |
| LUCAS | 2 | 6040 | 17.4 | 277379 | 0 | 277204 |
| GUERNSEY | 5 | 5551 | 10.5 | 277379 | 0 | 277204 |
| ATHENS | 10 | 5540 | 10.9 | 277379 | 0 | 277204 |
| BELMONT | 11 | 5475 | 10.1 | 277379 | 0 | 277204 |
| LICKING | 5 | 5408 | 7.9 | 277379 | 0 | 277204 |
| STARK | 4 | 5372 | 9.3 | 277379 | 0 | 277204 |
| MUSKINGUM | 5 | 5296 | 7.9 | 277379 | 0 | 277204 |
| MAHONING | 4 | 5057 | 11.9 | 277379 | 0 | 277204 |
| WASHINGTON | 10 | 4866 | 7.6 | 277379 | 0 | 277204 |
| PORTAGE | 4 | 4522 | 9.0 | 277379 | 0 | 277204 |
| LAKE | 12 | 4493 | 19.4 | 277379 | 0 | 277204 |
| RICHLAND | 3 | 4453 | 8.9 | 277379 | 0 | 277204 |
| TRUMBULL | 4 | 4439 | 7.0 | 277379 | 0 | 277204 |
| SCIOTO | 9 | 4331 | 7.0 | 277379 | 0 | 277204 |
| TUSCARAWAS | 11 | 4281 | 7.5 | 277379 | 0 | 277204 |
| LORAIN | 3 | 4130 | 8.4 | 277379 | 0 | 277204 |
| MEDINA | 3 | 4088 | 9.7 | 277379 | 0 | 277204 |
| BUTLER | 8 | 3806 | 8.1 | 277379 | 0 | 277204 |
| CLARK | 7 | 3740 | 9.3 | 277379 | 0 | 277204 |
| ASHTABULA | 4 | 3633 | 5.1 | 277379 | 0 | 277204 |
25 out of 88 are listed here.
Map Layer Queries
Bore holes per county — colored by "cls": 8,000+ borings (#7a0177), 4,000–7,999 (#c51b8a), 2,500–3,999 (#f768a1), 1,500–2,499 (#fbb4b9), Under 1,500 (#feebe2)
SELECT c.geom,
initcap(c.county) AS "County",
c.odot_district AS "ODOT District",
s.n AS "Bore holes",
round((s.n / c.area_sqmi)::numeric, 1) AS "Per sq mi",
CASE
WHEN s.n >= 8000 THEN 'a'
WHEN s.n >= 4000 THEN 'b'
WHEN s.n >= 2500 THEN 'c'
WHEN s.n >= 1500 THEN 'd'
ELSE 'e'
END AS cls
FROM oh_county c
CROSS JOIN LATERAL (
SELECT count(*) n
FROM oh_geotech_bore_hole_locations b
WHERE ST_Intersects(b.geom, c.geom)
) s
Picture: maps/turn-1-bore-holes-by-county.png
Used Data Sets
oh_county— last synced 2026-09-27 · 88 rowsoh_geotech_bore_hole_locations— last synced 2026-10-04 · 277,379 rows