How to use intersection in spatial guery in postgresql?

Question

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);

Answer 1

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);