1

I buffer within a distance of 80 ft off a road .Which produces the layer of the result of the buffer. I then produces a layer for the land around it . I know need to select everything that fall within the buffer areas, and produces a layer that show the percentage of how much the land that is within that buffer.
I am using OpenJump to display map.

example of SQl

BUFFER

select ST_Buffer(geom,80)::geometry
(polygon,26986) As geom from road
where street = 'hope';

LAND

select *
from land;

OR

I used in postgis/ pgadmin this sql but openjump require geom to be used

select parcel, land_type , full_str
from land
left join road on ST_Dwithin(land.geom,road.geom, 19.812) 
where road_name = 'hope';
tinlyx
  • 11,057
  • 18
  • 71
  • 119
Schoollife
  • 57
  • 6
  • You need to populate a field with the original area, intersect then divide the intersecting area by the original area (x 100) to find the percentage of the original area. – Michael Stimson Mar 07 '18 at 01:21

1 Answers1

2

Pretty much the same as this question: calculating percent area of intersection in where clause

But we want to get the percentage as a column in the output.
In this case the code would be:

SELECT l.parcel, l.land_type , l.full_str,  l.geom, (sum((st_area (st_intersection (l.geom,buf.geom))/st_area(l.geom)))*100) as PERCENTAGE
FROM land l, 
(
    ST_Buffer(geom,80)::geometry(polygon,26986) as geom 
    FROM road
    WHERE street = 'hope'
) as buf
WHERE st_intersects(l.geom, buf.geom);
Cushen
  • 2,928
  • 13
  • 15