How fill length and feature count in PostGIS table for each polygons

Question

I'am new to PostGIS/PostgreSql. I have a database which containts two shape-files :

1. polygonal layer towns and

2. linear layer of roads

   To calculate the length and feature count of 'Streets' inside each 'Town' use the following query in pgAdmin:

     SELECT
         m."townname",
         sum(ST_Length_Spheroid(r.the_geom,'SPHEROID["WGS 84",6378137,298.257223563]'))/1000 AS Roads_Km,
         count(*) AS Roads_Count
     FROM
         "Streets" AS r,
         "Towns" AS m
     WHERE 
         ST_Intersects(m.the_geom, r.the_geom)
     GROUP BY
         m."townname"
     ORDER BY
         m."townname";

and then I save the results in csv table with the fields 'Town name', 'length', 'count'.

I want to add in the 'Town table' the following columns : 'length', 'count' and 'time updates'.

I need some help to create :

1. A query which calculates 'length', 'count' (of linear geometries inside the Towns layer) and 'record resuls' as an attribute column within the 'Towns' Table

2. A new column called 'time updates' within the 'Towns' table which will hold the running time of the query.

   Thanks!

Updated 10.12.2012

I've added the following extra informations to help clarify my problem :

1. Polygonal (town) and linear (street) geometry
2. Attribute table for polygonal geometry
3. Result my query in pgAdmin result window

Updated 12.12.2012 This is corrected query:

UPDATE town t SET 
    length = calc.roadskm, -- column length in towns table
    count = calc.roadscount, -- column count in towns table
    updatetime = calc.time -- column updatetime in towns table
FROM (
    SELECT
        m.townname,
        sum(ST_Length_Spheroid(r.the_geom,'SPHEROID["WGS 84",6378137,298.257223563]'))/1000 AS roadskm,
        count(*) AS roadscount,
        now() as time
    FROM
        streets AS r, --linear layer of roads
        town AS m --polygonal layer of towns
        WHERE ST_Intersects(m.the_geom, r.the_geom)
    GROUP BY
        m.townname
        ) calc 
WHERE t.townname = calc.townname;

Answer 1

There might be syntax problems , but here is example howto use subquery.

UPDATE Towns t 
     SET t.length = r.Roads_km
        ,t.count = r.Roads_Count
        ,t.updatetime = r.update_time
FROM (
-- subquery
SELECT
     m."TownName"
     ,sum(ST_Length_Spheroid(r.the_geom,'SPHEROID["WGS 84",6378137,298.257223563]'))/1000 AS Roads_Km
, count(*) AS Roads_Count
, now() as update_time
FROM
     "Streets" AS r,
     "Towns" AS m
WHERE ST_DWitIn(m.the_geom, r.the_geom,0,1 /* i assume metric srid */  )
GROUP BY
     m."TownName"
ORDER BY
     m."TownName"
) as r 
WHERE t.TownName = r.TownName

:Edited to show howto use subquery in update

Answer 2

If your tables geom is not immersivly complex then I suggest a view would fit your needs better.

CREATE or REPLACE VIEW myview AS
 SELECT
     m."TownName",
     sum(ST_Length_Spheroid(r.the_geom,'SPHEROID["WGS 84",6378137,298.257223563]'))/1000 AS Roads_Km,
     count(*) AS Roads_Count,
     now() as 'Time'  --Note: now() returns the time where the query started, not the time it needed to complete. 
 FROM
     "Streets" AS r,
     "Towns" AS m
 WHERE 
     ST_Intersects(m.the_geom, r.the_geom)
 GROUP BY
     m."TownName"
 ORDER BY
     m."TownName";

To get your results :

SELECT * FROM myview;

A view is basically a query which is being run every time you call it (think it as a shortcut). So if you add more elements in "Towns" and "Roads" table the view will accordingly be updated.