Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It only takes a minute to sign up.
Sign up to join this community
I am trying to use the ST_Intersection function to intersect points by county and find the counts by county, but I am having no luck.
SELECT counties.name,
FROM counties INNER JOIN routes
ON ST_Intersects(counties.geom, routes.geog1)
group by counties.name;
The error is telling me: relation "counties" does not exist.
Can anyone help me fix my query. Should I be using the st_contains function?
*2nd day working with posgres/postgis so bear with me please!
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.
routes implies a linestring rather than a point. I guess you're using something like geog1 as a start point and geog2 as an endpoint? The query I wrote should still work. You might change to ST_Contains because I think ST_Intersects would count a point on a county border as intersecting both neighboring polygons. You should change your question to specify routes.geog1 is a POINT geometry.
May 13, 2016 at 21:07
SELECT count(*) FROM counties GROUP BY counties.name?ST_Intersectionis not an aggregate function, so even if you PostGIS was finding the relation, I doubt this query would work.