Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

Every time I started a query the connection with the database is lost. Anyone familiar with this problem?

This problem occurs with various query, for instance:

CREATE TABLE table
SELECT a.*, b.column, b.column 
FROM table_a a JOIN table b 
ON ST_intersects (a.geom, b.the_geom)
WHERE st_IsValid (a.geom);

I just updated from postgis 1.5.3 to 1.5.5 but that doesn't seem to help.

Anyone an idea?


UPDATE

I solved the problem.

There was something wrong with the topology of one of the layers. After editing the query worked without disconnecting.

I find it rather strange that the program and service crashes when the topology is not correct. Can someone shed a light on this issue.

share|improve this question
    
As noted below in the comments, the backend should never crash. If it's crashing on you that's a bug that we need to fix. Can you share the data that makes this happen? –  Paul Ramsey Nov 28 '12 at 16:46

1 Answer 1

Are you sure that you want to create a table before querying it? Try just this:

SELECT a.*, b.* 
FROM table_a a JOIN table_b b
ON ST_Intersects (a.geom, b.the_geom)
WHERE ST_IsValid (a.geom);

Otherwise go to the postgres.log and see if there are any errors reported and update your question according the log file.

Make sure that you select the proper columns. I guess b.column is not existent. Furthermore you were selecting it twice.

share|improve this answer
    
Yes I am sure I want to create a new table. I wasn't selecting it twice, the b.column statement was meant as an example :). –  Stefan Aug 23 '12 at 6:40
    
Then you should use the proper syntax in the future. You also should avoid to post examples which apparently do not work due to these obvious errors. I'm glad that you solved your problem anyway. –  mistapink Aug 23 '12 at 8:13
    
Thanks for you advice, next time I will write a more useful example :) But isn't it strange that when I use the wrong syntax that the connection is lost. Normally postgres/postgis just gives an error, and from that error I can improve my syntax. –  Stefan Aug 23 '12 at 8:42
1  
Sadly I don't have postgis installed here and I don't have the "corrupted" data you had. But mostly it depends on the configuration of your system and where the error occurs. If it is deep inside of postgis it is sometimes hard to use the postgres internal structures to catch the exception and then the database crashes. So you may found a bug :) –  mistapink Aug 23 '12 at 10:35
1  
The database shouldn't crash ever. If you can find the pairing of geometries that caused the crash, or can even just share the tables, please put them on a ticket on the PostGIS issue tracker. trac.osgeo.org/postgis –  Paul Ramsey Aug 23 '12 at 18:05

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.