Search code examples
sqlpostgresqlpostgis

Is there a way to select SUM with a ST_INTERSECTS restriction?

I will try to explain my problem. I'm trying to select a SUM of attributes on a column with an ST_intersect restriction with this expression;


with inter as (
select
    planet_osm_point.way pw,
    (select sum (planet_osm_point.osm_id)
    from planet_osm_polygon, planet_osm_point
    where st_intersects (planet_osm_point.way, planet_osm_polygon.way)) as sum1
    from planet_osm_point
    )
    select pw, sum1
    from inter, planet_osm_point
    group by pw, sum1

This returns me the same SUM of all osm_id for each planet_osm_point.way. I want to sum only the osm_id that intersects with the polygon and group by planet_osm_point.way.

Any ideas?

Thanks!

Solution

  • I believe a simple spatial join would suffice, e.g.

    SELECT pol.way, sum(poi.osm_id)
FROM planet_osm_point poi, planet_osm_polygon pol
WHERE ST_Intersects(poi.way,pol.way)
GROUP BY pol.way