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

[Richard Greenwood]

Why not just have two geometry columns in one table? One column for the
actual geometry and another for the label.

On Fri, Sep 16, 2016 at 2:36 PM, Matthew Baker  wrote:

> 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 not commit changes to layer schools_district_map
>
> Errors: ERROR: 1 attribute value change(s) not applied.
>
> Provider errors: PostGIS error while changing attributes:
>
> ERROR:  infinite recursion detected in rules for relation 
> "schools_district_map"
>
>
> Here is the definition of the view :
>
> CREATE OR REPLACE VIEW public.schools_district_map AS
>  SELECT sch.schnum,
> sch.oid,
> sch.abbreviation,
> sch.school_level,
> sch.geom,
> l.label_x,
> l.label_y
>FROM temp_schools_label sch
>  LEFT JOIN district_map_labels l ON sch.schnum = l.schnum;
>
> And here are the rules I've applied to make the view 'editable':
>
> --delete rulecreate or replace rule "delete_label" as on delete to 
> schools_district_map do insteaddelete from schools_district_map where oid = 
> old.oid;
> --insert rule
> create or replace rule "insert_label" as on insert to schools_district_map do 
> insteadinsert into schools_district_map (label_x, label_y)values 
> (new.label_x, new.label_y);
>
> --update rulecreate or replace rule "labels_update" as on UPDATE TO 
> schools_district_map do instead update schools_district_map set
> label_x = new.label_x, label_y = new.label_ywhere oid = 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
>
>
>
> ___
> postgis-users mailing list
> postgis-users@lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/postgis-users
>



-- 
Richard W. Greenwood, PLS
www.greenwoodmap.com
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/postgis-users

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

[Russell Mercer]

Matt,

It looks like you are on the right track with your view and rules setup.  I
think your primary issue may be that each of the rules is trying to act on
the view to do the insert, update, delete, as opposed to acting on the
underlying table.

For example, instead of this:

--delete rulecreate or replace rule "delete_label" as on delete to
schools_district_map do insteaddelete from schools_district_map where
oid = old.oid;

Try this

--delete rule

create or replace rule "delete_label" as

on delete to schools_district_map do instead

delete from temp_schools_label

where oid = old.oid;

This way, you are directing the delete command to redirect to the
underlying table instead of sticking with the view itself, which will
always be read only.  If you change each of your rules to reflect the same,
you should have better luck.

Hope that helps,
Russell


On Fri, Sep 16, 2016 at 1:36 PM, Matthew Baker  wrote:

> 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 not commit changes to layer schools_district_map
>
> Errors: ERROR: 1 attribute value change(s) not applied.
>
> Provider errors: PostGIS error while changing attributes:
>
> ERROR:  infinite recursion detected in rules for relation 
> "schools_district_map"
>
>
> Here is the definition of the view :
>
> CREATE OR REPLACE VIEW public.schools_district_map AS
>  SELECT sch.schnum,
> sch.oid,
> sch.abbreviation,
> sch.school_level,
> sch.geom,
> l.label_x,
> l.label_y
>FROM temp_schools_label sch
>  LEFT JOIN district_map_labels l ON sch.schnum = l.schnum;
>
> And here are the rules I've applied to make the view 'editable':
>
> --delete rulecreate or replace rule "delete_label" as on delete to 
> schools_district_map do insteaddelete from schools_district_map where oid = 
> old.oid;
> --insert rule
> create or replace rule "insert_label" as on insert to schools_district_map do 
> insteadinsert into schools_district_map (label_x, label_y)values 
> (new.label_x, new.label_y);
>
> --update rulecreate or replace rule "labels_update" as on UPDATE TO 
> schools_district_map do instead update schools_district_map set
> label_x = new.label_x, label_y = new.label_ywhere oid = 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
>
>
>
> ___
> postgis-users mailing list
> postgis-users@lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/postgis-users
>
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/postgis-users

[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


___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/postgis-users