I'am new to PostGIS/PostgreSql. I have a database which containts two shape-files :
polygonal layer towns and
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 :
A query which calculates 'length', 'count' (of linear geometries inside the Towns layer) and 'record resuls' as an attribute column within the 'Towns' Table
A new column called 'time updates' within the 'Towns' table which will hold the running time of the query.
I've added the following extra informations to help clarify my problem :
- Polygonal (town) and linear (street) geometry
- Attribute table for polygonal geometry
- 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;