To be clear, it seems that you want to count routes by county. I assume
routes is a
counties is a
(MULTI)POLYGON. You have also indicated that they are both tables use the same SRID (4269).
ERROR: relation "counties" does not exist is odd, but you say in the comments that
SELECT count(*), name FROM public."Counties" group by name; is working. Are you running it exactly that way? Because if so, it looks like you defined the table using an upper case quoted identifier for
"Counties", which will not match
"COUNTIES". In general, Postgres developers will avoid using upper case letters in identifier names for exactly this reason, in order to avoid having to exactly match case in double-quoted identifiers.
Assuming that's the issue, you should be able to get the count you want using:
SELECT counties.name, COUNT(*) AS route_count
FROM counties JOIN routes
ON ST_Intersects(counties.geom, routes.geog1)
GROUP BY counties.name;
ST_Intersection() function you had in your original query is not an aggregate function, and therefore can't be used in the select list if you are using
GROUP BY. If you actually want geometries of intersected routes in the result set, that is a different query.
Are routes completely contained by counties? If any routes cross a county border, they will be counted in both intersected counties. But switching to something like
ST_Contains will not count them if they cross into neighboring county. Pick your poison.