Tell me more ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.
up vote 1 down vote favorite

I am having trouble rendering the following PostGIS query in qgis1.803.

SELECT
s.musym,
st_intersection(s.the_geom,f.the_geom) as geo,
nextval('"soilsplittersequence"'::regclass) as k
From public."SoilsSpatial" as s JOIN public."Fields" as f 
ON ST_Intersects(s.the_geom, f.the_geom) 
where f."FarmNumber"=37

When I run this query in the db manager module it returns 500 ish results, but I can't display it. I also can't display this query if I use it to create a view in my postgis database.

The only way I can get it to display is if I make the query into a create table.

I'm assuming that the problem is related to my use of a sequence to generate a unique id. The reason that I'm using this technique is that there is a many to many relationship between the two tables.

Thanks for the advice.

share|improve this question
2  
What happens when you try and view it in QGIS as a live query? Do you get a error? – Nathan W Sep 25 '12 at 20:56
When I execute the query in the db Manager module of qgis I don't receive any error messages and get a full table of results, but when I try to load the results into the canvas I get: Fetching from cursor qgisf0 failed Database error: ERROR: cannot execute nextval() in a read-only transaction – avery Sep 26 '12 at 13:03
The error makes sense. I'm guessing DB Manager is only using read-only transactions but nextval() will change the database (increments the sequence). You'll want to remove the nextval() and try again. – dustymugs Oct 3 '12 at 14:25
Do you need an integer id in the query? – DavidF Oct 12 '12 at 2:38

Know someone who can answer? Share a link to this question via email, Google+, Twitter, or Facebook.

Your Answer

 
discard

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

Browse other questions tagged or ask your own question.