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

[Basques, Bob (CI-StPaul)]

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



-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20120720/1a6fda27/attachment.html>