[postgis-users] null to zero

[Bob Pawley]

Thanks Francois

Your suggestion worked.

It really helps for someone to look at the trees while I am only able to see 
the forest.

Bob

-----Original Message----- 
From: Francois Hugues
Sent: Tuesday, March 19, 2013 12:06 PM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] null to zero

Maybe, you will have to use count(foo.edge_data.geom) instead of count(*) as 
Tom underlined it before.

Hugues

Francois Hugues <hugues.francois at irstea.fr> a écrit :

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
_______________________________________________
postgis-users mailing list
postgis-users at lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
postgis-users at lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users