1

How would I calculate the percentage of streets that have sidewalks summarized by census tracts using PostGIS. My sidewalks file has an id that links it to its adjacent street and I've worked out how to select the street segment total grouped by tract, but I'm unsure of how to generate the number of streets with sidewalks without generating a completely new subquery. The code below seems to work, but takes a really long time to run as it's running the same select twice on close to a million street records.

select t.geoid10, count(streets_no_sdw), count(streets_sdw) 
from    
   (
    select cen_tract_2010.geoid10, 
               (st_intersection(teleatlas_streets.wkb_geometry, 
                st_buffer(cen_tract_2010.wkb_geometry, .5))) as streets_geom 
    from transportation.teleatlas_streets 
        join geography.cen_tract_2010 
            on st_intersects(teleatlas_streets.wkb_geometry, cen_tract_2010.wkb_geometry)
   ) as streets_no_sdw,
   (
    select cen_tract_2010.geoid10, 
               (st_intersection(teleatlas_streets.wkb_geometry, 
                st_buffer(cen_tract_2010.wkb_geometry, .5))) as streets_geom 
    from transportation.teleatlas_streets 
        join geography.cen_tract_2010 
            on st_intersects(teleatlas_streets.wkb_geometry, cen_tract_2010.wkb_geometry)
    join transportation.mpo_sidewalks on dynamap_id = teleatlas_id
   ) as streets_sdw    
  ,geography.cen_tract_2010 as t
where 
t.geoid10 = streets_no_sdw.geoid10 and t.geoid10 = streets_sdw.geoid10
group by t.geoid10
  • Show what you've tried with subquery. It's hard not to propose the simplest solution of just joining another table to the two you have without seeing the problem. – Jakub Kania Aug 16 '15 at 10:48
1

Creating a materialized view that represents the street segments with sidewalks can help speed up the query as long as you know the underlying tables won't be updated very frequently.

Create the materialized view:

CREATE MATERIALIZED VIEW transportation.streets_with_sdw AS
SELECT *,
       CASE WHEN dynamap_id in (select distinct teleatlas_id from transportation.mpo_sidewalks) THEN 'YES'
            ELSE 'NO'
       END AS sidewalk
FROM transportation.teleatlas_streets

Whenever updates do occur, the view will need to be refreshed with REFRESH MATERIALIZED VIEW transportation.streets_with_sdw

Now add some indexes to speed up the queries:

CREATE UNIQUE INDEX dynamap_id_idx on transportation.streets_with_sdw(dynamap_id);
CREATE INDEX streets_with_sdw_gix ON transportation.streets_with_sdw USING gist(wkb_geometry);

With the view configured the percentage of roads with sidewalks within the tract boundary can be calculated:

SELECT s.geoid10, s.cnt_sdw, s.cnt_no_sdw,
       CASE WHEN (s.cnt_sdw + s.cnt_no_sdw) > 0 THEN (s.cnt_sdw::float / (s.cnt_sdw + s.cnt_no_sdw)) * 100
            ELSE 0
       END AS sidewalk_percent
FROM
       SELECT g.geoid10,
              SUM(CASE WHEN r.sidewalk='YES' THEN 1 ELSE 0 END) AS cnt_sdw,
              SUM(CASE WHEN r.sidewalk='NO' THEN 1 ELSE 0 END) AS cnt_no_sdw
       FROM geography.cen_tract_2010 as g
       LEFT JOIN
            transportation.streets_with_sdw AS r
       ON 
            ST_DWITHIN(r.wkb_geometry, g.wkb_geometry, .5)
       GROUP BY g.geoid10)
AS s;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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