0

I want to get id, time and point_metric columns from intersection between two geometry(geom_lines and gem_houses). I wrote following sql code, however it fore an error (argument of WHERE must be type boolean, not type geometry). I searched and knew I must use "ST_Intersection" instead of "ST_Intersects" operator. But, "ST_Intersects" only get boolean whilst I need geometry, so I have to use "ST_Intersection", I think. Dose anybody have an idea?

SELECT id, time, ST_AsText(geom_houses) FROM my_table
where ST_Intersection(geom_lines , geom_houses);

1 Answer 1

2

If you want the intersection, that should be one of the things you SELECT. If you only want rows where there is an intersection, that goes in the WHERE.

So you probably wanted (no test, because you didn't provide the full table schema) something like:

SELECT id, time, ST_AsText(geom_houses), ST_AsText(ST_Intersection(geom_line, geom_houses)) FROM my_table
WHERE ST_Intersects(geom_lines, geom_houses);
2
  • I have a similar question and found this answer useful. I hope it's okay to ask for a small clarification rather than creating a duplicate question. What does my_table mean here? We only have two geometries namely geom_houses and geom_line. So is it a new geometry table for the resulting geometry?
    – rm167
    Feb 16, 2022 at 11:37
  • If you have two separate tables, and you want to intersect one geometry from each, you'll need a query that selects the geometry from each table. That is more about the SELECT than about the ST_Intersects (which is basically the same, once you have the geometries).
    – BradHards
    Feb 17, 2022 at 0:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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