gistfile1.txt

avgRunOnAdmin og avgRunOnZone

Når http://trackanalyzer.safetrack.dk/api/v1/unitsummary kaldes laves en middeltidig table med dette schema:

  Column  |         Type         | Modifiers
----------+----------------------+-----------
 gid      | integer              |
 unitid   | integer              |
 run1     | integer              |
 date     | date                 |
 the_geom | geometry(Point,4326) |
 
 Derpå laves border analyserne.
 
 SQL'en bag avgRunOnAdmin ser således ud: {$table} er den middlertidige tabel og {$admTable} er kommunegrænser
 
 SELECT 
 	avg({$table}.run1)::integer AS run1,
 	count(*) AS count,
 	sum({$table}.run1)::integer AS activity,
 	TO_CHAR((avg({$table}.run1)::integer || ' second')::interval, 'HH24:MI:SS') AS rtime, 
 	TO_CHAR((sum({$table}.run1)::integer || ' second')::interval, 'HH24:MI:SS') AS atime, 
 	{$admTable}.the_geom, {$admTable}.gid, {$admTable}.name
 FROM
 	{$table}, {$admTable}
 WHERE
 	{$table}.the_geom && st_transform({$admTable}.the_geom, 4326) AND st_intersects({$table}.the_geom, st_transform({$admTable}.the_geom, 4326))
GROUP BY
	{$admTable}.the_geom,
	{$admTable}.gid, 
	{$admTable}.name


avgRunOnZone ser således ud. $obj["relation"] er zone tabellen skabt med /public/Zone api'et.

SELECT 
	avg({$table}.run1)::integer AS run1,
	count(*) AS count, 
	sum({$table}.run1)::integer AS activity, 
	TO_CHAR((avg({$table}.run1)::integer || ' second')::interval, 'HH24:MI:SS') AS rtime, 
	TO_CHAR((sum({$table}.run1)::integer || ' second')::interval, 'HH24:MI:SS') AS atime,
	{$obj["relation"]}.id,
	{$obj["relation"]}.the_geom,
	{$obj["relation"]}.gid
	{$obj["relation"]}.name
FROM
	{$table}, 
	{$obj["relation"]}
WHERE 
	{$table}.the_geom && st_transform({$obj["relation"]}.the_geom, 4326) AND st_intersects({$table}.the_geom, st_transform({$obj["relation"]}.the_geom, 4326))
GROUP BY 
	{$obj["relation"]}.id, 
	{$obj["relation"]}.the_geom,
	{$obj["relation"]}.gid,
	{$obj["relation"]}.name

Zonetabellen bliver fyldt med denne function:

private function makeZoneSql($list, $schema, $table)
    {
        $sqls = array();
        foreach ($list as $obj) {
            $format = "INSERT into {$schema}.{$table}(id,name,the_geom) VALUES(%s,'%s',ST_geomfromtext(ST_astext(ST_FlipCoordinates(ST_geomfromtext('%s',4326))),4326));";
            $sqls[] = sprintf($format, $obj->id, $obj->name, $obj->polygon);
        }
        return $sqls;
    }