<div dir="ltr"><div>Hi Nicolas,</div><div><br></div><div>Thanks for the detailed description that make the unclear clear...</div><div><br></div><div>Now I feel ready to implement ...<br></div><div><br></div><div>Your support is a great example for the strength of the open source community ...</div><div><br></div><div>BR.</div><div><br></div><div>Ofer</div></div><div class="gmail_extra"><br><div class="gmail_quote">On Tue, Feb 10, 2015 at 11:07 AM, Nicolas Ribot <span dir="ltr"><<a href="mailto:nicolas.ribot@gmail.com" target="_blank">nicolas.ribot@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 dir="ltr">Hi Ofer,<div><br></div><div>Some precisions regarding the query I sent:</div><div><br></div><div>A topoGeometry objects stores the following information (<a href="http://postgis.net/docs/manual-2.1/topogeometry.html" target="_blank">http://postgis.net/docs/manual-2.1/topogeometry.html</a>):</div><div><br></div><div>• topopology_id: id of the topology, as stored in the Topology metadata</div><div>• layer_id : the id of the layer in the topology<br></div><div>• id: the id of the topogeometry object</div><div>• type: the geometry type (point, line...)</div><div><br></div><div>When joining between the Polygon table containing the TopoGeometryObject, I used a notation seen on this list to compare 2 values:</div><div>(a, b) = (c, d), equivalent to a = b and c = d.</div><div>The "1" in the first part (pe.parcelle p1 on (1, (p1.topo).id)) is the hard-coded (not very smart: I copied a test query I used) identifier of the Topology layer in the topology.layer metadata table.</div><div><br></div><div>I obtained this value by running:</div><div>select layer_id from topology.layer where schema_name = 'pe' and table_name = 'parcelle;</div><div><br></div><div>So, in your case, check the layer_id of the department table and use this value.</div><span class="HOEnZb"><font color="#888888"><div><br></div><div>Nicolas</div><div><br></div></font></span></div><div class="HOEnZb"><div class="h5"><div class="gmail_extra"><br><div class="gmail_quote">On 9 February 2015 at 21:18, Ofer Zilberstein <span dir="ltr"><<a href="mailto:zilberstein.ofer@gmail.com" target="_blank">zilberstein.ofer@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 dir="ltr">Hi,<div><br></div><div>1. I advanced ...and I used the <span style="font-size:12.8000001907349px"> topology.toTopoGeom() and now the relation is populated.</span><span style="font-size:12.8000001907349px"> </span></div><div><span style="font-size:12.8000001907349px"><br></span></div><div><span style="font-size:12.8000001907349px">2. I try to understand the following join </span></div><span><div><span style="font-size:12.8000001907349px"><br></span></div><div><span style="font-size:large;line-height:11.5200004577637px;font-family:Calibri;color:black"><b>pe.parcelle</b></span><span style="font-size:large;line-height:11.5200004577637px;font-family:Calibri;color:black"><b> p1 on (1, (p1.topo).id) </b>= (r1.layer_id, r1.topogeo_id)</span><span style="font-size:12.8000001907349px"><br></span></div><div><span style="font-size:large;line-height:11.5200004577637px;font-family:Calibri;color:black"><br></span></div></span><div><font color="#000000" face="Calibri" size="4"><span style="line-height:11.5200004577637px">the second part of the join is clear -- comes from the relation table</span></font></div><div><font color="#000000" face="Calibri" size="4"><span style="line-height:11.5200004577637px">however the first part - is not so clear</span></font></div><div><font color="#000000" face="Calibri" size="4"><span style="line-height:11.5200004577637px"><br></span></font></div><div><span style="font-size:12.8000001907349px">according to you - "parcelle is the polygon table with a topo column containing the TopoGeometry object corresponding to the polygon" (you have used here pe.parcelle - is it the table name ? )</span><br></div><div><span style="font-size:12.8000001907349px"><br></span></div><div><span style="font-size:12.8000001907349px">so if in my case I have table 'department' with column 'topogeom' it would look like</span><br></div><div><span style="font-size:12.8000001907349px"><br></span></div><div><span style="font-size:12.8000001907349px">department d1 on (1,(d1.topogeom).id) = </span><span style="color:rgb(0,0,0);font-family:Calibri;font-size:large;line-height:11.5200004577637px">(r1.layer_id, r1.topogeo_id) ?</span><span><font color="#888888"><span style="font-size:12.8000001907349px"><br></span></font></span></div><span><font color="#888888"><div><br></div><div><span style="font-size:12.8000001907349px"><br></span></div><div><span style="font-size:12.8000001907349px">Ofer </span></div><div><font color="#000000" face="Calibri" size="4"><span style="line-height:11.5200004577637px"><br></span></font></div><div><font color="#000000" face="Calibri" size="4"><span style="line-height:11.5200004577637px"> </span></font></div><div><span style="font-size:large;line-height:11.5200004577637px;font-family:Calibri;color:black"><br></span></div><div><span style="font-size:large;line-height:11.5200004577637px;font-family:Calibri;color:black"><br></span></div></font></span></div><div><div><div class="gmail_extra"><br><div class="gmail_quote">On Mon, Feb 9, 2015 at 3:52 PM, Ofer Zilberstein <span dir="ltr"><<a href="mailto:zilberstein.ofer@gmail.com" target="_blank">zilberstein.ofer@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 dir="ltr">Hi Nicolas,<div><br></div><div>1. I used ST_CreateTopoGeo() to build the topology (not the <span style="font-size:12.8000001907349px">topology.toTopoGeom() as you mentioned )</span></div><div><span style="font-size:12.8000001907349px"><br></span></div><div><span style="font-size:12.8000001907349px">When I look at the relation table its empty ? should I use </span> <span style="font-size:12.8000001907349px">topology.toTopoGeom() instead, and it will populate it ?</span></div><div><span style="font-size:12.8000001907349px"><br></span></div><div><span style="font-size:12.8000001907349px">2. can you explain what the parentheses (special the 1, ...) part mean...</span></div><span><div><span style="font-size:12.8000001907349px"><br></span></div><div><p style="line-height:90%;margin-top:10pt;margin-bottom:0pt;margin-left:0in;text-indent:0in;direction:ltr;word-break:normal"><font size="4"><span style="font-family:Calibri;color:black">join </span><span style="font-family:Calibri;color:black">pe.parcelle</span><span style="font-family:Calibri;color:black"> p1
on <b>(1, (p1.topo).id)</b> = (r1.layer_id, r1.topogeo_id)</span></font></p></div><div><span style="font-size:12.8000001907349px"><br></span></div></span><div><span style="font-size:12.8000001907349px">Thanks in advanced ...</span></div><span><font color="#888888"><div><span style="font-size:12.8000001907349px"><br></span></div><div><span style="font-size:12.8000001907349px">Ofer</span></div></font></span></div><div><div><div class="gmail_extra"><br><div class="gmail_quote">On Mon, Feb 9, 2015 at 11:05 AM, Nicolas Ribot <span dir="ltr"><<a href="mailto:nicolas.ribot@gmail.com" target="_blank">nicolas.ribot@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 dir="ltr">Hi,<div><br></div><div>As usual with SQL, there must be several other ways to perform the same query.</div><div><br></div><div>As the edge view stores 2 faces id per row (left and right), I duplicated the joins to join both left_face and right face against the original polygon table (p1 and p2 aliases)</div><span><font color="#888888"><div><br></div><div>Nicolas</div></font></span></div><div><div><div class="gmail_extra"><br><div class="gmail_quote">On 8 February 2015 at 21:57, Ofer Zilberstein <span dir="ltr"><<a href="mailto:zilberstein.ofer@gmail.com" target="_blank">zilberstein.ofer@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 dir="ltr">Much Thanks,<div><br></div><div>First I will try to understand the join that you did and then try to implement it...</div><span><font color="#888888"><div><br></div><div>Ofer</div></font></span></div><div><div><div class="gmail_extra"><br><div class="gmail_quote">On Sun, Feb 8, 2015 at 12:44 PM, Nicolas Ribot <span dir="ltr"><<a href="mailto:nicolas.ribot@gmail.com" target="_blank">nicolas.ribot@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 dir="ltr">Hi,<div><br></div><div>When you build the topology, for instance with topology.toTopoGeom(), you get back a TopoGeometry object for each polygon added to the topology.</div><div>This TopoGeometry is used to keep a link between your polygon object and all the topological elements composing it. </div><div>The "relation" table inside the topo schema holding the topology contains the identifier of the topoGeometry object and the identifiers of topo elements composing the polygon.</div><div><br></div><div>To get the right and left original polygons from the topology, you make a join between edge, relation and pg table: </div><div>(topo_grass is the topo schema, parcelle is the polygon table with a topo column containing the TopoGeometry object corresponding to the polygon):</div><div><pre style="color:rgb(169,183,198);font-family:Menlo;background-color:rgb(43,43,43)"><font size="1"><span style="color:rgb(204,120,50);font-weight:bold;background-color:rgb(62,56,80)">select </span><span style="background-color:rgb(62,56,80)">e.edge_id</span><span style="color:rgb(204,120,50);background-color:rgb(62,56,80)">, </span><span style="background-color:rgb(62,56,80)">e.left_face</span><span style="color:rgb(204,120,50);background-color:rgb(62,56,80)">, </span><span style="background-color:rgb(62,56,80)">e.right_face</span><span style="color:rgb(204,120,50);background-color:rgb(62,56,80)">,</span><span style="color:rgb(204,120,50)"><br></span><span style="color:rgb(204,120,50)"> </span>p1.<span style="color:rgb(208,208,255);font-weight:bold">id </span><span style="color:rgb(204,120,50);font-weight:bold">as </span>left_id<span style="color:rgb(204,120,50)">, </span>p2.<span style="color:rgb(208,208,255);font-weight:bold">id </span><span style="color:rgb(204,120,50);font-weight:bold">as </span>right_id<br><span style="color:rgb(204,120,50);font-weight:bold">from </span>topo_grass.edge e<br> <span style="color:rgb(204,120,50);font-weight:bold">join </span>topo_grass.relation r1 <span style="color:rgb(204,120,50);font-weight:bold">on </span>r1.<span style="color:rgb(208,208,255);font-weight:bold">element_id </span>= e.<span style="color:rgb(208,208,255);font-weight:bold">left_face<br></span><span style="color:rgb(208,208,255);font-weight:bold"> </span><span style="color:rgb(204,120,50);font-weight:bold">join </span>pe.parcelle p1 <span style="color:rgb(204,120,50);font-weight:bold">on </span>(<span style="color:rgb(104,151,187)">1</span><span style="color:rgb(204,120,50)">, </span>(p1.<span style="color:rgb(208,208,255);font-weight:bold">topo</span>).<span style="color:rgb(208,208,255);font-weight:bold">id</span>) = (r1.<span style="color:rgb(208,208,255);font-weight:bold">layer_id</span><span style="color:rgb(204,120,50)">, </span>r1.<span style="color:rgb(208,208,255);font-weight:bold">topogeo_id</span>)<br> <span style="color:rgb(204,120,50);font-weight:bold">join </span>topo_grass.relation r2 <span style="color:rgb(204,120,50);font-weight:bold">on </span>r2.<span style="color:rgb(208,208,255);font-weight:bold">element_id </span>= e.<span style="color:rgb(208,208,255);font-weight:bold">right_face<br></span><span style="color:rgb(208,208,255);font-weight:bold;background-color:rgb(62,56,80)"> </span><span style="color:rgb(204,120,50);font-weight:bold;background-color:rgb(62,56,80)">join </span><span style="background-color:rgb(62,56,80)">pe.parcelle p2 </span><span style="color:rgb(204,120,50);font-weight:bold;background-color:rgb(62,56,80)">on </span><span style="background-color:rgb(62,56,80)">(</span><span style="color:rgb(104,151,187);background-color:rgb(62,56,80)">1</span><span style="color:rgb(204,120,50);background-color:rgb(62,56,80)">, </span><span style="background-color:rgb(62,56,80)">(p2.topo).id) = (r2.layer_id</span><span style="color:rgb(204,120,50);background-color:rgb(62,56,80)">, </span><span style="background-color:rgb(62,56,80)">r2.topogeo_id)</span><span style="color:rgb(204,120,50)">;</span></font></pre></div><div>Nicolas</div><div><br></div><div><br></div></div><div class="gmail_extra"><br><div class="gmail_quote"><div><div>On 8 February 2015 at 10:29, Ofer Zilberstein <span dir="ltr"><<a href="mailto:zilberstein.ofer@gmail.com" target="_blank">zilberstein.ofer@gmail.com</a>></span> wrote:<br></div></div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div><div><div dir="ltr"><br clear="all"><div>Hi,</div><div><br></div><div>When you build topology from set of connected polygons, the original polygon attributes are not part of the topology model. The model creates MBR face for each original polygon. </div><div><br></div><div>The edges hold the left & right faces.</div><div><br></div><div>How or what is the right way to add to the edges the right and left original polygons ?</div><span><font color="#888888"><div><br></div>-- <br><div><div dir="ltr"><p><span style="font-size:10pt;font-family:Arial,sans-serif"><b>Ofer Zilberstein</b></span></p><p><span style="font-size:9pt;line-height:14px;font-family:Arial,sans-serif;color:rgb(95,95,95)"> Mobile: <a href="tel:972-544886873" value="+972544886873" target="_blank">972-544886873</a><u></u></span></p><p><span style="font-size:9pt;line-height:14px;font-family:Arial,sans-serif;color:rgb(95,95,95)"> Skype: ofer.zilberstein</span></p><p><span style="font-size:9pt;line-height:14px;font-family:Arial,sans-serif;color:rgb(95,95,95)"> Mail: <a href="mailto:zilberstein.ofer@gmail.com" target="_blank">zilberstein.ofer@gmail.com</a></span></p></div></div>
</font></span></div>
<br></div></div>_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br></blockquote></div><br></div>
<br>_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br></blockquote></div><br><br clear="all"><div><br></div>-- <br><div><div dir="ltr"><p><span style="font-size:10pt;font-family:Arial,sans-serif"><b>Ofer Zilberstein</b></span></p><p><span style="font-size:9pt;line-height:14px;font-family:Arial,sans-serif;color:rgb(95,95,95)"> Mobile: <a href="tel:972-544886873" value="+972544886873" target="_blank">972-544886873</a><u></u></span></p><p><span style="font-size:9pt;line-height:14px;font-family:Arial,sans-serif;color:rgb(95,95,95)"> Skype: ofer.zilberstein</span></p><p><span style="font-size:9pt;line-height:14px;font-family:Arial,sans-serif;color:rgb(95,95,95)"> Mail: <a href="mailto:zilberstein.ofer@gmail.com" target="_blank">zilberstein.ofer@gmail.com</a></span></p></div></div>
</div>
</div></div><br>_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br></blockquote></div><br></div>
</div></div><br>_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br></blockquote></div><br><br clear="all"><div><br></div>-- <br><div><div dir="ltr"><p><span style="font-size:10pt;font-family:Arial,sans-serif"><b>Ofer Zilberstein</b></span></p><p><span style="font-size:9pt;line-height:14px;font-family:Arial,sans-serif;color:rgb(95,95,95)"> Mobile: <a href="tel:972-544886873" value="+972544886873" target="_blank">972-544886873</a><u></u></span></p><p><span style="font-size:9pt;line-height:14px;font-family:Arial,sans-serif;color:rgb(95,95,95)"> Skype: ofer.zilberstein</span></p><p><span style="font-size:9pt;line-height:14px;font-family:Arial,sans-serif;color:rgb(95,95,95)"> Mail: <a href="mailto:zilberstein.ofer@gmail.com" target="_blank">zilberstein.ofer@gmail.com</a></span></p></div></div>
</div>
</div></div></blockquote></div><br><br clear="all"><div><br></div>-- <br><div><div dir="ltr"><p><span style="font-size:10pt;font-family:Arial,sans-serif"><b>Ofer Zilberstein</b></span></p><p><span style="font-size:9pt;line-height:14px;font-family:Arial,sans-serif;color:rgb(95,95,95)"> Mobile: <a href="tel:972-544886873" value="+972544886873" target="_blank">972-544886873</a><u></u></span></p><p><span style="font-size:9pt;line-height:14px;font-family:Arial,sans-serif;color:rgb(95,95,95)"> Skype: ofer.zilberstein</span></p><p><span style="font-size:9pt;line-height:14px;font-family:Arial,sans-serif;color:rgb(95,95,95)"> Mail: <a href="mailto:zilberstein.ofer@gmail.com" target="_blank">zilberstein.ofer@gmail.com</a></span></p></div></div>
</div>
</div></div><br>_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br></blockquote></div><br></div>
</div></div><br>_______________________________________________<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/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br></blockquote></div><br><br clear="all"><div><br></div>-- <br><div class="gmail_signature"><div dir="ltr"><p><span style="font-size:10pt;font-family:Arial,sans-serif"><b>Ofer Zilberstein</b></span></p><p><span style="font-size:9pt;line-height:14px;font-family:Arial,sans-serif;color:rgb(95,95,95)"> Mobile: 972-544886873<u></u></span></p><p><span style="font-size:9pt;line-height:14px;font-family:Arial,sans-serif;color:rgb(95,95,95)"> Skype: ofer.zilberstein</span></p><p><span style="font-size:9pt;line-height:14px;font-family:Arial,sans-serif;color:rgb(95,95,95)"> Mail: <a href="mailto:zilberstein.ofer@gmail.com" target="_blank">zilberstein.ofer@gmail.com</a></span></p></div></div>
</div>