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.franc...@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.c...@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.franc...@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-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 >
_______________________________________________ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/postgis-users