1

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.

  • 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.