[postgis-users] Spatial Join Performance

[Eric Shuman]

Paul,

I did try using distance function instead of contains and the query
completed much faster!  30 minutes instead of 2.5 hours. Thanks for the
tip!!!

But... =>
Another issue was brought up earlier in this thread mentioning that not all
taxlot will be in just one zone... which has now brought up another road
block. I have been taking care of this issue by selecting distinct on
(taxlot,zone) then grouping on the taxlot and concatenating the zone with a
delimiter.

The issue now is that comparing on a point won't pick up all the zones (I
was just going to accept this), but comparing on the polygon gets false hits
due to topological shifting and errors.  I am experimenting with creating a
negative buffer and comparing its distance to the zone.  This works on a
test subject lot.

The road block is this...  When I try to run the query on my entire data set
I get this error.
-----------------------------------------------
server closed the connection unexpectedly
	This probably means the server terminated abnormally
	before or while processing the request.
-----------------------------------------------

To speed up the buffer I have also simplified the_geom.(this works)  I broke
the steps down by creating additional geometry fields in my taxlot layer to
hold the simplified geometry and then the buffered geometry. The simplify
step works, the buffer is where it bails.

The taxlot geometries as multipolygon.  I ran an isvalid() on the geometry
and found some to be invalid, but if I exclude them from the query I still
get the server crash.

Here are the queries:
--SELECT AddGeometryColumn('','taxlot','the_simple_geom','-1','GEOMETRY',2);
--update taxlot set the_simple_geom = simplify(taxlot.the_geom,5);
--SELECT AddGeometryColumn('','taxlot','the_buffer_geom','-1','GEOMETRY',2);
--update taxlot set the_buffer_geom = buffer(taxlot.the_simple_geom,-5);
BAILS HERE


Any ideas on what might be wrong???

(should this be a new thread?)

---------------
Eric Shuman