Creating an 'editable' view in PostGIS

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

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 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 rule
create or replace rule "delete_label" as 
on delete to schools_district_map do instead
delete from schools_district_map 
where oid = old.oid;

--insert rule

create or replace rule "insert_label" as 
on insert to schools_district_map do instead
insert into schools_district_map (label_x, label_y)
values (new.label_x, new.label_y);


--update rule
create 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_y
where 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
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: 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 rule
create or replace rule "delete_label" as 
on delete to schools_district_map do instead
delete 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 <[hidden email]> 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 rule
create or replace rule "delete_label" as 
on delete to schools_district_map do instead
delete from schools_district_map 
where oid = old.oid;

--insert rule

create or replace rule "insert_label" as 
on insert to schools_district_map do instead
insert into schools_district_map (label_x, label_y)
values (new.label_x, new.label_y);


--update rule
create 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_y
where 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
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-users


_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: Creating an 'editable' view in PostGIS

rgreenwood
In reply to this post by Matthew Baker
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 <[hidden email]> 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 rule
create or replace rule "delete_label" as 
on delete to schools_district_map do instead
delete from schools_district_map 
where oid = old.oid;

--insert rule

create or replace rule "insert_label" as 
on insert to schools_district_map do instead
insert into schools_district_map (label_x, label_y)
values (new.label_x, new.label_y);


--update rule
create 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_y
where 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
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-users



--
Richard W. Greenwood, PLS
www.greenwoodmap.com

_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

SUM: Creating an 'editable' view in PostGIS

Matthew Baker
In reply to this post by Russell Mercer

All,

Thanks to Russel Mercer who corrected my logic - this works great, and we'll be implementing this for several sets of map label placement! (see response below).

Regarding Richard Greenwood's suggestion that we use geometry to store the label placement - in theory and a bit of practice, the QGIS x($geometry) / y($geometry) label placement expression values *do* work for displaying labels - but QGIS doesn't allow you to move the labels when they are assigned to the geometry x,y - so we would be moving the feature itself, which doesn't lend itself to an intuitive label placement workflow.

Added to that, what we're trying to do is build a table we use for cartography that pulls from 3 separate tables - one of which will store all the label placement properties for upwards of 8 printed maps, all at various scales and sizes.

So for the time being, using LABEL_X, and LABEL_Y fields and updating the properties using QGIS and editable views works great!

Thanks again everyone!

-Matt Baker

Denver, CO



On 9/16/2016 3:37 PM, Russell Mercer wrote:
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 rule
create or replace rule "delete_label" as 
on delete to schools_district_map do instead
delete 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 <[hidden email]> 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 rule
create or replace rule "delete_label" as 
on delete to schools_district_map do instead
delete from schools_district_map 
where oid = old.oid;

--insert rule

create or replace rule "insert_label" as 
on insert to schools_district_map do instead
insert into schools_district_map (label_x, label_y)
values (new.label_x, new.label_y);


--update rule
create 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_y
where 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
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-users



_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-users