[postgis-users] split line at polygon edge

[Rémi Cura]

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
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20160317/35f776b4/attachment.html>