2

I have this query:

select sum(way_area) from planet_osm_polygon 
      where building<>'no' and building<>'' 
      and way && st_transform(
           st_setsrid(st_makebox2d(
                st_point(2.2852565067385426, 50.990981849954764),
                st_point(2.2861549793351283, 50.990083377358175))
           ,4326)
      ,3857);

I would like to change it so that it only considers the area of the building that is in the zone, instead of the whole building. I suppose I should use st_intersects but how do I use it with my box to only have the little bits counted?

I tried this query but it gives me the same result as the first one (which is not what I need):

select sum(way_area) from planet_osm_polygon where building<>'no' and building<>'' and 
       st_intersects(
             st_transform(st_setsrid(st_makebox2d(st_point(2.2852565067385426, 50.990981849954764),  st_point(2.2861549793351283, 50.990083377358175)),4326),3857)
            ,way);

Additional info that may be useful: the way_area column contains the total surface on ground of a polygon, and the way column contains the geometry.

4
  • On a side note, 3857 is not suitable for computing areas.
    – JGH
    Jun 19 at 15:00
  • What could I use instead of 3857? I am very new to this.
    – DianeS
    Jun 19 at 15:24
  • 1
    You would have to find a suitable local projection that preserves areas (UTM or else)
    – JGH
    Jun 19 at 15:25
  • Not to bother you but would 54009 (Mollweide) do the job correctly? Is there another one native to postgis that I could use?
    – DianeS
    Jun 19 at 15:38

1 Answer 1

4

Since the && or st_interescts are located in the where clause, they will be used decide if a row from the table should or not be returned.

Using the returned rows, both queries instructs to compute the sum using the entire geometry of each row.

Instead, you will want to compute the area of the instersection between returned rows and the BBox and then to sum it.

Let's also move the BBox code to a CTE, so we have to write it once only:

WITH bbox(geom) as (
  SELECT st_transform(
           st_setsrid(st_makebox2d(
                st_point(2.2852565067385426, 50.990981849954764),
                st_point(2.2861549793351283, 50.990083377358175))
           ,4326)
      ,3857)
)
SELECT sum(st_area(st_intersection(way,bbox.geom))) 
FROM planet_osm_polygon
  JOIN bbox 
   ON ST_Intersects(way, bbox.geom)
WHERE building<>'no' and building<>'';
2
  • Thank you, but this query gives me ERROR: function st_intersection(real, geometry) does not exist LINE 9: SELECT sum(st_intersection(way_area,bbox.geom)) ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. I tried SELECT sum(st_intersection(way,bbox.geom)) but got sum(geometry) does not exist. I then tried sum(way_area) and got the same result... which is logical given the explanation in your answer.
    – DianeS
    Jun 19 at 15:18
  • 1
    Ah yes, you can't use the pre-computed area anymore. See edit for computing it
    – JGH
    Jun 19 at 15:24

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.