Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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;

enter image description here

share|improve this question
    
If you can, it's better not to use capitalized column names. Then you can write m.townname instead of m."TownName". In PgAdmin for example, sometimes the capitalized letters are not shown which can be puzzling. –  djq Dec 11 '12 at 2:34
    
With the query that you are using, if a road has part of this length outside the town, all the length of road will be counted. If you need a different thing you should change your query –  Francisco Puga Dec 11 '12 at 8:59
    
@Francisco Puga, Thanks! I know. For my data it is necessary –  HasT Dec 11 '12 at 9:13

2 Answers 2

up vote 3 down vote accepted
+50

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

share|improve this answer
    
Now I have result (length, feature count inside each polygon) linear layer Steeet in pgAdmin Result window (and I save this result in csv table). I want record length, feature count of linear layer Street inside each polygon Towns in polygon attribute table and add time of run query. –  HasT Dec 3 '12 at 13:18
    
count(r.the_geom) gives you count from used r.the_geom –  simplexio Dec 3 '12 at 13:52
    
Thanks for now() as update_time function! But I need record result in Towns attribute table. How to do it? –  HasT Dec 4 '12 at 9:41
    
do you mean UPDATE column in Towns table ? English is not my native language. –  simplexio Dec 4 '12 at 11:00
    
Yes, update records in Length, count, time_update columns of Towns attribute table layer after run query –  HasT Dec 4 '12 at 11:36

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.

share|improve this answer
    
Thanks you for answer, but result must save in attribute table of Towns layer. –  HasT Dec 11 '12 at 7:43

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.