[mapserver-users] An interesting SQL linestring building problem from postgres.

[Basques, Bob (CI-StPaul)]

This is a lot faster!!


select
    row_number() over () as rnum,
    vname,
    st_multi(st_union(the_line)) as the_line,
    acqtime
from
    loc
where
    vname in ('463','462')
and
    acqtime > '2014-12-01 08:00'
and
    acqtime <= '2014-12-01 10:00'
group by
    vname, acqtime
order by
    acqtime


________________________________
From: Basques, Bob (CI-StPaul)
Sent: Wednesday, December 03, 2014 10:06 PM
To: mapserver-users at lists.osgeo.org
Subject: RE: [mapserver-users] An interesting SQL linestring building problem from postgres.

All,

Made some progress:

select
    row_number() over () as rnum,
    vname,
    st_multi(st_union(the_line)) as the_line
from
    loc
where
    vname in ('463','462')
and
    acqtime > '2014-12-01 08:00'
and
    acqtime <= '2014-12-01 10:00'
group by
    vname

But it's a lot slower than the original query for some reason.  A couple of line strings at a 1000 or so segments take almost a minute.

Still working it . . . I wonder if making the groups into something other than a multi would be better?

bobb

________________________________
From: mapserver-users-bounces at lists.osgeo.org [mapserver-users-bounces at lists.osgeo.org] on behalf of Worth Lutz [wal3 at mindspring.com]
Sent: Tuesday, December 02, 2014 4:00 PM
To: mapserver-users at lists.osgeo.org
Subject: Re: [mapserver-users] An interesting SQL linestring building problem from postgres.

I've done something similar.

I don't have it in front of me at the moment but a postgres select can do this.

Look in Postgres for "window"  and "row_number".

You use the postgres "OVER" clause to group the items and then row_number to get a key for color.

SELECT
    a,
    b,
    c,
    row_number() OVER (PARTITION BY d) AS color_key
FROM table

The partition is your grouping of three.  row_number would be your color_key.  Use a modulus to limit the number of colors.

here is a link which might get you started
    http://www.codeproject.com/articles/308281/How-to-Use-ROW-NUMBER-to-Enumerate-and-Partition-R

Note the final SQL statement which shows multiple items inside the "OVER" clause.

I hope this helps point you in the right direction.

If I can find a second, I'll look at your problem a little harder.

Worth Lutz
------------------

On 12/02/2014 02: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.

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<mailto:mapserver-users at lists.osgeo.org>
http://lists.osgeo.org/mailman/listinfo/mapserver-users

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/mapserver-users/attachments/20141204/21740a1a/attachment.htm>