I think it's a little easier than that. Use COUNT(DISTINCT somekey) to
count an record only once even when it is multiplied by the joining process.
SELECT a.district, count(DISTINCT b.gid) As num_disjoint
FROM planfile AS a INNER JON planfile AS b ON (a.district = b.district)
WHERE ST_Disjoint(a.the_geom, b.the_geom)
GROUP BY a.district;
Leo
-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Mark
Leslie
Sent: Wednesday, March 25, 2009 7:03 PM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] ST_Disjoint function
Lee Meilleur wrote:
>
> Hello,
>
> I'm running a contiguity check using the ST_Disjoint function. The
> results list the number of distinct polygons for a redistricting plan,
> some disjoint and some touching on a corner. I need to distinguish
> between the two and also show the total of all. To test this, I
> created a dataset that contains 2 sets of disjoint polygons. When I
> run the ST_Disjoint expression, the district with 3 disjoint polygons
> is listed as having 6, while the district with 2 disjoint polygons is
> listed as having 2. The feature type in this layer is polygon. The
> expression I'm using is:
>
> SELECT a.district, count(a.district) FROM planfile AS a, planfile AS b
> WHERE a.district = b.district AND ST_Disjoint(a.the_geom, b.the_geom)
> GROUP BY a.district;
>
> Results:
>
> district | count
> ----------+----------
> 17B | 6 (incorrect)
> 26B | 2 (correct)
>
> Am I missing something? Viewing the data in Quantum GIS shows three
> disjoint polygons for district 17B and three unique records in the
> attribute table.
>
> I took this further and added another set of disjoint polygons. At
> first just two, and the ST_Disjoint function shows two. But when I
> increased it to four, the ST_Disjoint function came up with twelve.
>
> I've tested this on two different PostGIS servers with the same results:
>
> POSTGIS="1.3.2" GEOS="3.0.0-CAPI-1.4.1" PROJ="Rel. 4.6.0 Dec 2007
> USE_STATS POSTGIS="1.3.5" GEOS="3.0.3-CAPI-1.4.2" PROJ="Rel. 4.6.1 21
> August 2008 "USE_STATS
>
> Thanks in advance for any help.
>
> Lee Meilleur
You are looking for a count of the total number of disjoint polygons for
each district id, while you're asking for the total number of disjoint
polygon pairs for each district id, as below:
create table blah (id serial, district varchar, point geometry);
insert into blah (district, point) values ('17B', 'POINT(0 0)'), ('17B',
'POINT(1 0)'), ('17B', 'POINT(2 0)');
select a.district, count(a.district) from blah as a, blah as b where
a.district = b.district AND st_disjoint(a.point, b.point) group by
a.district;
district | count
----------+-------
17B | 6
id | id | district
----+----+----------
1 | 3 | 17B
1 | 2 | 17B
2 | 3 | 17B
2 | 1 | 17B
3 | 2 | 17B
3 | 1 | 17B
Assuming you have some sort of unique id, try:
select c.district, count(*) from (select distinct a.id, a.district from
blah as a, blah as b where a.district = b.district AND st_disjoint(a.point,
b.point)) as c group by district;
district | count
----------+-------
17B | 3
Mark Leslie
Geospatial Software Architect
LISAsoft
-------------------------------------------------------------
Ph: +61 2 8570 5000 Fax: +61 2 8570 5099 Mob: +61 Suite 112, Jones Bay Wharf
19-21 Pirrama Rd Pyrmont NSW 2009
-------------------------------------------------------------
LISAsoft is part of the A2end Group of Companies http://www.ardec.com.au
http://www.lisasoft.com http://www.terrapages.com
_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users