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.
Thanks!
Updated 10.12.2012
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;
m.townname
instead ofm."TownName"
. In PgAdmin for example, sometimes the capitalized letters are not shown which can be puzzling.