[postgis-users] null to zero

[Francois Hugues]

Hello,

I'm not sure to understand your problem but maybe st_numgeometries is not the right function to use since it counts geometries. Within a multipolygon. 

Maybe am i wrong but why don't you use count() like a similar problem suggested it recently ?

Select foo1.id, coalesce(count(*), 0)
>From foo1
Left join foo.edge_data on st_intersects(foo1.five, foo.effet_data.geom)
Group by foo1.id

Hugues 

Bob Pawley <rjpawley at shaw.ca> a écrit :

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