1

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!

  • if counties table is in another schema than public, then you need to specify your_schema.counties in the from clause. (or alter your search_path) – WKT May 13 '16 at 19:05
  • no they are both in public – ziggy May 13 '16 at 19:07
  • What is result of SELECT count(*) FROM counties GROUP BY counties.name? – Lee Hachadoorian May 13 '16 at 20:09
  • Also, ST_Intersection is not an aggregate function, so even if you PostGIS was finding the relation, I doubt this query would work. – Lee Hachadoorian May 13 '16 at 20:10
  • 1
    I would undo that edit. I'm working on an answer. – Lee Hachadoorian May 13 '16 at 20:45
0

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.

  • okay thanks I am going to try this out. my routes table is not a linestring: it is has two separate geometry points (start and end) in the same table. Is that bad practice to have both start and end points in one table? – ziggy May 13 '16 at 21:02
  • 1
    The practice should be whatever you can apply consistently. To me the name 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. – Lee Hachadoorian May 13 '16 at 21:07
  • updated the SRID, did you query it worked!! literally I can't thank you enough been pounding at this for a while – ziggy May 13 '16 at 21:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.