<div dir="ltr">Why not just have two geometry columns in one table? One column for the actual geometry and another for the label.</div><div class="gmail_extra"><br><div class="gmail_quote">On Fri, Sep 16, 2016 at 2:36 PM, Matthew Baker <span dir="ltr"><<a 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 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 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>
<font face="Arial"></font>
<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 href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><br>
<a 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><br clear="all"><div><br></div>-- <br><div class="gmail_signature" data-smartmail="gmail_signature"><div dir="ltr">Richard W. Greenwood, PLS<br><a href="http://www.greenwoodmap.com" target="_blank">www.greenwoodmap.com</a></div></div>
</div>