Re: [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 :

> 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  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 :
> >
> >> 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-boun...@lists.osgeo.org] *De
> >> la part de* Garret W
> >> *Envoyé :* jeudi 17 mars 2016 04:11
> >> *À :* postgis-users@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

Re: [postgis-users] split line at polygon edge

[François Hugues]

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  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 :
>
>> 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-boun...@lists.osgeo.org] *De
>> la part de* Garret W
>> *Envoyé :* jeudi 17 mars 2016 04:11
>> *À :* postgis-users@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@lists.osgeo.org
>> http://lists.osgeo.org/mailman/listinfo/postgis-users
>>
>
>
> ___
> postgis-users mailing list
> postgis-users@lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/postgis-users
>
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/postgis-users

Re: [postgis-users] split line at polygon edge

[François Hugues]

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-boun...@lists.osgeo.org] De la part 
de Garret W
Envoyé : jeudi 17 mars 2016 04:11
À : postgis-users@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@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/postgis-users

Re: [postgis-users] split line at polygon edge

[Garret W]

Thanks 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  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 :
>
>> 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-boun...@lists.osgeo.org] *De
>> la part de* Garret W
>> *Envoyé :* jeudi 17 mars 2016 04:11
>> *À :* postgis-users@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@lists.osgeo.org
>> http://lists.osgeo.org/mailman/listinfo/postgis-users
>>
>
>
> ___
> postgis-users mailing list
> postgis-users@lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/postgis-users
>
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/postgis-users

Re: [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 :

> 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-boun...@lists.osgeo.org] *De
> la part de* Garret W
> *Envoyé :* jeudi 17 mars 2016 04:11
> *À :* postgis-users@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@lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/postgis-users
>
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/postgis-users