Форумы GIS-Lab.info

Код: Выделить всё

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";

Код: Выделить всё

select   data1,
            ... ,
           (select  sum(*) from t1 where ....  order by ... ) as sum,
           (select  count(*) from t2 where ....  order by ... ) as count
from t1 where ....  order by ... 

Код: Выделить всё

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;