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
