[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 <mattbaker at gmail.com> 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 at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/postgis-users
>



-- 
Richard W. Greenwood, PLS
www.greenwoodmap.com
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20160918/21ab33b8/attachment.html>