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.
select clipped.geoid10, count(streets_geom) as streets_countfrom (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 clippedwhere st_dimension(clipped.streets_geom) = 1 group by geoid10;
أكثر...
select clipped.geoid10, count(streets_geom) as streets_countfrom (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 clippedwhere st_dimension(clipped.streets_geom) = 1 group by geoid10;
أكثر...