# [postgis-users] ST_Disjoint function

Paragon Corporation lr at pcorp.us
Wed Mar 25 20:46:05 PDT 2009

```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

```