[postgis] Error while joining geo-columns ?

[Manuel Weindorf]

Hi,

is this normal ?


explain means the follwing:
explain select p1.geoid, p2.geoid from polygons p1, polygons p2 where 
p1.geo_value && p2.geo_value;
NOTICE:  QUERY PLAN:
 
Nested Loop  (cost=0.00..5197.21 rows=594872 width=32)
  ->  Seq Scan on polygons p1  (cost=0.00..189.39 rows=2439 width=16)
  ->  Index Scan using geo_index on polygons p2  (cost=0.00..2.04 rows=1 
width=16)
 
EXPLAIN


But the query:

select p1.geoid, p2.geoid 
from polygons p1, polygons p2 
where p1.geo_value && p2.geo_value;

results in:

pqReadData() -- backend closed the channel unexpectedly.
        This probably means the backend terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.


The serverlog says nothing special:

Server process (pid 11357) exited with status 11 at Thu Aug 30 16:36:37 2001
Terminating any active server processes...
Server processes were terminated at Thu Aug 30 16:36:37 2001
Reinitializing shared memory and semaphores
The Data Base System is starting up
DEBUG:  database system was interrupted at 2001-08-30 16:33:15 CEST
DEBUG:  CheckPoint record at (0, 31369604)
DEBUG:  Redo record at (0, 31369604); Undo record at (0, 0); Shutdown TRUE
DEBUG:  NextTransactionId: 24096; NextOid: 94297
DEBUG:  database system was not properly shut down; automatic recovery in 
progress...
DEBUG:  ReadRecord: record with zero len at (0, 31369668)
DEBUG:  redo is not required
DEBUG:  database system is in production state

I created the table as described by Dave (Upgrade from 0.5 to 0.6). 
After I imported a intermediate file from shp2pgsql into a postgis 0.6 
database, I created a new table without geometry column. I added the column 
as described, inserted the data (ca. 2500 Multipolygons) , created a 
gist-index and got the result above.

The following query works well:
select geoid from polygons 
where geo_value && setSRID('BOX3D(3487135 5346180,3488330 
5347695)'::BOX3D,123);

Has anybody any suggestions ??

thanks in advance

Manuel


------------------------ Yahoo! Groups Sponsor ---------------------~-->
Secure your servers with 128-bit SSL encryption! Grab your copy of VeriSign's FREE Guide: "Securing Your Web Site for Business." Get it Now!
http://us.click.yahoo.com/n7RbFC/zhwCAA/yigFAA/PhFolB/TM
---------------------------------------------------------------------~->

To unsubscribe from this group, send an email to:
postgis-unsubscribe at yahoogroups.com

 

Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/