To be clear, it seems that you want to count routes by county. I assume routes
is a (MULTI)LINESTRING
and counties
is a (MULTI)POLYGON
. You have also indicated that they are both tables use the same SRID (4269).
The 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
, "counties"
, or "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;
The 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.
SELECT count(*) FROM counties GROUP BY counties.name
?ST_Intersection
is not an aggregate function, so even if you PostGIS was finding the relation, I doubt this query would work.