1

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.

enter image description here

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

2

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

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.