[mapserver-dev] Adding a time index stamp to returned image.

Fri Jul 24 14:30:02 PDT 2015

my SQL looks like this:

 DATA "bbox from (
SELECT
row_number() over (order by accessed_at nulls last) as seq,
client,
extract('epoch' from (%time_idx1%::timestamp + INTERVAL '%min% minute' - accessed_at)::interval) as elapsed,
id, accessed_at, bbox
                        FROM
                                layer_metrics
                        Where
                                accessed_at < (%time_idx1%::timestamp + INTERVAL '%min% minute')
AND
                                accessed_at >= (%time_idx1%::timestamp + INTERVAL '%min% minute' - INTERVAL '10 minute')
AND
client NOT IN ('%172.17.17.97%','%172.17.17.151%')

                                         ) as subquery using unique id using srid=200068"

I tried a modified SQL like so:

SELECT
                                row_number() over (order by accessed_at nulls last) as seq,
                                client,
                                extract('epoch' from ('2015-07-21 08:00:00'::timestamp + INTERVAL '25 minute' - accessed_at)::interval) as elapsed,
                                '2015-07-21 08:00:00'::timestamp + INTERVAL '25 minute' as tstamp,
                                id, accessed_at, to_char(accessed_at - INTERVAL '5 hours', 'HH24:MI:SS') as tstamp --,
                               -- bbox
                        FROM
                                layer_metrics
                        Where
                                accessed_at < ('2015-07-21 08:00:00'::timestamp + INTERVAL '25 minute')
                        AND
                                accessed_at >= ('2015-07-21 08:00:00'::timestamp + INTERVAL '25 minute' - INTERVAL '120 minute')

to get the “tstamp" value in all rows:

1;"172.17.16.150";6860;"2015-07-21 08:25:00";2062502;"2015-07-21 06:30:40+00";"01:30:40"
2;"172.17.16.150";6860;"2015-07-21 08:25:00";2062503;"2015-07-21 06:30:40+00";"01:30:40"
3;"172.17.16.150";6860;"2015-07-21 08:25:00";2062504;"2015-07-21 06:30:40+00";"01:30:40"
4;"172.17.16.150";6860;"2015-07-21 08:25:00";2062505;"2015-07-21 06:30:40+00";"01:30:40"
5;"172.17.16.150";6857;"2015-07-21 08:25:00";2062506;"2015-07-21 06:30:43+00";"01:30:43"
6;"172.17.16.150";6857;"2015-07-21 08:25:00";2062507;"2015-07-21 06:30:43+00";"01:30:43"
7;"172.17.16.150";6857;"2015-07-21 08:25:00";2062508;"2015-07-21 06:30:43+00";"01:30:43"
8;"172.17.16.150";6857;"2015-07-21 08:25:00";2062509;"2015-07-21 06:30:43+00";"01:30:43"
9;"172.17.16.150";6842;"2015-07-21 08:25:00";2062510;"2015-07-21 06:30:58+00";"01:30:58"
10;"172.17.16.150";6842;"2015-07-21 08:25:00";2062511;"2015-07-21 06:30:58+00";"01:30:58”

Which outputs just peachy,  but for some reason a time object won’t come through via an expression for labelling.  I think my problem is I’m trying to do it all in one call.  I didn’t try a whole separate layer . . .anyway, any of the “tstamp” values will work for the label.

bobb

On Jul 24, 2015, at 4:08 PM, Lime, Steve D (MNIT) <Steve.Lime at state.mn.us<mailto:Steve.Lime at state.mn.us>> wrote:

Should be very doable – I like a challenge. You’re not saying much about how you were going about it. I created a quick test that drops the current time in the center of the map where the time is from the now() function in PostgreSQL. See:

  http://maps1.dnr.state.mn.us/cgi-bin/mapserv64?mode=map&map=/usr/local/mapserver/apps/test/timestamp/test.map

If you refresh it you’ll get the timestamp associated. Presumably you could format this to your liking using the various utilities in PostgreSQL. To do this I had to create a dummy table in my database that contained a single dummy column, and I added 1 row to it. That allowed me to get useful SQL generated via MapServer. The timestamp layer looks like:

 LAYER
    NAME “timestamp”
    TYPE POINT
    STATUS DEFAULT

    TRANSFORM FALSE
    UNITS PERCENTAGES

    CONNECTIONTYPE POSTGIS
    CONNECTION “database of your choice”
    DATA "the_geom FROM (SELECT ST_MakePoint(.50, .50) AS the_geom, NOW() AS tstamp, dummy_column FROM dummy_table) AS foo USING UNIQUE dummy_column USING SRID=-1"
    LABELITEM tstamp
    CLASS
      LABEL
        TYPE TRUETYPE
        FONT “verdana-bold”
        COLOR 0 0 0
        SIZE 12
        POSITION CC
      END
    END
  END

To change the location of the point you’d edit the coordinates inside the ST_MakePoint() function. Hope it helps.

Steve

From: mapserver-dev-bounces at lists.osgeo.org<mailto:mapserver-dev-bounces at lists.osgeo.org> [mailto:mapserver-dev-bounces at lists.osgeo.org] On Behalf Of Basques, Bob (CI-StPaul)
Sent: Tuesday, July 21, 2015 4:09 PM
To: mapserver-dev at lists.osgeo.org<mailto:mapserver-dev at lists.osgeo.org>
Subject: [mapserver-dev] Adding a time index stamp to returned image.

All,

I’m using a POSTGRES connection an trying to place a timestamp text chunk into the corner of a image from Postgres.

I’ve got a method for generating a set of images for a time-lapse movie (https://www.dropbox.com/s/7spkctc2mmtfmhc/out4.mp4?dl=0), but need to add a timestamp to each of the images upon output.  I’m not having much luck though outputting a seemingly simple item from Postgres.  Fro some reason TimeStamps are not completely understood by MapServer.  Maybe I need to cast the timestamp to a text string or something . . .

Thoughts??

Thanks

bobb

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/mapserver-dev/attachments/20150724/8ba8c2d2/attachment-0001.html>