<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=utf-8">
</head>
<body bgcolor="#FFFFFF" text="#000000">
<p><font face="Arial">Hi all,</font></p>
<p><font face="Arial">I've asked this on GIS.SE, 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 style="" class="lang-sql prettyprint prettyprinted"><code><span class="pln">Could </span><span class="kwd">not</span><span class="pln"> </span><span class="kwd">commit</span><span class="pln"> changes </span><span class="kwd">to</span><span class="pln"> layer schools_district_map
Errors</span><span class="pun">:</span><span class="pln"> ERROR</span><span class="pun">:</span><span class="pln"> </span><span class="lit">1</span><span class="pln"> attribute value change</span><span class="pun">(</span><span class="pln">s</span><span class="pun">)</span><span class="pln"> </span><span class="kwd">not</span><span class="pln"> applied</span><span class="pun">.</span><span class="pln">
Provider errors</span><span class="pun">:</span><span class="pln"> PostGIS error </span><span class="kwd">while</span><span class="pln"> changing attributes</span><span class="pun">:</span><span class="pln">
ERROR</span><span class="pun">:</span><span class="pln"> infinite recursion detected </span><span class="kwd">in</span><span class="pln"> rules </span><span class="kwd">for</span><span class="pln"> relation </span><span class="str">"schools_district_map"
</span></code><code><span class="str"></span></code></pre>
<p><font face="Arial">Here is the definition of the view :</font></p>
<pre style="" class="lang-sql prettyprint prettyprinted"><font face="Courier New"><code><span class="kwd">CREATE</span><span class="pln"> </span><span class="kwd">OR</span><span class="pln"> REPLACE </span><span class="kwd">VIEW</span><span class="pln"> </span><span class="kwd">public</span><span class="pun">.</span><span class="pln">schools_district_map </span><span class="kwd">AS</span><span class="pln">
</span><span class="kwd">SELECT</span><span class="pln"> sch</span><span class="pun">.</span><span class="pln">schnum</span><span class="pun">,</span><span class="pln">
sch</span><span class="pun">.</span><span class="pln">oid</span><span class="pun">,</span><span class="pln">
sch</span><span class="pun">.</span><span class="pln">abbreviation</span><span class="pun">,</span><span class="pln">
sch</span><span class="pun">.</span><span class="pln">school_level</span><span class="pun">,</span><span class="pln">
sch</span><span class="pun">.</span><span class="pln">geom</span><span class="pun">,</span><span class="pln">
l</span><span class="pun">.</span><span class="pln">label_x</span><span class="pun">,</span><span class="pln">
l</span><span class="pun">.</span><span class="pln">label_y
</span><span class="kwd">FROM</span><span class="pln"> temp_schools_label sch
</span><span class="kwd">LEFT</span><span class="pln"> </span><span class="kwd">JOIN</span><span class="pln"> district_map_labels l </span><span class="kwd">ON</span><span class="pln"> sch</span><span class="pun">.</span><span class="pln">schnum </span><span class="pun">=</span><span class="pln"> l</span><span class="pun">.</span><span class="pln">schnum</span><span class="pun">;</span></code></font></pre>
<p><font face="Arial">And here are the rules I've applied to make
the view 'editable':</font></p>
<pre style="" class="lang-sql prettyprint prettyprinted"><code><span class="com">--delete rule</span><span class="pln">
</span><span class="kwd">create</span><span class="pln"> </span><span class="kwd">or</span><span class="pln"> replace </span><span class="kwd">rule</span><span class="pln"> </span><span class="str">"delete_label"</span><span class="pln"> </span><span class="kwd">as</span><span class="pln">
</span><span class="kwd">on</span><span class="pln"> </span><span class="kwd">delete</span><span class="pln"> </span><span class="kwd">to</span><span class="pln"> schools_district_map do instead
</span><span class="kwd">delete</span><span class="pln"> </span><span class="kwd">from</span><span class="pln"> schools_district_map
</span><span class="kwd">where</span><span class="pln"> oid </span><span class="pun">=</span><span class="pln"> old</span><span class="pun">.</span><span class="pln">oid</span><span class="pun">;</span><span class="pln">
</span><span class="com">--insert rule</span><span class="pln">
</span><span class="kwd">create</span><span class="pln"> </span><span class="kwd">or</span><span class="pln"> replace </span><span class="kwd">rule</span><span class="pln"> </span><span class="str">"insert_label"</span><span class="pln"> </span><span class="kwd">as</span><span class="pln">
</span><span class="kwd">on</span><span class="pln"> </span><span class="kwd">insert</span><span class="pln"> </span><span class="kwd">to</span><span class="pln"> schools_district_map do instead
</span><span class="kwd">insert</span><span class="pln"> </span><span class="kwd">into</span><span class="pln"> schools_district_map </span><span class="pun">(</span><span class="pln">label_x</span><span class="pun">,</span><span class="pln"> label_y</span><span class="pun">)</span><span class="pln">
</span><span class="kwd">values</span><span class="pln"> </span><span class="pun">(</span><span class="pln">new</span><span class="pun">.</span><span class="pln">label_x</span><span class="pun">,</span><span class="pln"> new</span><span class="pun">.</span><span class="pln">label_y</span><span class="pun">);</span><span class="pln">
</span><span class="com">--update rule</span><span class="pln">
</span><span class="kwd">create</span><span class="pln"> </span><span class="kwd">or</span><span class="pln"> replace </span><span class="kwd">rule</span><span class="pln"> </span><span class="str">"labels_update"</span><span class="pln"> </span><span class="kwd">as</span><span class="pln">
</span><span class="kwd">on</span><span class="pln"> </span><span class="kwd">UPDATE</span><span class="pln"> </span><span class="kwd">TO</span><span class="pln"> schools_district_map do instead
</span><span class="kwd">update</span><span class="pln"> schools_district_map </span><span class="kwd">set</span><span class="pln">
label_x </span><span class="pun">=</span><span class="pln"> new</span><span class="pun">.</span><span class="pln">label_x
</span><span class="pun">,</span><span class="pln"> label_y </span><span class="pun">=</span><span class="pln"> new</span><span class="pun">.</span><span class="pln">label_y
</span><span class="kwd">where</span><span class="pln"> oid </span><span class="pun">=</span><span class="pln"> new</span><span class="pun">.</span><span class="pln">oid</span><span class="pun">;</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 class="moz-txt-link-freetext" href="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</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>
</body>
</html>