[postgis-users] null to zero

[Bob Pawley]

Hi

I am attempting to establish the number of geometries intersecting various polygons and relating the number of geometries to the id of each polygon.

The below works for a single known polygon.

        select  coalesce (sum(st_numgeometries(foo.edge_data.geom)), 0) 
        from foo1, foo.edge_data
       where st_intersects(foo.edge_data.geom, foo1.five)
       and foo1.id = 58
(returns 0)

However I would like to select all the polygons and choose only those intersected by zero or one geometry.

But in the following, as soon as I use the” group by” clause, does not return the coalesced null values, it only returns geometries numbering 1 or more.

    select coalesce (sum(st_numgeometries(foo.edge_data.geom)), 0),  foo1.id
    from foo1, foo.edge_data
    where st_intersects(foo.edge_data.geom, foo1.five)
    and foo1.id = foo1.id
    group by foo1.id

Is there any way of reworking this to obtain polygon ids with null valued intersections??

Bob
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20130319/1f3c33b2/attachment.html>