Trouble with PostGIS and multiple selection sources

Question

I'm attempting to take geometry columns from two different tables, perform basic PostGIS operations on them, and have them returned in a single result set.

My SQL statement looks like the following:

SELECT (ST_Dump(ST_Polygonize(exterior_rings.the_geom))).geom AS exterior,
       (ST_Dump(ST_Polygonize(interior_rings.the_geom))).geom AS interior

FROM (SELECT the_geom FROM geodata."_unioned_exterior_rings") AS exterior_rings, 
     (SELECT the_geom FROM geodata."_unioned_interior_rings") AS interior_rings;

When I execute this command in pgAdmin III's arbitrary SQL query tool, after roughly 8 minutes of execution, I receive:

********** Error **********

... and the query tool complains it is no longer connected to the database. This leads me to believe the operation is timing out. The respective sizes of the tables are fairly humble. _unioned_exterior_rings has ~4000 rows, and _unioned_interior_rings has ~100. I've had other queries succeed after twice as much run time so I don't feel as if I'm hitting some preset limit.

When I use pgAdmin's console tool to execute the query, after some time it becomes apparent that the entire tool has been disconnected without any kind of error message. On some reconnect attempts I've received notices that the reconnection fails because the database is in a fatal recover mode. If I try again shortly there after it usually works.

When I execute the statements separately (doing the polygonize/dump on an individual table) I get the expected results after only a few seconds.

The version of PostgreSQL is 8.4, PostGIS is 1.4.2, and pgAdmin III is at 1.12.2.

I can't claim any real SQL proficiency, so I'm willing accept that my mistake may be simple or fundamental. Any help would be appreciated.

Answer 1

This is just a quick glance, but I think that you might want to think about the strategy of your query.

I am guessing that since you are pulling features from two different tables and you are not specifying any join criteria, you may be getting a Cartesian join. In other words, for each feature in the first table, you are joining it to every feature in the second table. The result is lots of records (and relatively expensive calculations for each record).

If you really just want to combine the polygons for the interior rings and the exterior rings, you might try using a union query. Something like this?:

SELECT (ST_Dump(ST_Polygonize(exterior_rings.the_geom))).geom AS the_geom
  FROM geodata."_unioned_exterior_rings"
 UNION
SELECT (ST_Dump(ST_Polygonize(interior_rings.the_geom))).geom AS interior
  FROM geodata."_unioned_interior_rings" AS the_geom;

This is untested, but might be more the concept that you are looking for.

Answer 2

if i'm reading your post and reply to david correctly, you're trying to make "donut" polygons from two tables, one of exterior rings and the other their corresponding interior rings. right?

using the PostGIS documentation entry on ST_MakePolygon (http://postgis.refractions.net/docs/ST_MakePolygon.html) as a guide, the following should work for you:

SELECT
  CASE WHEN
    ST_Accum(ST_LineMerge(ST_Boundary(i.the_geom))) IS NULL THEN e.the_geom
  ELSE ST_MakePolygon(ST_LineMerge(ST_Boundary(e.the_geom)),ST_Accum(ST_LineMerge(ST_Boundary(i.the_geom))))
FROM _unioned_exterior_rings e LEFT JOIN _unioned_interior_rings i
  ON(ST_Within(i.the_geom,e.the_geom));

per the documentation, the case statement is required because ST_MakePolygon will flop if it receives a null array. the left join makes sure exterior rings w/no corresponding inner rings are not excluded.

the combination of ST_Accum, ST_LineMerge and ST_Boundary takes your interior rings, converts them to multi-linestrings, then stuffs them into arrays. the first argument of ST_MakePolygon is the geometry of closed linestrings representing your exterior rings.

good luck!

-rob