PostGIS ST_Intersects result is ignored by where condition

Question

I have a simple query filtering by st_intersects function finding all lines intersecting with a polygon.

select *, st_intersect(graph_line, st_geomfromtext('POLYGON((261 156,148 452,333 507,566 199,261 156))') 
from graph_schema.graph_line 
where graph_id=<ID> and st_intersect(graph_line, st_geomfromtext('POLYGON((261 156,148 452,333 507,566 199,261 156))')

The problem is that for some data where a line is just a point the st_intersects returns false even though it intersects. Furthermore even when the result is false it is returned in the result.

Examples:

select st_intersects(st_geomfromtext('LINESTRING(480 270,480 270)')::geometry, st_geomfromtext('POLYGON((261 156,148 452,333 507,566 199,261 156))')::geometry);

returns false, but where condition does not filter it when the first parameter is taken from the table - and even when the object should spatialy intersect.

Is this because of the LINESTRING being just a point? Or what can be the problem?

Answer 1

The input is an invalid geometry, so the output is meaningless.

What you can do is to make the input a valid geometry before using it. ST_MakeValid would fix the faulty geom (making them a point) and would keep the valid ones intact.

select st_intersects(
    st_makeValid(st_setsrid(st_geomfromtext('LINESTRING(480 270,480 270)'),3857)), 
    st_setsrid(st_geomfromtext('POLYGON((261 156,148 452,333 507,566 199,261 156))'),3857)
);
 st_intersects
---------------
 t