[postgis-users] ST_Disjoint function

[Mark Leslie]

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