9

I have a line and need to get a list of coordinates where the line crosses a polygon. A line could cross multiple polygons. A line could also only cross one edge of polygon, if the line starts inside the polygon.

ST_CROSSES returns a bool indicating if the line crosses but I need the actual coordinates where the line intersects the polygon edge.

The basic SQL I have doesn't get me points:

SELECT trails.oid
FROM trails, areas
WHERE ST_Crosses(trails.geom, areas.geom);
5
14

You need to use ST_Intersection to get the geometries intersection.

But if you intersect a line with a polygon the most probable result will be another line (the piece inside the polygon). So, before using it, you need to transform your polygon geometries in lines. You can use the ST_Boundary() for that.

For lines that intersect the same polygon more than once the intersection will return a Multi-point geometry, you probably want it separated, so you need to use ST_Dump() in the end.

That said, try something like this:

SELECT
    trails.oid,
    (ST_Dump(st_intersection(ST_Boundary(areas.geom),trails.geom))).geom AS points
FROM
    areas, trails
WHERE
    ST_Intersects(areas.geom,trails.geom);

The final ST_Intersects in the WHERE clause makes sure that you don't try to get the intersection of two geometries that don't even touch, and will avoid you getting empty geometries in the end.

3

One way this could be achieved would be by running the following script:

SELECT (st_dumppoints(st_intersection(areas.geom,trails.geom))).geom AS points
FROM areas, trails
WHERE st_intersects(areas.geom,trails.geom) = true;

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.