[postgis-users] Creating an 'editable' view in PostGIS

[Matthew Baker]

Hi all,

I've asked this on GIS.SE, to no avail:

I'd like to store label placement properties in a separate table, build 
a view of the data I want to use for my map, and use QGIS to move the 
labels manually. The idea is when the labels are updated, the placement 
coordinates would go into the LABEL_X and LABEL_Y fields in the label 
placement table, but the geometry of the underlying points and several 
other attributes (coming from the source tables) would remain unchanged.

However, when all is in place, QGIS throws the following error when 
trying to save the edits to the view (the edit session can start, labels 
moved, but cannot save):

|Could notcommitchanges tolayer schools_district_map 
Errors:ERROR:1attribute value change(s)notapplied.Provider 
errors:PostGIS error whilechanging attributes:ERROR:infinite recursion 
detected inrules forrelation "schools_district_map" |||

Here is the definition of the view :

|CREATEORREPLACE VIEWpublic.schools_district_map 
ASSELECTsch.schnum,sch.oid,sch.abbreviation,sch.school_level,sch.geom,l.label_x,l.label_y 
FROMtemp_schools_label sch LEFTJOINdistrict_map_labels l ONsch.schnum 
=l.schnum;|

And here are the rules I've applied to make the view 'editable':

|--delete rulecreateorreplace 
rule"delete_label"asondeletetoschools_district_map do instead 
deletefromschools_district_map whereoid =old.oid;--insert 
rulecreateorreplace rule"insert_label"asoninserttoschools_district_map 
do instead insertintoschools_district_map 
(label_x,label_y)values(new.label_x,new.label_y);--update 
rulecreateorreplace rule"labels_update"asonUPDATETOschools_district_map 
do instead updateschools_district_map setlabel_x =new.label_x ,label_y 
=new.label_y whereoid =new.oid;|

QGIS is then set to display the labels using the label_x and label_y field.

I used this post as a guide to build the view, rules:

http://gis.stackexchange.com/questions/88120/how-to-set-posgis-default-sequential-value-in-a-qgis-editable-view

If anyone can spot where I might have left something out, or if there is 
a glaring oversight on my part, OR if this is maybe a bad idea... let me 
know!

Thank you!!!

-Matt Baker
Denver Public Schools
Denver, CO


-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20160916/e87946ae/attachment.html>