[postgis-users] split line at polygon edge

[Rémi Cura]

Hey, in theory st_intersection already uses index and st_intersects under
the hood,
I personally prefer to explicitly add it, as I found it more easy to
understand.

What you didn't take into account is that you may get multiline,
for instance if your polygon is a U, and the line cross it left to right,
the intersection result would be two lines (each in a vertical part of the
U).
If you don't mind having multilinestring :

--------------------------------------
SELECT
ST_Multi(ST_CollectionExtract(ST_Intersection(t.geom,d.geom),2))::geometry(multilinestring,4326)
AS geom
    , t.trails_id, d.id AS trail_system
FROM temp_trails AS t, divisions AS d
WHERE ST_Intersects(t.geom,d.geom) = TRUE;
-------------------------------------
if you want only line, you have to break multilines into simple lines
-------------------------------------
SELECT  row_number() over() as qgis_id, dmp.path AS line_id
    , dmp.geom::geometry(linestring,4326) AS geom
    , t.trails_id, d.id AS trail_system
FROM temp_trails AS t, divisions AS d
    , ST_Dump(ST_CollectionExtract(ST_Intersection(t.geom,d.geom),2)) as dmp
WHERE ST_Intersects(t.geom,d.geom) = TRUE;
--------------------------------------

Cheers,
Rémi-C



2016-03-17 22:17 GMT+01:00 François Hugues <hugues.francois at irstea.fr>:

> Hi,
>
> Empty geometries are returned when there is no intersection and I think we
> forgot something obvious. When you want to intersect geometries you need to
> add WHERE ST_Intersects (a.geom,b.geom).
>
> Things should work better and faster.
>
> HugThanks Remi-C and Hugues for your suggestions, they got me what I
> needed!
>
> I first tried Remi-C's example, since I was curious about how it would turn
> out. It gave me an error mentioning that it could not convert
> GeometryCollection to LineString. This error brought me back to what Hugues
> mentioned. So I used ST_Summary() to verify the GeometryCollections, which
> appeared to be empty (0 elements), and mixed in I noticed the LineStrings,
> MultiLineStrings. Since the Collections seemed to be empty I opted to
> separate out the linestrings I using a function Hugues mentioned
> ST_GeometryType()
>
> Specifically I used:
> ST_GeometryType(geom) like '%Line%'
>
> to get both linestring and multilinestrings.
>
> In the end it took 2 statements, even though I knew someone much more
> proficient then myself could do it in one.
>
> My final statements where:
> #create table public.temp_trail_div1 as select st_intersection(t.geom,
> d.geom) as geom,t.trails_id,
> d.id as trail_system from public.temp_trails as t, public.divisions as d;
>
> #create table public.temp_trail_div_sep as select * from
> public.temp_trail_div1 where ST_GeometryType(geom) like '%Line%';
>
> This seems to have done the trick, for now. Could someone enlighten me on
> how that might be done in one statement?
>
> Thanks again,
> Garret
>
>
> On Thu, Mar 17, 2016 at 5:45 AM, Rémi Cura <remi.cura at gmail.com> wrote:
>
> > Hey,
> > two things :
> > recent version of QGIS are boringly strict about geometry type,
> > so if you want to be able to add the corresponding postgis layer to qgis,
> > you may have to explicitely cast the result.
> > QGIS also require a unique identifier per row,
> > which you can fabricate with row_number() for instance
> > ​​
> > ----------------------------------------------------------------
> > CREATE TABLE my_table AS
> > SELECT row_number() over() AS qgis_unique_id,
> >     st_intersection(t.geom, d.geom)::geometry(linestring,4326) AS geom
> >     ,t.trails_id, d.id
> > FROM public.temp_trails as t, public.polys as d;
> > ----------------------------------------------------------------
> >
> > Cheers,
> > Rémi-C
> >
> > 2016-03-17 8:15 GMT+01:00 François Hugues <hugues.francois at irstea.fr>:
> >
> >> Hello,
> >>
> >>
> >>
> >> Dis you take a look at the query result ? I think you should first try
> >> to see what is the type of geometry returned using ST_GeometryType().
> You
> >> may have some geometrycollections and I’m not sure QGis can handle  it.
> In
> >> this case you could extract lines using ST_CollectionExtract().
> >>
> >>
> >>
> >> To achieve what you want to do, you’ll be able to compare your original
> >> lines table with the result of your query using ST_Difference().
> >>
> >>
> >>
> >> Regards,
> >>
> >>
> >>
> >> Hugues.
> >>
> >>
> >>
> >> *De :* postgis-users [mailto:postgis-users-bounces at lists.osgeo.org] *De
> >> la part de* Garret W
> >> *Envoyé :* jeudi 17 mars 2016 04:11
> >> *À :* postgis-users at lists.osgeo.org
> >> *Objet :* [postgis-users] split line at polygon edge
> >>
> >>
> >>
> >> Hi Ive been looking for a way to take several hundred lines and split
> >> them where they intersect a polygon while also giving them the ID of the
> >> polygon they fall in. Ive seen many posts on splitting polygons. But its
> >> been difficult for me to adapt those examples.
> >>
> >> Ive been able to get an output from this:
> >>
> >> select st_intersection(t.geom, d.geom),t.trails_id, d.id
> >>  from public.temp_trails as t, public.polys as d;
> >>
> >> Its giving me the line and IDs that I wanted but the geom is unreadable
> >> for some reason by QGIS.
> >>
> >> 99.9% of the lines fall within a polygon. Id like to still hang on to
> >> those few lines that arent contained in a polygon. They should just be
> >> split with no ID added
> >>
> >> Im using; postgis 2.2, postgresql 9.5
> >>
> >> Thank you
> >> Garret
> >>
> >> _______________________________________________
> >> postgis-users mailing list
> >> postgis-users at lists.osgeo.org
> >> http://lists.osgeo.org/mailman/listinfo/postgis-users
> >>
> >
> >
> > _______________________________________________
> > postgis-users mailing list
> > postgis-users at lists.osgeo.org
> > http://lists.osgeo.org/mailman/listinfo/postgis-users
> >
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/postgis-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20160318/27380bb0/attachment.html>