3

I have a line and polygon: enter image description here

I would like to programmatically figure out whether they intersect or not. So, I do:

select ST_Intersects (
    (
        select ST_SetSRID(ST_Buffer(ST_Transform(ST_SetSRID((p.geom), 4326), 26986), 50), 4326)
        from point p
        where p.id = 25785
    )
    ,
    (
        select ST_SetSRID(ST_MakeLine(ST_SetSRID(p.geom, 4326)), 4326)
        FROM point p
        where p.trajectory_id = 1455
    )
);

But when I run the query, PostGIS says that they do not intersect!! But on the picture above we definitely see that they intersect (or line crosses the polygon).

enter image description here

What is the problem?

I also tried ST_Crosses function, but it also gives false.

  • 3
    Could you maybe post the output of the above geometry creation queries using ST_AsText. For what it is worth, you don't need to set the SRID twice, as in 2nd subquery, not that it matters, just redundant. – John Powell Oct 18 '15 at 15:20
  • If you would like to create line from a table of points a group by clause should be used, and st_makeline as an aggregate. A minor problem is that the order of the points in the line is not defined. – Zoltan Oct 18 '15 at 20:38
  • Can you confirm your ST_MakeLine clause is working? Is it the messy blue line in you map? Also, there are many questions on GIS SE apparently involving ST_Buffer that actually are far better expressed using the ST_DWithin function. Investigate a few Qs here about ST_DWithin. Finally, i agree: you seem to have too many instances of ST_SetSRID, making your query hard to manually parse. – Martin F Oct 18 '15 at 22:00
7

Here is a proposed cleaned-up code. Assuming you need to input the buffer size in the local unit of "EPSG:26986".

To simplify a bit, I propose to intersect geometry directly into the local SRID. This make the query more generic because it will work no matter what is the input SRID of the geometry (if its correctly defined!).

And if the SRID (4326) is already set up in the point table meta-data you could probably drop all the ST_SetSRID() functions because they aren't doing anything and they just make the function less reusable.

select ST_Intersects (
    (
        select ST_Buffer(ST_Transform(ST_SetSRID(p.geom,4326),26986),50))
        from point p
        where p.id = 25785
    )
    ,
    (
        select ST_MakeLine(ST_Transform(ST_SetSRID(p.geom,4326),26986))
        FROM point p
        where p.trajectory_id = 1455
        ORDER BY id
    )
);

Please also notice that you must add an ORDER BY clause to your second query in order to make a clean path object. On your snapshot we can clearly see that there is a false straight line. Hence this could cause false positive later-on in your algorithm. I reused the field id but maybe there is a more appropriate one depending on your data.

5

That first ST_SetSRID in your subquerry should be ST_Transform.

With ST_SetSRID you just assign new SRID to the geometry without actually transforming your data into the new SRID.

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.