[mapserver-users] An interesting SQL linestring building problem from postgres.
Basques, Bob (CI-StPaul)
bob.basques at ci.stpaul.mn.us
Wed Dec 3 20:06:12 PST 2014
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/6206f1ef/attachment.htm>
More information about the MapServer-users
mailing list