Calculating a score for each road in Openstreetmap produces unexpected result. What am I missing?-CodePudding

I have a Postgres database with a postgis extention installed and filles with open street map data.

With the following SQL statement :

SELECT                                                                
    l.osm_id,
    sum(
        st_area(st_intersection(ST_Buffer(l.way, 30), p.way))
        /
        st_area(ST_Buffer(l.way, 30))
    ) as green_fraction
FROM planet_osm_line AS l
INNER JOIN planet_osm_polygon AS p ON ST_Intersects(l.way, ST_Buffer(p.way,30))
WHERE p.natural in ('water') or p.landuse in ('forest') GROUP BY l.osm_id;

I calculate a "green" score.

My goal is to create a "green" score for each osm_id.

Which means; how much of a road is near a water, forrest or something similar.

For example a road that is enclosed by a park would have a score of 1.

A road that only runs by a river for a short period of time would have a score of for example 0.4

OR so is my expectation.

But by inspection the result of this calculation I get sometimes Values of

212.11701212511463 for a road with the OSM ID -647522

and 82 for a road with osm ID -6497265

I do get values between 0 and 1 too but I don't understand why I do also get such huge values.

What am I missing ?

I was expecting values between 1 and 0.

CodePudding user response：

Using a custom unique ID that you must populate, the query can also union eventually overlapping polygons:

SELECT                                                                
    l.uid,
    st_area(
        ST_UNION(
            st_intersection(ST_Buffer(l.way, 30), p.way))
    ) / st_area(ST_Buffer(l.way, 30)) as green_fraction
FROM planet_osm_line AS l
INNER JOIN planet_osm_polygon AS p 
    ON st_dwithin(l.way, p.way,30)
WHERE p.natural in ('water') or p.landuse in ('forest') 
GROUP BY l.uid;