[postgis-users] Finding points of intersection of a linestring with another multiolygon and linestring

[Trang Nguyen]

I realize I didn't clearly phrase my question:


-          Is there a way to retrieve the points in the waypoints linestring that intersects a polygon or another linestring without having to write a function to traverse each point in waypoints to check whether the conditions st_intersects(point, seg_wp_intersection) and st_intersects(point, zone_wp_intersection)  are met?


Any help on this would be greatly appreciated!

Thanks,
Trang
From: postgis-users-bounces at lists.osgeo.org [mailto:postgis-users-bounces at lists.osgeo.org] On Behalf Of Trang Nguyen
Sent: Wednesday, September 30, 2015 10:28 PM
To: postgis-users at lists.osgeo.org
Subject: [postgis-users] Finding points of intersection of a linestring with another multiolygon and linestring

Hi forum,

I have a LINESTRING represent the waypoints of a trip through road segments (LINESTRING) and zones (MULTIPOLYGON).
For each segment and zone, I would like to find out the points in the waypoints  which intersected the targeted zone/segment geometries. First and last intersection point into and exiting the zone or segment  would the sufficient as well.
I can use st_intersection to find the intersection each of waypoints to segment and zone geometries, but this returns a geometry that doesn't necessarily contain the subset of intersecting points in the linestring. The query will be run on large datasets, so performance is an important consideration. GIST indexes exist on all the geometries joined (zone_geom, segment_geom and waypoints).

---------------------------
Query
---------------------------
select
st_transform(st_intersection(segment_geom, waypoints),4326) seg_wp_intersection, -- need subset of points in waypoints that intersected with segment_geom
st_transform(st_intersection(zone_geom, waypoints),4326)  zone_wp_intersection,  -- need subset of  points in waypoints that intersected with zone_geom
trip_id, waypoints, zone_id, segment_id
from od1.v_trip_zone_segment
where
startts>=TIMESTAMP '2015-01-16T12:20:29.000Z' and startts<TIMESTAMP '2015-01-16T17:20:30.000Z' and endts<TIMESTAMP '2015-01-16T17:20:30.000Z' and zone_id in ('kansas_303','kansas_601','kansas_603','kansas_604','kansas_10','kansas_11','kansas_9','kansas_310','kansas_311','kansas_315','kansas_301','kansas_302','kansas_307','kansas_306','kansas_305','kansas_204','kansas_201','kansas_3','kansas_2','kansas_1') and segment_id in ('5062926','5062931','5062932','4062933','5055496','5063065','5062826','5062825','5062824','5062643','5062644','5062645','5062646','5055551','5062839','5062849','5062895','5063081') and segment_mapversion='1501'
and st_intersects(segment_geom, waypoints)
and st_intersects(szone_geom, waypoints)


Thanks,
Trang
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20151001/2a1295a6/attachment.html>