<html>
<head>
<meta content="text/html; charset=utf-8" http-equiv="Content-Type">
</head>
<body bgcolor="#FFFFFF" text="#000000">
<p><font face="Arial">All,</font></p>
<p><font face="Arial">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).</font></p>
<p><font face="Arial">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.</font></p>
<p><font face="Arial">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. <br>
</font></p>
<p><font face="Arial">So for the time being, using LABEL_X, and LABEL_Y
fields and updating the properties using QGIS and editable views
works great!</font></p>
<p><font face="Arial">Thanks again everyone!</font></p>
<p><font face="Arial">-Matt Baker</font></p>
<p><font face="Arial">Denver, CO</font></p>
<p><font face="Arial"><br>
</font></p>
<br>
<div class="moz-cite-prefix">On 9/16/2016 3:37 PM, Russell Mercer
wrote:<br>
</div>
<blockquote
cite="mid:CAB5KxtVJoo9xDM5GnfnSevqtCb56k=_zceKOJbpGKei_0r95yg@mail.gmail.com"
type="cite">
<div dir="ltr">
<div>
<div>Matt,<br>
<br>
</div>
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.<br>
<br>
</div>
For example, instead of this:<br>
<br>
<pre><code><span>--delete rule</span><span>
</span><span>create</span><span> </span><span>or</span><span> replace </span><span>rule</span><span> </span><span>"delete_label"</span><span> </span><span>as</span><span>
</span><span>on</span><span> </span><span>delete</span><span> </span><span>to</span><span> schools_district_map do instead
</span><span>delete</span><span> </span><span>from</span><span> schools_district_map
</span><span>where</span><span> oid </span><span>=</span><span> old</span><span>.</span><span>oid</span><span>;
</span></code></pre>
<pre><code><span>Try this
</span></code></pre>
<pre><code><span>--delete rule
</span></code></pre>
<pre><code><span>create or replace rule "delete_label" as
</span></code></pre>
<pre><code><span>on delete to schools_district_map do instead
</span></code></pre>
<pre><code><span>delete from temp_schools_label
</span></code></pre>
<pre><code><span>where oid = old.oid;
</span></code></pre>
<div>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.<br>
<br>
</div>
<div>Hope that helps,<br>
</div>
<div>Russell<br>
</div>
<div><br>
</div>
</div>
<div class="gmail_extra"><br>
<div class="gmail_quote">On Fri, Sep 16, 2016 at 1:36 PM,
Matthew Baker <span dir="ltr"><<a moz-do-not-send="true"
href="mailto:mattbaker@gmail.com" target="_blank">mattbaker@gmail.com</a>></span>
wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0
.8ex;border-left:1px #ccc solid;padding-left:1ex">
<div bgcolor="#FFFFFF" text="#000000">
<p><font face="Arial">Hi all,</font></p>
<p><font face="Arial">I've asked this on <a
moz-do-not-send="true" href="http://GIS.SE"
target="_blank">GIS.SE</a>, to no avail:</font></p>
<p><font face="Arial">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.<br>
<br>
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):<br>
</font></p>
<pre><code><span>Could </span><span>not</span><span> </span><span>commit</span><span> changes </span><span>to</span><span> layer schools_district_map
Errors</span><span>:</span><span> ERROR</span><span>:</span><span> </span><span>1</span><span> attribute value change</span><span>(</span><span>s</span><span>)</span><span> </span><span>not</span><span> applied</span><span>.</span><span>
Provider errors</span><span>:</span><span> PostGIS error </span><span>while</span><span> changing attributes</span><span>:</span><span>
ERROR</span><span>:</span><span> infinite recursion detected </span><span>in</span><span> rules </span><span>for</span><span> relation </span><span>"schools_district_map"
</span></code><code><span></span></code></pre>
<p><font face="Arial">Here is the definition of the view :</font></p>
<pre><font face="Courier New"><code><span>CREATE</span><span> </span><span>OR</span><span> REPLACE </span><span>VIEW</span><span> </span><span>public</span><span>.</span><span>schools_district_map </span><span>AS</span><span>
</span><span>SELECT</span><span> sch</span><span>.</span><span>schnum</span><span>,</span><span>
sch</span><span>.</span><span>oid</span><span>,</span><span>
sch</span><span>.</span><span>abbreviation</span><span>,</span><span>
sch</span><span>.</span><span>school_level</span><span>,</span><span>
sch</span><span>.</span><span>geom</span><span>,</span><span>
l</span><span>.</span><span>label_x</span><span>,</span><span>
l</span><span>.</span><span>label_y
</span><span>FROM</span><span> temp_schools_label sch
</span><span>LEFT</span><span> </span><span>JOIN</span><span> district_map_labels l </span><span>ON</span><span> sch</span><span>.</span><span>schnum </span><span>=</span><span> l</span><span>.</span><span>schnum</span><span>;</span></code></font></pre>
<p><font face="Arial">And here are the rules I've applied
to make the view 'editable':</font></p>
<pre><code><span>--delete rule</span><span>
</span><span>create</span><span> </span><span>or</span><span> replace </span><span>rule</span><span> </span><span>"delete_label"</span><span> </span><span>as</span><span>
</span><span>on</span><span> </span><span>delete</span><span> </span><span>to</span><span> schools_district_map do instead
</span><span>delete</span><span> </span><span>from</span><span> schools_district_map
</span><span>where</span><span> oid </span><span>=</span><span> old</span><span>.</span><span>oid</span><span>;</span><span>
</span><span>--insert rule</span><span>
</span><span>create</span><span> </span><span>or</span><span> replace </span><span>rule</span><span> </span><span>"insert_label"</span><span> </span><span>as</span><span>
</span><span>on</span><span> </span><span>insert</span><span> </span><span>to</span><span> schools_district_map do instead
</span><span>insert</span><span> </span><span>into</span><span> schools_district_map </span><span>(</span><span>label_x</span><span>,</span><span> label_y</span><span>)</span><span>
</span><span>values</span><span> </span><span>(</span><span>new</span><span>.</span><span>label_x</span><span>,</span><span> new</span><span>.</span><span>label_y</span><span>);</span><span>
</span><span>--update rule</span><span>
</span><span>create</span><span> </span><span>or</span><span> replace </span><span>rule</span><span> </span><span>"labels_update"</span><span> </span><span>as</span><span>
</span><span>on</span><span> </span><span>UPDATE</span><span> </span><span>TO</span><span> schools_district_map do instead
</span><span>update</span><span> schools_district_map </span><span>set</span><span>
label_x </span><span>=</span><span> new</span><span>.</span><span>label_x
</span><span>,</span><span> label_y </span><span>=</span><span> new</span><span>.</span><span>label_y
</span><span>where</span><span> oid </span><span>=</span><span> new</span><span>.</span><span>oid</span><span>;</span></code></pre>
<p><font face="Arial">QGIS is then set to display the
labels using the label_x and label_y field.</font></p>
<p><font face="Arial">I used this post as a guide to build
the view, rules: <br>
</font></p>
<p><font face="Arial"><a moz-do-not-send="true"
href="http://gis.stackexchange.com/questions/88120/how-to-set-posgis-default-sequential-value-in-a-qgis-editable-view"
target="_blank">http://gis.stackexchange.com/<wbr>questions/88120/how-to-set-<wbr>posgis-default-sequential-<wbr>value-in-a-qgis-editable-view</a></font></p>
<p><font face="Arial">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!</font></p>
<p><font face="Arial">Thank you!!!</font></p>
<p><font face="Arial">-Matt Baker<br>
Denver Public Schools<br>
Denver, CO<br>
</font></p>
<p><br>
</p>
</div>
<br>
______________________________<wbr>_________________<br>
postgis-users mailing list<br>
<a moz-do-not-send="true"
href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><br>
<a moz-do-not-send="true"
href="http://lists.osgeo.org/mailman/listinfo/postgis-users"
rel="noreferrer" target="_blank">http://lists.osgeo.org/<wbr>mailman/listinfo/postgis-users</a><br>
</blockquote>
</div>
<br>
</div>
</blockquote>
<br>
</body>
</html>