Finding points of intersection of a linestring with another multiolygon and linestring

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

Finding points of intersection of a linestring with another multiolygon and linestring

Trang Nguyen

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


_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: 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: [hidden email] [mailto:[hidden email]] On Behalf Of Trang Nguyen
Sent: Wednesday, September 30, 2015 10:28 PM
To: [hidden email]
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


_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: Finding points of intersection of a linestring with another multiolygon and linestring

Nicolas Ribot-3
Hi,

Yes, you can use st_dumppoints to extract way's points and use these points in a query with seg_wp_intersection and zone_wp_intersection to find intersections.

Nicolas

On 1 October 2015 at 20:27, Trang Nguyen <[hidden email]> wrote:

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: [hidden email] [mailto:[hidden email]] On Behalf Of Trang Nguyen
Sent: Wednesday, September 30, 2015 10:28 PM
To: [hidden email]
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


_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users


_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: Finding points of intersection of a linestring with another multiolygon and linestring

Trang Nguyen

Thanks Nicolas. Would I need to write a function to iterate over each point in st_dumppoints to find whether a point is within the intersecting geometry or is there a better way?

I’ve tried to do a select with st_dumppoints(waypoints) but finding that performance is very slow: 289 sec vs 2 sec without st_dumppoints for a very small time range and narrow set of input geometries. This is running on postgres 9.4.1 and postgis 2.1.5.

 

Trang

 

From: [hidden email] [mailto:[hidden email]] On Behalf Of Nicolas Ribot
Sent: Friday, October 02, 2015 2:20 AM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] Finding points of intersection of a linestring with another multiolygon and linestring

 

Hi,

 

Yes, you can use st_dumppoints to extract way's points and use these points in a query with seg_wp_intersection and zone_wp_intersection to find intersections.

 

Nicolas

 

On 1 October 2015 at 20:27, Trang Nguyen <[hidden email]> wrote:

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: [hidden email] [mailto:[hidden email]] On Behalf Of Trang Nguyen
Sent: Wednesday, September 30, 2015 10:28 PM
To: [hidden email]
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


_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

 


_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: Finding points of intersection of a linestring with another multiolygon and linestring

Trang Nguyen

I was finally able to get this mostly working with a st_dumppoints in an inner select. However, points in  zone_wp_intersection are not ordered by the time dimension (m coordinate):

 

"LINESTRING M (-95.7038708 38.9675286 1421442348000,-95.7028218 38.9720855 1421442366000,-95.704529 38.9656641 1421442338000)"

 

I can handle the sorting in the application tier but perhaps there is a better way to order the points by time directly in the query?

 

select pkey, trip_id, zone_id, segment_id, st_astext(ST_MakeLine((wp_pts).geom)) zone_wp_intersection,

startts, endts, startloc, endloc, probe_id, provider_id, movement_type, mode, trip_dist_m, trip_mean_speed_metersph, trip_max_speed_metersph, is_start_home, is_end_home, waypoints, match_cond

from (

select

st_dumppoints(waypoints) as wp_pts,  -- get actual points in waypoints that intersect with zone

pkey, trip_id, startts, endts, startloc, endloc, probe_id, provider_id, movement_type, mode, trip_dist_m, trip_mean_speed_metersph, trip_max_speed_metersph, is_start_home, is_end_home, waypoints, match_cond, zone_id, zone_geom, segment_id from od1.v_trip_zone_segment where startts>=TIMESTAMP '2015-01-16T12:20:29.000Z' and startts<TIMESTAMP '2015-01-17T17:20:30.000Z' and endts<TIMESTAMP '2015-01-17T17:20:30.000Z' and zone_id in ('kansas_303','kansas_601','kansas_603','kansas_604','kansas_10') and segment_id in ('5062926','5062931','5062932','4062933','5055496')

and st_intersects(segment_geom, waypoints)

and st_contains(st_buffer(st_transform(waypoints,3857), 30.0),st_transform(segment_geom,3857))

and st_intersects(zone_geom, waypoints)

) as k

where st_intersects((wp_pts).geom, zone_geom)

group by pkey, trip_id, zone_id, segment_id,

startts, endts, startloc, endloc, probe_id, provider_id, movement_type, mode, trip_dist_m, trip_mean_speed_metersph, trip_max_speed_metersph, is_start_home, is_end_home, waypoints, match_cond

order by pkey, zone_id

 

Thanks,

Trang

 

From: [hidden email] [mailto:[hidden email]] On Behalf Of Trang Nguyen
Sent: Friday, October 02, 2015 11:09 AM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] Finding points of intersection of a linestring with another multiolygon and linestring

 

Thanks Nicolas. Would I need to write a function to iterate over each point in st_dumppoints to find whether a point is within the intersecting geometry or is there a better way?

I’ve tried to do a select with st_dumppoints(waypoints) but finding that performance is very slow: 289 sec vs 2 sec without st_dumppoints for a very small time range and narrow set of input geometries. This is running on postgres 9.4.1 and postgis 2.1.5.

 

Trang

 

From: [hidden email] [[hidden email]] On Behalf Of Nicolas Ribot
Sent: Friday, October 02, 2015 2:20 AM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] Finding points of intersection of a linestring with another multiolygon and linestring

 

Hi,

 

Yes, you can use st_dumppoints to extract way's points and use these points in a query with seg_wp_intersection and zone_wp_intersection to find intersections.

 

Nicolas

 

On 1 October 2015 at 20:27, Trang Nguyen <[hidden email]> wrote:

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: [hidden email] [mailto:[hidden email]] On Behalf Of Trang Nguyen
Sent: Wednesday, September 30, 2015 10:28 PM
To: [hidden email]
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


_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

 


_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users