4

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

3
  • 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, 2012 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 Dec 11, 2012 at 8:59
  • @Francisco Puga, Thanks! I know. For my data it is necessary Dec 11, 2012 at 9:13

2 Answers 2

4
+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

11
  • 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. Dec 3, 2012 at 13:18
  • count(r.the_geom) gives you count from used r.the_geom Dec 3, 2012 at 13:52
  • Thanks for now() as update_time function! But I need record result in Towns attribute table. How to do it? Dec 4, 2012 at 9:41
  • do you mean UPDATE column in Towns table ? English is not my native language. Dec 4, 2012 at 11:00
  • Yes, update records in Length, count, time_update columns of Towns attribute table layer after run query Dec 4, 2012 at 11:36
4

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.

1
  • Thanks you for answer, but result must save in attribute table of Towns layer. Dec 11, 2012 at 7:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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