Adopted Highway Segments by Group Type

What follows is the transcript of this chat as State Planner generated it. It is here to show how the answer above was arrived at: every query State Planner ran, what each one returned, and where the data came from.

transcript.md

↑ Contents

State Planner chat · https://stateplanner.com

State: Ohio (OH)
Started: October 21, 2026, 11:43 PM UTC
Runtime: 42 sec
Status: completed

Prompt

Map Ohio's Adopt-A-Highway segments, colored by the kind of group that adopted them. How many segments does each ODOT district have and how many are adopted, and which groups have logged the most pickups and bags of litter?

Model: Anthropic: Claude Haiku 5.5 (anthropic/claude-haiku-5.5, via openrouter)

Response

Adopt-A-Highway segments by ODOT district, status as of the 2026-10-19 load

District	Segments	Adopted	Share adopted	Retired	Cancelled	Non-adoptable	No status
01	273	112	41%	103	2	54	2
02	291	63	22%	188	0	39	1
03	251	85	34%	48	7	109	2
04	154	67	44%	0	1	85	1
05	239	79	33%	95	7	56	2
06	583	181	31%	4	0	384	14
07	369	147	40%	144	19	59	0
08	181	96	53%	10	1	71	3
09	130	13	10%	20	3	94	0
10	340	76	22%	173	39	46	6
11	251	124	49%	41	53	33	0
12	523	184	35%	242	22	73	2
Ohio	3,585	1,227	34%	1,068	154	1,103	33

"Adopted" means segment status is Adopted. Share adopted is adopted divided by all segments in the district, so it includes the non-adoptable and retired ones in the denominator.

Currently adopted segments by group type

Group type	Adopted segments	Groups	Pickups	Bags
Business	175	129	3	785
Service Club	127	98	2	330
Other	113	69	6	841
Fraternity/Sorority	103	51	0	189
Community Service	95	62	0	889
In Memory	76	57	1	842
Family/Friends	72	51	0	98
School	48	38	8	335
Church	35	28	0	288
Youth Group	16	16	0	0
Individual	11	6	0	0
Type not recorded	356	297	15	447

Bags here are per segment as recorded. The pickup and bag figures are the group's own logs, not an audit. Most of the adopted segments are linked to no permit record, so pickups are sparse.

Groups with the most bags logged (all segments, any status)

Group	Type	Segments	Pickups	Bags
In Memory of SPC Aaron Gardner	In Memory	1	0	565
Citizens' Climate Lobby	Community Service	1	0	458
Owens Corning	Business	3	7	241
Muskingum Adult Probation	Other	1	0	205
Meadow View Church of Christ	Church	1	0	166
Covestro	Business	1	0	134
Amazon Web Services	Business	5	0	117
AORD Athens Ohio Roller Derby	Other	3	0	114
Licking Valley Lions Club	Service Club	1	0	105
New Concord United M Church	(blank)	2	3	95

Groups with the most pickups logged (all segments, any status)

Group	Type	Segments	Pickups	Bags
Garver Asset Protection	Business	3	9	41
Shelly & Sands	(blank)	2	8	93
Owens Corning	Business	3	7	241
Hocking College Dental Hygiene Clinic	School	3	5	25
Fairfield Co Democratic Party	Other	3	4	75
Central Ohio Roller Derby	(blank)	1	4	44
Boy Scout #826	(blank)	1	4	18
Wayne Grange	(blank)	1	4	15

Map: adopted segments coloured by group type

Adopted Highway Segments by Group Type

Legend (1,227 adopted segments):

Blue: Business
Green: Community Service
Orange: Service Club
Purple: In Memory
Brown: Other
Pink: Fraternity/Sorority
Red: Family/Friends
Teal: School
Yellow-green: Church
Navy: Youth Group
Light pink: Individual
Grey: type not recorded

Caveats

Pickups are recorded per permit, and only 197 adopted segments carry a permit number. Pickup totals are therefore small and incomplete. Bags are recorded per segment, so the bag totals are the more complete measure.
Group names are free text. A group that appears under two spellings is counted twice, and the ranking tables use the name as typed.
The group-type rows are roughly tallied. The 356 "not recorded" segments are counted separately.

Exploratory Queries

Query 1 — See segment status and group type values

   SELECT segment_status,
          group_type,
          count(*) n,
          count(group_name) named,
          sum(total_num_pick_ups) picks,
          sum(total_num_of_bags) bags
     FROM oh_adopt_a_highway
 GROUP BY 1,
          2
 ORDER BY 3 DESC

segment_status	group_type	n	named	picks	bags
Non-Adoptable Sponsor-A-Highway		693	96
Retired		510	509	33	242
Non-Adoptable Unsafe		401	1
Adopted		356	356	15	447
Adopted	Business	175	175	3	785
Retired	Business	171	171	19	332
Adopted	Service Club	127	127	2	330
Adopted	Other	113	113	6	841
Adopted	Fraternity/Sorority	103	103	0	203
Adopted	Community Service	95	95	0	889
Adopted	In Memory	76	76	1	847
Adopted	Family/Friends	72	72	0	98
Retired	Community Service	60	60	1	29
Cancelled		56	43
Retired	Other	55	55	5	36
Retired	Fraternity/Sorority	53	53
Adopted	School	48	48	8	335
Retired	School	44	44	6	26
Retired	Church	42	42	2	10
Retired	Service Club	41	41	2	26
Retired	In Memory	37	37	2	3
Adopted	Church	35	35	0	288
Cancelled	Business	31	31	2	11
Retired	Family/Friends	30	30	1	0
		27	13

25 out of 47 are listed here.

data/turn-1-query-1.csv

Query 2 — Count segments and adopted segments per ODOT district

   SELECT odot_district,
          count(*) segments,
          count(*) FILTER (
              WHERE group_name IS NOT NULL
                AND group_name <> ''
          ) adopted,
          count(*) FILTER (
              WHERE segment_status ILIKE '%avail%'
          ) available
     FROM oh_adopt_a_highway
 GROUP BY 1
 ORDER BY 1

odot_district	segments	adopted
01	273	218
02	291	251
03	251	147
04	154	84
05	239	184
06	583	272
07	369	310
08	181	107
09	130	36
10	340	280
11	251	219
12	523	445

data/turn-1-query-2.csv

Query 3 — Break district segment counts down by status

   SELECT odot_district,
          count(*) segments,
          count(*) FILTER (
              WHERE segment_status = 'Adopted'
          ) adopted,
          count(*) FILTER (
              WHERE segment_status = 'Retired'
          ) retired,
          count(*) FILTER (
              WHERE segment_status = 'Cancelled'
          ) cancelled,
          count(*) FILTER (
              WHERE segment_status LIKE 'Non-Adoptable%'
          ) non_adoptable,
          count(*) FILTER (
              WHERE segment_status IS NULL
          ) no_status
     FROM oh_adopt_a_highway
 GROUP BY 1
 ORDER BY 1

odot_district	segments	adopted	retired	cancelled	non_adoptable	no_status
01	273	112	103	2	54	2
02	291	63	188	0	39	1
03	251	85	48	7	109	2
04	154	67	0	1	85	1
05	239	79	95	7	56	2
06	583	181	4	0	384	14
07	369	147	144	19	59	0
08	181	96	10	1	71	3
09	130	13	20	3	94	0
10	340	76	173	39	46	6
11	251	124	41	53	33	0
12	523	184	242	22	73	2

data/turn-1-query-3.csv

Query 4 — Check whether pickup counts repeat across segments sharing a permit

   SELECT count(*) segs,
          count(DISTINCT permit_id) permits,
          count(DISTINCT current_permit_number) cur_permits,
          count(DISTINCT group_name) groups,
          count(*) FILTER (
              WHERE permit_id IS NULL
          ) no_permit
     FROM oh_adopt_a_highway
    WHERE segment_status = 'Adopted'

segs	permits	cur_permits	groups	no_permit
1227	197	336	879	1030

data/turn-1-query-4.csv

Query 5 — Check how pickups and bags repeat across segments of one permit

     WITH a AS (
             SELECT *
               FROM oh_adopt_a_highway
              WHERE segment_status = 'Adopted'
          )
   SELECT count(*) FILTER (
              WHERE permit_id IS NOT NULL
          ) segs_with_permit,
          count(DISTINCT permit_id) permits,
          sum(total_num_pick_ups) FILTER (
              WHERE permit_id IS NOT NULL
          ) raw_picks_with_permit,
          (
             SELECT sum(p)
               FROM (
                       SELECT max(total_num_pick_ups) p
                         FROM a
                        WHERE permit_id IS NOT NULL
                     GROUP BY permit_id
                    ) x
          ) dedup_picks,
          sum(total_num_pick_ups) FILTER (
              WHERE permit_id IS NULL
          ) picks_no_permit,
          sum(total_num_of_bags) FILTER (
              WHERE permit_id IS NULL
          ) bags_no_permit,
          (
             SELECT sum(p)
               FROM (
                       SELECT max(total_num_of_bags) p
                         FROM a
                        WHERE permit_id IS NOT NULL
                     GROUP BY permit_id
                    ) x
          ) dedup_bags
     FROM a

segs_with_permit	permits	raw_picks_with_permit	dedup_picks	picks_no_permit	bags_no_permit	dedup_bags
197	197	35	35	0	4606	457

data/turn-1-query-5.csv

Query 6 — See whether bag totals repeat across a group's segments

     WITH a AS (
             SELECT *
               FROM oh_adopt_a_highway
              WHERE segment_status = 'Adopted'
          )
   SELECT count(*) groups,
          sum(segs) segs,
          sum(
          CASE
                    WHEN distinct_bags = 1
                          AND segs > 1 THEN 1
                              ELSE 0
          END
          ) groups_same_bags_multi,
          sum(
          CASE
                    WHEN segs > 1 THEN 1
                    ELSE 0
          END
          ) multi_seg_groups,
          max(segs) max_segs
     FROM (
             SELECT group_name,
                    count(*) segs,
                    count(DISTINCT total_num_of_bags) distinct_bags
               FROM a
           GROUP BY group_name
          ) g

groups	segs	groups_same_bags_multi	multi_seg_groups	max_segs
879	1227	29	163	11

data/turn-1-query-6.csv

Query 7 — Rank groups by bags of litter

     WITH r AS (
             SELECT group_name,
                    group_type,
                    coalesce(permit_id, segment_id) k,
                    segment_status,
                    max(total_num_pick_ups) pk,
                    max(total_num_of_bags) bg,
                    count(*) segs
               FROM oh_adopt_a_highway
              WHERE group_name IS NOT NULL
                AND group_name <> ''
           GROUP BY 1,
                    2,
                    3,
                    4
          ),
          g AS (
             SELECT group_name,
                    max(group_type) gt,
                    string_agg(DISTINCT segment_status, ', ') statuses,
                    sum(segs) segments,
                    sum(coalesce(pk, 0)) picks,
                    sum(coalesce(bg, 0)) bags
               FROM r
           GROUP BY group_name
          )
   SELECT group_name AS "Group",
          gt AS "Type",
          statuses AS "Segment status",
          segments AS "Segments",
          picks AS "Pickups",
          bags AS "Bags"
     FROM g
 ORDER BY bags DESC
    LIMIT 20

Group	Type	Segment status	Segments	Pickups	Bags
In Memory of SPC Aaron Gardner	In Memory	Adopted	1	0	565
Citizens' Climate Lobby	Community Service	Adopted	1	0	458
Owens Corning	Business	Cancelled, Retired	3	7	241
Muskingum Adult Probation	Other	Adopted	1	0	205
Meadow View Church of Christ	Church	Adopted	1	0	166
Covestro	Business	Adopted	1	0	134
Amazon Web Services	Business	Adopted	5	0	117
AORD Athens Ohio Roller Derby	Other	Adopted, Cancelled, Retired	3	0	114
Licking Valley Lions Club	Service Club	Adopted	1	0	105
New Concord United M Church		Retired	2	3	95
Shelly & Sands		Adopted	2	8	93
Memory of Johnathan P Wood	In Memory	Adopted	2	0	92
Judge Drew Canon, Adult Prob.	Other	Adopted	2	1	81
Zane State College Natural Resources	School	Adopted	1	0	81
River View Student Council	School	Adopted	2	1	76
Fairfield Co Democratic Party	Other	Adopted	3	4	75
Friends of 690	Community Service	Adopted	1	0	71
Williamson Committee (Shane & Rona & Ray Ray)	Other	Adopted	1	0	71
Progressive Foam Tech.	Business	Adopted	3	1	62
Oldcastle Infrastructure	Business	Adopted	1	0	59

data/turn-1-query-7.csv

Query 8 — Rank groups by pickups

     WITH r AS (
             SELECT group_name,
                    group_type,
                    coalesce(permit_id, segment_id) k,
                    segment_status,
                    max(total_num_pick_ups) pk,
                    max(total_num_of_bags) bg,
                    count(*) segs
               FROM oh_adopt_a_highway
              WHERE group_name IS NOT NULL
                AND group_name <> ''
           GROUP BY 1,
                    2,
                    3,
                    4
          ),
          g AS (
             SELECT group_name,
                    max(group_type) gt,
                    string_agg(DISTINCT segment_status, ', ') statuses,
                    sum(segs) segments,
                    sum(coalesce(pk, 0)) picks,
                    sum(coalesce(bg, 0)) bags
               FROM r
           GROUP BY group_name
          )
   SELECT group_name AS "Group",
          gt AS "Type",
          statuses AS "Segment status",
          segments AS "Segments",
          picks AS "Pickups",
          bags AS "Bags"
     FROM g
 ORDER BY picks DESC,
          bags DESC
    LIMIT 20

Group	Type	Segment status	Segments	Pickups	Bags
Garver Asset Protection	Business	Adopted, Retired	3	9	41
Shelly & Sands		Adopted	2	8	93
Owens Corning	Business	Cancelled, Retired	3	7	241
Hocking College Dental Hygiene Clinic	School	Cancelled, Retired	3	5	25
Fairfield Co Democratic Party	Other	Adopted	3	4	75
Central Ohio Roller Derby		Retired	1	4	44
Boy Scout #826		Retired	1	4	18
Wayne Grange		Retired	1	4	15
New Concord United M Church		Retired	2	3	95
Mt. Vernon Nazarene Univ. A.D.	School	Adopted	2	3	54
North Valley Bank	Business	Retired	1	3	50
Gamblers MCC	Other	Retired	1	3	22
Cowboy's Caretakers		Retired	1	3	0
The Gels Home		Retired	1	3	0
Lakewood National Honor Society	School	Adopted	2	2	20
Zane State Criminal Justice	School	Adopted	2	2	19
Muskingum Hills Ruritan	Other	Retired	1	2	14
EMerge Real Estate	Business	Cancelled	2	2	11
16:15 Skate Ministry		Retired	1	2	11
Keene UMC	Church	Retired	1	2	10

data/turn-1-query-8.csv

Query 9 — Totals by group type for currently adopted segments

     WITH r AS (
             SELECT group_type,
                    coalesce(permit_id, segment_id) k,
                    segment_status,
                    group_name,
                    max(total_num_pick_ups) pk,
                    max(total_num_of_bags) bg
               FROM oh_adopt_a_highway
              WHERE segment_status = 'Adopted'
           GROUP BY 1,
                    2,
                    3,
                    4
          )
   SELECT coalesce(group_type, '(blank)') AS "Group type",
          count(*) AS "Adopted segments",
          count(DISTINCT group_name) AS "Groups",
          sum(coalesce(pk, 0)) AS "Pickups",
          sum(coalesce(bg, 0)) AS "Bags"
     FROM r
 GROUP BY 1
 ORDER BY "Bags" DESC

Group type	Adopted segments	Groups	Pickups	Bags
Community Service	94	62	0	889
In Memory	73	57	1	842
Other	112	69	6	841
Business	174	129	3	785
(blank)	355	297	15	447
School	48	38	8	335
Service Club	127	98	2	330
Church	35	28	0	288
Fraternity/Sorority	101	51	0	189
Family/Friends	72	51	0	98
Youth Group	16	16	0	0
Individual	11	6	0	0

data/turn-1-query-9.csv

Map Layer Queries

Adopted segments — colored by "group_kind": Business (#1f77b4), Community Service (#2ca02c), Service Club (#ff7f0e), In Memory (#9467bd), Other (#8c564b), Fraternity/Sorority (#e377c2), Family/Friends (#d62728), School (#17becf), Church (#bcbd22), Youth Group (#000080), Individual (#ff99cc), Type not recorded (#555555)

   SELECT geom,
          coalesce(nullif(trim(group_type), ''), 'Not recorded') AS group_kind,
          group_name AS "Group",
          route_type || ' ' || route_nbr AS "Route",
          county AS "County",
          odot_district AS "ODOT district",
          total_num_pick_ups AS "Pickups(permit)",
          total_num_of_bags AS "Bags"
     FROM oh_adopt_a_highway
    WHERE segment_status = 'Adopted'

Picture: maps/turn-1-adopt-a-highway-group-types.png

Used Data Sets

oh_adopt_a_highway — last synced 2026-10-20 · 3,585 rows

data/turn-1-query-1.csv

↑ Contents

segment_status	group_type	n	named	picks	bags
Non-Adoptable Sponsor-A-Highway		693	96
Retired		510	509	33	242
Non-Adoptable Unsafe		401	1
Adopted		356	356	15	447
Adopted	Business	175	175	3	785
Retired	Business	171	171	19	332
Adopted	Service Club	127	127	2	330
Adopted	Other	113	113	6	841
Adopted	Fraternity/Sorority	103	103	0	203
Adopted	Community Service	95	95	0	889
Adopted	In Memory	76	76	1	847
Adopted	Family/Friends	72	72	0	98
Retired	Community Service	60	60	1	29
Cancelled		56	43
Retired	Other	55	55	5	36
Retired	Fraternity/Sorority	53	53
Adopted	School	48	48	8	335
Retired	School	44	44	6	26
Retired	Church	42	42	2	10
Retired	Service Club	41	41	2	26
Retired	In Memory	37	37	2	3
Adopted	Church	35	35	0	288
Cancelled	Business	31	31	2	11
Retired	Family/Friends	30	30	1	0
		27	13
Retired	Youth Group	22	22
Cancelled	Community Service	17	17
Adopted	Youth Group	16	16	0	0
Cancelled	Service Club	14	14
Cancelled	Family/Friends	11	11
Adopted	Individual	11	11
Cancelled	Church	7	7
Cancelled	School	6	6
Cancelled	Other	6	6
Non-Adoptable Construction Zone		5	0
	Community Service	4	4
Cancelled	In Memory	4	4
Non-Adoptable Inmate		2	0
Retired	Other	1	1
Non-Adoptable Sponsor-A-Highway	Other	1	1
Cancelled	Fraternity/Sorority	1	1
Non-Adoptable Unsafe	Youth Group	1	1
Retired	Individual	1	1
	Business	1	1
	Other	1	1
Cancelled	Other	1	1
Retired		1	1

47 rows.

data/turn-1-query-2.csv

↑ Contents

odot_district	segments	adopted
01	273	218
02	291	251
03	251	147
04	154	84
05	239	184
06	583	272
07	369	310
08	181	107
09	130	36
10	340	280
11	251	219
12	523	445

12 rows.

data/turn-1-query-3.csv

↑ Contents

odot_district	segments	adopted	retired	cancelled	non_adoptable	no_status
01	273	112	103	2	54	2
02	291	63	188	0	39	1
03	251	85	48	7	109	2
04	154	67	0	1	85	1
05	239	79	95	7	56	2
06	583	181	4	0	384	14
07	369	147	144	19	59	0
08	181	96	10	1	71	3
09	130	13	20	3	94	0
10	340	76	173	39	46	6
11	251	124	41	53	33	0
12	523	184	242	22	73	2

12 rows.

data/turn-1-query-4.csv

↑ Contents

segs	permits	cur_permits	groups	no_permit
1227	197	336	879	1030

1 row.

data/turn-1-query-5.csv

↑ Contents

segs_with_permit	permits	raw_picks_with_permit	dedup_picks	picks_no_permit	bags_no_permit	dedup_bags
197	197	35	35	0	4606	457

1 row.

data/turn-1-query-6.csv

↑ Contents

groups	segs	groups_same_bags_multi	multi_seg_groups	max_segs
879	1227	29	163	11

1 row.

data/turn-1-query-7.csv

↑ Contents

Group	Type	Segment status	Segments	Pickups	Bags
In Memory of SPC Aaron Gardner	In Memory	Adopted	1	0	565
Citizens' Climate Lobby	Community Service	Adopted	1	0	458
Owens Corning	Business	Cancelled, Retired	3	7	241
Muskingum Adult Probation	Other	Adopted	1	0	205
Meadow View Church of Christ	Church	Adopted	1	0	166
Covestro	Business	Adopted	1	0	134
Amazon Web Services	Business	Adopted	5	0	117
AORD Athens Ohio Roller Derby	Other	Adopted, Cancelled, Retired	3	0	114
Licking Valley Lions Club	Service Club	Adopted	1	0	105
New Concord United M Church		Retired	2	3	95
Shelly & Sands		Adopted	2	8	93
Memory of Johnathan P Wood	In Memory	Adopted	2	0	92
Judge Drew Canon, Adult Prob.	Other	Adopted	2	1	81
Zane State College Natural Resources	School	Adopted	1	0	81
River View Student Council	School	Adopted	2	1	76
Fairfield Co Democratic Party	Other	Adopted	3	4	75
Friends of 690	Community Service	Adopted	1	0	71
Williamson Committee (Shane & Rona & Ray Ray)	Other	Adopted	1	0	71
Progressive Foam Tech.	Business	Adopted	3	1	62
Oldcastle Infrastructure	Business	Adopted	1	0	59

20 rows.

data/turn-1-query-8.csv

↑ Contents

Group	Type	Segment status	Segments	Pickups	Bags
Garver Asset Protection	Business	Adopted, Retired	3	9	41
Shelly & Sands		Adopted	2	8	93
Owens Corning	Business	Cancelled, Retired	3	7	241
Hocking College Dental Hygiene Clinic	School	Cancelled, Retired	3	5	25
Fairfield Co Democratic Party	Other	Adopted	3	4	75
Central Ohio Roller Derby		Retired	1	4	44
Boy Scout #826		Retired	1	4	18
Wayne Grange		Retired	1	4	15
New Concord United M Church		Retired	2	3	95
Mt. Vernon Nazarene Univ. A.D.	School	Adopted	2	3	54
North Valley Bank	Business	Retired	1	3	50
Gamblers MCC	Other	Retired	1	3	22
Cowboy's Caretakers		Retired	1	3	0
The Gels Home		Retired	1	3	0
Lakewood National Honor Society	School	Adopted	2	2	20
Zane State Criminal Justice	School	Adopted	2	2	19
Muskingum Hills Ruritan	Other	Retired	1	2	14
EMerge Real Estate	Business	Cancelled	2	2	11
16:15 Skate Ministry		Retired	1	2	11
Keene UMC	Church	Retired	1	2	10

20 rows.

data/turn-1-query-9.csv

↑ Contents

Group type	Adopted segments	Groups	Pickups	Bags
Community Service	94	62	0	889
In Memory	73	57	1	842
Other	112	69	6	841
Business	174	129	3	785
(blank)	355	297	15	447
School	48	38	8	335
Service Club	127	98	2	330
Church	35	28	0	288
Fraternity/Sorority	101	51	0	189
Family/Friends	72	51	0	98
Youth Group	16	16	0	0
Individual	11	6	0	0

12 rows.

maps/turn-1-adopt-a-highway-group-types.png

↑ Contents

provenance/oh_adopt_a_highway.md

↑ Contents

Provenance: `oh_adopt_a_highway`

This file describes how the rows in PostgreSQL table oh_adopt_a_highway were obtained from an external source: where they were fetched and how fields were renamed and typed. Field meanings are in the mapping table below.

Identity

Table: oh_adopt_a_highway
Family: TIMS
Display name: Adopt A Highway
Source URL: https://tims.dot.state.oh.us/ags/rest/services/Assets/Adopt_A_Highway/MapServer/0
Geometry (source / stored): esriGeometryPolyline / geometry(MultiLineString, 4326)
Update cycle: Daily
OID column: objectid

Pipeline (TIMS family)

Ohio DOT TIMS publishes this layer on the TIMS data page. The catalog behind that page is GET https://tims.dot.state.oh.us/tims/api/glossary. Each entry names a MapServer layer URL.

Data is fetched as GeoJSON from that layer's REST query endpoint (where=1=1, outFields=*, outSR=4326, f=geojson), paginated with resultOffset / resultRecordCount. GeoJSON was chosen over the portal's CSV/SHP/GDB/KMZ exports because it keeps full attribute names, keeps geometry, and loads into PostGIS via ST_GeomFromGeoJSON.

Esri field names become lowercase snake_case PostgreSQL columns (non [a-z0-9_] → _). Dates stored as Esri epoch-milliseconds become timestamptz. Geometry is ST_SetSRID(ST_GeomFromGeoJSON(…), 4326), wrapped in ST_Multi when the table's stored type is Multi*. Esri shape-area / shape-length system fields are dropped.

The same MapServer query is used whenever the table is refreshed.

Field → column mapping

Source field	Column	Esri type	Description
OBJECTID	`objectid`	esriFieldTypeOID
NLFID	`nlfid`	esriFieldTypeString	Network Linear Feature Identifier of the route
CRS	`crs`	esriFieldTypeString	County, route and begin county milepost of route
ODOT_DISTRICT	`odot_district`	esriFieldTypeString	ODOT District Number
COUNTY	`county`	esriFieldTypeString	Full County Name
COUNTY_CD	`county_cd`	esriFieldTypeString	Three Character County Code
ROUTE_TYPE	`route_type`	esriFieldTypeString	Type of route - IR, US, SR, CR, TR, MR, etc
ROUTE_NBR	`route_nbr`	esriFieldTypeString	5 Digit Route Number
ROUTE_SUFFIX	`route_suffix`	esriFieldTypeString	A, B, C, D, I, F, T etc.
CTL_BEGIN_NBR	`ctl_begin_nbr`	esriFieldTypeDouble	County log point of start point
CTL_END_NBR	`ctl_end_nbr`	esriFieldTypeDouble	County log point of end point
STL_BEGIN_NBR	`stl_begin_nbr`	esriFieldTypeDouble	State log point of start point
STL_END_NBR	`stl_end_nbr`	esriFieldTypeDouble	State log point of end point
LATITUDE_DD_BEGIN	`latitude_dd_begin`	esriFieldTypeDouble	Latitude of start point
LATITUDE_DD_END	`latitude_dd_end`	esriFieldTypeDouble	Latitude of end point
LONGITUDE_DD_BEGIN	`longitude_dd_begin`	esriFieldTypeDouble	Longitude of start point
LONGITUDE_DD_END	`longitude_dd_end`	esriFieldTypeDouble	Longitude of end point
PERP_DATE	`perp_date`	esriFieldTypeDate	Date of Linear Referencing System(LRS) highway segment is located against
SEGMENT_STATUS	`segment_status`	esriFieldTypeString	Status of AAH segment
SEGMENT_ID	`segment_id`	esriFieldTypeString	Unique ID for AAH segment
GROUP_NAME	`group_name`	esriFieldTypeString	Name of AAH group
GROUP_TYPE	`group_type`	esriFieldTypeString	Type of AAH group
RECOG_SIGN_STATUS	`recog_sign_status`	esriFieldTypeString	Status of AAH group's recognition sign
GOLD_STAR_GROUP	`gold_star_group`	esriFieldTypeString	AAH group's Gold Star Status
GOLD_STAR_SIGN_STATUS	`gold_star_sign_status`	esriFieldTypeString	Status of AAH group's Gold Star Recognition Sign
DATE_AWARDED_GOLD	`date_awarded_gold`	esriFieldTypeDate	Date that AAH group was awarded Gold Star Status
GROUP_START_DATE	`group_start_date`	esriFieldTypeDate	Date the AAH group started in AAH program
CURRENT_PERMIT_NUMBER	`current_permit_number`	esriFieldTypeString	AAH group's current or most recent permit number
PERMIT_ID	`permit_id`	esriFieldTypeString	Unique ID of record
PERMIT_STATUS	`permit_status`	esriFieldTypeString	Status of AAH group's permit record
PERMIT_ISSUE_DATE	`permit_issue_date`	esriFieldTypeDate	Date the permit was issued
PERMIT_END_DATE	`permit_end_date`	esriFieldTypeDate	Date of permit expiration
PERMIT_COMMENT	`permit_comment`	esriFieldTypeString	Free form comment field for permit
PERMIT_ON_FILE	`permit_on_file`	esriFieldTypeString	Y/N field for if the permit is attached to the permit record
TOTAL_NUM_PICK_UPS	`total_num_pick_ups`	esriFieldTypeInteger	Total number of pickups on file for the current or most recent permit
TOTAL_NUM_OF_BAGS	`total_num_of_bags`	esriFieldTypeInteger	Total number of bags reported for all pickups on file for most recent permit
LAST_PICK_UP_DATE	`last_pick_up_date`	esriFieldTypeDate	Date of the last pickup on file for the permit
(feature geometry)	`geom`	`ST_Multi(ST_SetSRID(ST_GeomFromGeoJSON(…), 4326))`	Feature geometry, WGS84

Geometry

SRID 4326 (WGS84). GIST index on geom.
Empty / null geometries stored as SQL NULL.

Adopted Highway Segments by Group Type

Layers

Data sets used

Ask your own question about Ohio

Transcript

transcript.md

State Planner chat · https://stateplanner.com

Prompt

Response

Exploratory Queries

Query 1 — See segment status and group type values

Query 2 — Count segments and adopted segments per ODOT district

Query 3 — Break district segment counts down by status

Query 4 — Check whether pickup counts repeat across segments sharing a permit

Query 5 — Check how pickups and bags repeat across segments of one permit

Query 6 — See whether bag totals repeat across a group's segments

Query 7 — Rank groups by bags of litter

Query 8 — Rank groups by pickups

Query 9 — Totals by group type for currently adopted segments

Map Layer Queries

Used Data Sets

data/turn-1-query-1.csv

data/turn-1-query-2.csv

data/turn-1-query-3.csv

data/turn-1-query-4.csv

data/turn-1-query-5.csv

data/turn-1-query-6.csv

data/turn-1-query-7.csv

data/turn-1-query-8.csv

data/turn-1-query-9.csv

maps/turn-1-adopt-a-highway-group-types.png

provenance/oh_adopt_a_highway.md

Provenance: `oh_adopt_a_highway`

Identity

Pipeline (TIMS family)

Field → column mapping

Geometry

odot_district	segments	adopted
01	273	218
02	291	251
03	251	147
04	154	84
05	239	184
06	583	272
07	369	310
08	181	107
09	130	36
10	340	280
11	251	219
12	523	445

odot_district	segments	adopted	retired	cancelled	non_adoptable	no_status
01	273	112	103	2	54	2
02	291	63	188	0	39	1
03	251	85	48	7	109	2
04	154	67	0	1	85	1
05	239	79	95	7	56	2
06	583	181	4	0	384	14
07	369	147	144	19	59	0
08	181	96	10	1	71	3
09	130	13	20	3	94	0
10	340	76	173	39	46	6
11	251	124	41	53	33	0
12	523	184	242	22	73	2

odot_district	segments	adopted
01	273	218
02	291	251
03	251	147
04	154	84
05	239	184
06	583	272
07	369	310
08	181	107
09	130	36
10	340	280
11	251	219
12	523	445

odot_district	segments	adopted	retired	cancelled	non_adoptable	no_status
01	273	112	103	2	54	2
02	291	63	188	0	39	1
03	251	85	48	7	109	2
04	154	67	0	1	85	1
05	239	79	95	7	56	2
06	583	181	4	0	384	14
07	369	147	144	19	59	0
08	181	96	10	1	71	3
09	130	13	20	3	94	0
10	340	76	173	39	46	6
11	251	124	41	53	33	0
12	523	184	242	22	73	2

odot_district	segments	adopted
01	273	218
02	291	251
03	251	147
04	154	84
05	239	184
06	583	272
07	369	310
08	181	107
09	130	36
10	340	280
11	251	219
12	523	445

odot_district	segments	adopted	retired	cancelled	non_adoptable	no_status
01	273	112	103	2	54	2
02	291	63	188	0	39	1
03	251	85	48	7	109	2
04	154	67	0	1	85	1
05	239	79	95	7	56	2
06	583	181	4	0	384	14
07	369	147	144	19	59	0
08	181	96	10	1	71	3
09	130	13	20	3	94	0
10	340	76	173	39	46	6
11	251	124	41	53	33	0
12	523	184	242	22	73	2

odot_district	segments	adopted
01	273	218
02	291	251
03	251	147
04	154	84
05	239	184
06	583	272
07	369	310
08	181	107
09	130	36
10	340	280
11	251	219
12	523	445

odot_district	segments	adopted	retired	cancelled	non_adoptable	no_status
01	273	112	103	2	54	2
02	291	63	188	0	39	1
03	251	85	48	7	109	2
04	154	67	0	1	85	1
05	239	79	95	7	56	2
06	583	181	4	0	384	14
07	369	147	144	19	59	0
08	181	96	10	1	71	3
09	130	13	20	3	94	0
10	340	76	173	39	46	6
11	251	124	41	53	33	0
12	523	184	242	22	73	2

Adopted Highway Segments by Group Type

Layers

Data sets used

Ask your own question about Ohio

Transcript

State Planner chat · https://stateplanner.com

Prompt

Response

Exploratory Queries

Query 1 — See segment status and group type values

Query 2 — Count segments and adopted segments per ODOT district

Query 3 — Break district segment counts down by status

Query 4 — Check whether pickup counts repeat across segments sharing a permit

Query 5 — Check how pickups and bags repeat across segments of one permit

Query 6 — See whether bag totals repeat across a group's segments

Query 7 — Rank groups by bags of litter

Query 8 — Rank groups by pickups

Query 9 — Totals by group type for currently adopted segments

Map Layer Queries

Used Data Sets

Provenance: oh_adopt_a_highway

Identity

Pipeline (TIMS family)

Field → column mapping

Geometry

Provenance: `oh_adopt_a_highway`

odot_district	segments	adopted
01	273	218
02	291	251
03	251	147
04	154	84
05	239	184
06	583	272
07	369	310
08	181	107
09	130	36
10	340	280
11	251	219
12	523	445

odot_district	segments	adopted	retired	cancelled	non_adoptable	no_status
01	273	112	103	2	54	2
02	291	63	188	0	39	1
03	251	85	48	7	109	2
04	154	67	0	1	85	1
05	239	79	95	7	56	2
06	583	181	4	0	384	14
07	369	147	144	19	59	0
08	181	96	10	1	71	3
09	130	13	20	3	94	0
10	340	76	173	39	46	6
11	251	124	41	53	33	0
12	523	184	242	22	73	2

odot_district	segments	adopted
01	273	218
02	291	251
03	251	147
04	154	84
05	239	184
06	583	272
07	369	310
08	181	107
09	130	36
10	340	280
11	251	219
12	523	445

odot_district	segments	adopted	retired	cancelled	non_adoptable	no_status
01	273	112	103	2	54	2
02	291	63	188	0	39	1
03	251	85	48	7	109	2
04	154	67	0	1	85	1
05	239	79	95	7	56	2
06	583	181	4	0	384	14
07	369	147	144	19	59	0
08	181	96	10	1	71	3
09	130	13	20	3	94	0
10	340	76	173	39	46	6
11	251	124	41	53	33	0
12	523	184	242	22	73	2