<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=us-ascii">
<meta name="Generator" content="Microsoft Word 14 (filtered medium)">
<style><!--
/* Font Definitions */
@font-face
{font-family:Wingdings;
panose-1:5 0 0 0 0 0 0 0 0 0;}
@font-face
{font-family:Wingdings;
panose-1:5 0 0 0 0 0 0 0 0 0;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:Tahoma;
panose-1:2 11 6 4 3 5 4 4 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri","sans-serif";}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:#0563C1;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:#954F72;
text-decoration:underline;}
p.MsoListParagraph, li.MsoListParagraph, div.MsoListParagraph
{mso-style-priority:34;
margin-top:0in;
margin-right:0in;
margin-bottom:0in;
margin-left:.5in;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri","sans-serif";}
span.EmailStyle17
{mso-style-type:personal;
font-family:"Calibri","sans-serif";
color:windowtext;}
span.EmailStyle18
{mso-style-type:personal-reply;
font-family:"Calibri","sans-serif";
color:#44546A;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@page WordSection1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
{page:WordSection1;}
/* List Definitions */
@list l0
{mso-list-id:99029873;
mso-list-type:hybrid;
mso-list-template-ids:1303135544 -2043802680 67698691 67698693 67698689 67698691 67698693 67698689 67698691 67698693;}
@list l0:level1
{mso-level-start-at:3;
mso-level-number-format:bullet;
mso-level-text:-;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;
font-family:"Calibri","sans-serif";
mso-fareast-font-family:Calibri;
mso-bidi-font-family:"Times New Roman";}
@list l0:level2
{mso-level-number-format:bullet;
mso-level-text:o;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;
font-family:"Courier New";}
@list l0:level3
{mso-level-number-format:bullet;
mso-level-text:\F0A7;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;
font-family:Wingdings;}
@list l0:level4
{mso-level-number-format:bullet;
mso-level-text:\F0B7;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;
font-family:Symbol;}
@list l0:level5
{mso-level-number-format:bullet;
mso-level-text:o;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;
font-family:"Courier New";}
@list l0:level6
{mso-level-number-format:bullet;
mso-level-text:\F0A7;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;
font-family:Wingdings;}
@list l0:level7
{mso-level-number-format:bullet;
mso-level-text:\F0B7;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;
font-family:Symbol;}
@list l0:level8
{mso-level-number-format:bullet;
mso-level-text:o;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;
font-family:"Courier New";}
@list l0:level9
{mso-level-number-format:bullet;
mso-level-text:\F0A7;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;
font-family:Wingdings;}
ol
{margin-bottom:0in;}
ul
{margin-bottom:0in;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang="EN-US" link="#0563C1" vlink="#954F72">
<div class="WordSection1">
<p class="MsoNormal"><span style="color:#44546A">I realize I didn’t clearly phrase my question:<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#44546A"><o:p> </o:p></span></p>
<p class="MsoListParagraph" style="text-indent:-.25in;mso-list:l0 level1 lfo1"><![if !supportLists]><span style="color:#44546A"><span style="mso-list:Ignore">-<span style="font:7.0pt "Times New Roman"">
</span></span></span><![endif]><span style="color:#44546A">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, </span>seg_wp_intersection<span style="color:#44546A">) and st_intersects(point,
</span>zone_wp_intersection<span style="color:#44546A">) are met? <o:p></o:p></span></p>
<p class="MsoListParagraph"><span style="color:#44546A"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="color:#44546A">Any help on this would be greatly appreciated!<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#44546A"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="color:#44546A">Thanks,<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#44546A">Trang<o:p></o:p></span></p>
<div>
<div style="border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0in 0in 0in">
<p class="MsoNormal"><b><span style="font-size:10.0pt;font-family:"Tahoma","sans-serif"">From:</span></b><span style="font-size:10.0pt;font-family:"Tahoma","sans-serif""> postgis-users-bounces@lists.osgeo.org [mailto:postgis-users-bounces@lists.osgeo.org]
<b>On Behalf Of </b>Trang Nguyen<br>
<b>Sent:</b> Wednesday, September 30, 2015 10:28 PM<br>
<b>To:</b> postgis-users@lists.osgeo.org<br>
<b>Subject:</b> [postgis-users] Finding points of intersection of a linestring with another multiolygon and linestring<o:p></o:p></span></p>
</div>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">Hi forum,<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">I have a LINESTRING represent the waypoints of a trip through road segments (LINESTRING) and zones (MULTIPOLYGON).<o:p></o:p></p>
<p class="MsoNormal">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.<o:p></o:p></p>
<p class="MsoNormal">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).<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">---------------------------<o:p></o:p></p>
<p class="MsoNormal">Query<o:p></o:p></p>
<p class="MsoNormal">---------------------------<o:p></o:p></p>
<p class="MsoNormal">select<o:p></o:p></p>
<p class="MsoNormal">st_transform(st_intersection(segment_geom, waypoints),4326) seg_wp_intersection, -- need subset of points in waypoints that intersected with segment_geom<o:p></o:p></p>
<p class="MsoNormal">st_transform(st_intersection(zone_geom, waypoints),4326) zone_wp_intersection, -- need subset of points in waypoints that intersected with zone_geom<o:p></o:p></p>
<p class="MsoNormal">trip_id, waypoints, zone_id, segment_id <o:p></o:p></p>
<p class="MsoNormal">from od1.v_trip_zone_segment <o:p></o:p></p>
<p class="MsoNormal">where <o:p></o:p></p>
<p class="MsoNormal">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'<o:p></o:p></p>
<p class="MsoNormal">and st_intersects(segment_geom, waypoints)<o:p></o:p></p>
<p class="MsoNormal">and st_intersects(szone_geom, waypoints)<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">Thanks,<o:p></o:p></p>
<p class="MsoNormal">Trang<o:p></o:p></p>
</div>
</body>
</html>