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: [email protected] [mailto:[email protected]] 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 [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
