[mapserver-users] An interesting SQL linestring building problem from postgres.
Stephen Woodbridge
woodbri at swoodbridge.com
Tue Dec 2 13:07:45 PST 2014
On 12/2/2014 2:56 PM, Basques, Bob (CI-StPaul) wrote:
> All,
>
> I have a bunch of individual LINE segments in Postgres and want to
> display them via MapServer like so:
>
> First:
>
> ·Get a chunk of them based on a set of IDs (a set of three for example)
> and a begin/end timestamp (got this figured out already)
>
> ·Take the result and combine up the separate line strings into
> multi-segment (grouped) lines ordered by ID, timestamp (got this figured
> out for individual IDs)
>
> ·Add a random number to each grouping of multi-line string rows for
> dynamic color-coding of each group. (got this figured out for individual
> IDs)
>
> ·Have results output as three rows, one for each multi-segment line.
If the goal is to just display them, then you do not need to create a
multi-segment line, just display them as individual lines with the color
attribute.
I you need them 'glued together' as a multilines for some other reason.
then union should work.
select color, union(geom) from lines group by color order by timestamp;
> I’m lost on the method to do the last piece. Mostly it relates about
> how to approach which piece first. Seems like a straightforward SQL,
> but I’ve tried a few different things, and it falls part each time for me.
>
> Anyone have examples of something like this to start from?
>
> The pertinent Mapfile fragment, I know this only color codes each
> individual segment at the moment. VEH_IDs are passed into the MapFile
> from CGI (vname=veh_id in DB):
>
> DATA "the_line FROM (select the_line, vname, acqtime, trunc(random() * 4
> + 1) rnum from loc) as foo USING unique vname using SRID=4326"
>
> FILTER " vname in (%veh_id%) AND acqtime >
> '%time_idx1%' AND acqtime <'%time_idx2%' "
>
> VALIDATION
> 'time_idx1' '.*'
> 'time_idx2' '.*'
> 'veh_id' '.*' ## veh_id=’100’,’200’,’300’ . . .
> END
>
> CLASSITEM "rnum"
>
> CLASS ## Use the RNUM value to color code the line
> strings randomly.
> STYLE
> COLOR 255 0 0
> WIDTH 5
> END
> EXPRESSION '1'
> END
> CLASS
> STYLE
> COLOR 0 255 0
> WIDTH 5
> END
> EXPRESSION '2'
> END
> CLASS
> STYLE
> COLOR 0 0 255
> WIDTH 5
> END
> EXPRESSION '3'
> END
> CLASS
> STYLE
> COLOR 255 255 0
> WIDTH 5
> END
> EXPRESSION '4'
> END
> CLASS
> NAME "Segments"
> STYLE
> COLOR 0 255 0
> WIDTH 5
> END
> END
>
> Thanks
>
> Bobb
>
>
>
> _______________________________________________
> mapserver-users mailing list
> mapserver-users at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/mapserver-users
>
More information about the MapServer-users
mailing list