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

[Matthew Baker]

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 rulecreateorreplace 
> rule"delete_label"asondeletetoschools_district_map do instead 
> deletefromschools_district_map whereoid =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 <mattbaker at gmail.com 
> <mailto:mattbaker at gmail.com>> wrote:
>
>     Hi all,
>
>     I've asked this on GIS.SE <http://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
>     <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 at lists.osgeo.org <mailto:postgis-users at lists.osgeo.org>
>     http://lists.osgeo.org/mailman/listinfo/postgis-users
>     <http://lists.osgeo.org/mailman/listinfo/postgis-users>
>
>

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