'[postgis-users] Looking for example SQL code to generate linestring for GPS/Plow Up/Down action.'

[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgis-users
Subject:    [postgis-users] Looking for example SQL code to generate linestring for GPS/Plow Up/Down action.
From:       "Basques, Bob (CI-StPaul)" <bob.basques () ci ! stpaul ! mn ! us>
Date:       2012-07-20 15:39:03
Message-ID: C9CEA89772DA74488B5A5B6EB17DC481440A8A () 055-CH1MPN1-003 ! 055d ! mgd ! msft ! net
[Download RAW message or body]

I have a (GPS Point) table that looks like:

1;0;0;0;0;geom
2;0;0;0;1;geom
3;0;0;0;0;geom
4;0;0;0;1;geom
5;0;0;0;0;geom
6;0;0;0;1;geom
7;0;0;0;0;geom
8;0;0;0;1;geom
9;0;0;0;0;geom
10;0;0;0;1;geom
11;0;0;0;1;geom
12;0;0;0;0;geom
13;0;0;0;0;geom
14;0;0;0;1;geom
15;0;0;0;0;geom
16;0;0;0;1;geom
17;0;0;0;0;geom

The next to last column is the plow up/down switch.  "1"= Plow Down or start of line \
segment, and "0" = Plow up or end of line segment.  If two or more sequential records \
have a "1" value, then it's to be treated as a multi-segment line.  So, what would \
the SQL look like to do that.  I know I need a self join, but the "1"/"0" value thing \
is making my brain freeze for some reason . . .  the inner select needs to select the \
rows sequentially beginning from a "1" value, until it reaches a "0" value.  This \
would become a linestring in the outer result, right?

Thanks

bobb

[Attachment #3 (text/html)]

<html dir="ltr">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<style type="text/css" id="owaParaStyle"></style>
</head>
<body fpstyle="1" ocsi="0">
<div style="direction: ltr;font-family: Tahoma;color: #000000;font-size: 10pt;">I \
have a (GPS Point) table that looks like: <div><br>
</div>
<div>
<div>1;0;0;0;0;geom</div>
<div>2;0;0;0;1;geom</div>
<div>3;0;0;0;0;geom</div>
<div>4;0;0;0;1;geom</div>
<div>5;0;0;0;0;geom</div>
<div>6;0;0;0;1;geom</div>
<div>7;0;0;0;0;geom</div>
<div>8;0;0;0;1;geom</div>
<div>9;0;0;0;0;geom</div>
<div>10;0;0;0;1;geom</div>
<div>11;0;0;0;1;geom</div>
<div>12;0;0;0;0;geom</div>
<div>13;0;0;0;0;geom</div>
<div>14;0;0;0;1;geom</div>
<div>15;0;0;0;0;geom</div>
<div>16;0;0;0;1;geom</div>
<div>17;0;0;0;0;geom</div>
</div>
<div><br>
</div>
<div>The next to last column is the plow up/down switch. &nbsp;&quot;1&quot;= Plow \
Down or start of line segment, and &quot;0&quot; = Plow up or end of line segment. \
&nbsp;If two or more sequential records have a &quot;1&quot; value, then it's to be \
treated as a multi-segment line. &nbsp;So, what would  the SQL look like to do that. \
&nbsp;I know I need a self join, but the &quot;1&quot;/&quot;0&quot; value thing is \
making my brain freeze for some reason . . . &nbsp;the inner select needs to select \
the rows sequentially beginning from a &quot;1&quot; value, until it reaches a \
&quot;0&quot; value. &nbsp;This would  become a linestring in the outer result, \
right?</div> <div><br>
</div>
<div>Thanks</div>
<div><br>
</div>
<div>bobb</div>
<div><br>
</div>
<div><br>
</div>
<div><br>
</div>
</div>
</body>
</html>

_______________________________________________
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

--===============4075905440585125726==--

[prev in list] [next in list] [prev in thread] [next in thread] 