<div dir="ltr">Perfect!<div>Thanks</div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Wed, Jun 30, 2021 at 11:32 PM Florian Nadler <<a href="mailto:florian.nadler@cybertec.at">florian.nadler@cybertec.at</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">
<div>
<p>With reference to the parameter issue, try to utilize "using":<br>
<br>
</p>
<pre style="background-color:rgb(255,255,255);color:rgb(8,8,8);font-family:"JetBrains Mono",monospace"> <span style="color:rgb(0,51,179)">EXECUTE </span><span style="color:rgb(6,125,23)">'create table mst_tree as (WITH t1 as (SELECT (ST_Dump($1)).geom AS geom ),
</span><span style="color:rgb(6,125,23)"> t2 as(select st_snaptogrid(geom,$2) geom from t1)
</span><span style="color:rgb(6,125,23)">select geom, st_startpoint(geom) as src, st_endpoint(geom) as dest, st_length(geom) as weight from t2)' </span><span style="color:rgb(0,51,179)">using </span><span style="color:rgb(0,0,0)">tree</span>,<span style="color:rgb(0,0,0)">tolerance</span>;</pre>
<p><br>
</p>
<pre style="background-color:rgb(255,255,255);color:rgb(8,8,8);font-family:"JetBrains Mono",monospace"><span style="color:rgb(0,51,179)">CREATE OR REPLACE FUNCTION </span><span style="color:rgb(0,0,0)">public</span>.<span style="color:rgb(0,98,122);font-style:italic">mst</span>(
<span style="color:rgb(0,0,0)">tree </span><span style="color:rgb(0,51,179)">geometry</span>,
<span style="color:rgb(0,0,0)">tolerance </span><span style="color:rgb(0,51,179)">float
</span>)
<span style="color:rgb(0,51,179)">RETURNS TABLE
</span><span style="color:rgb(0,51,179)"> </span>(
<span style="color:rgb(135,16,148)">mst_geom </span><span style="color:rgb(0,51,179)">geometry</span>,
<span style="color:rgb(135,16,148)">mst_weight </span><span style="color:rgb(0,51,179)">double precision
</span><span style="color:rgb(0,51,179)"> </span>)
<span style="color:rgb(0,51,179)">LANGUAGE </span><span style="color:rgb(6,125,23)">'plpgsql'
</span><span style="color:rgb(6,125,23)"> </span><span style="color:rgb(0,51,179)">COST </span><span style="color:rgb(23,80,235)">100
</span><span style="color:rgb(23,80,235)"> </span><span style="color:rgb(0,51,179)">VOLATILE PARALLEL UNSAFE
</span><span style="color:rgb(0,51,179)"> ROWS </span><span style="color:rgb(23,80,235)">1000
</span><span style="color:rgb(23,80,235)">
</span><span style="color:rgb(0,51,179)">AS
</span><span style="color:rgb(6,125,23)">$BODY$
</span><span style="color:rgb(0,51,179)">DECLARE
</span><span style="color:rgb(0,51,179)"> </span><span style="color:rgb(0,0,0)">edge </span><span style="color:rgb(0,51,179)">RECORD</span>;
<span style="color:rgb(0,0,0)">e </span><span style="color:rgb(0,51,179)">integer</span>;
<span style="color:rgb(0,0,0)">i </span><span style="color:rgb(0,51,179)">integer </span>:= <span style="color:rgb(23,80,235)">0 </span>;
<span style="color:rgb(0,51,179)">BEGIN
</span><span style="color:rgb(0,51,179)"> EXECUTE </span><span style="color:rgb(6,125,23)">'drop table if exists mst_edges'</span>;
<span style="color:rgb(0,51,179)">EXECUTE </span><span style="color:rgb(6,125,23)">'drop table if exists mst_visited'</span>;
<span style="color:rgb(0,51,179)">EXECUTE </span><span style="color:rgb(6,125,23)">'drop table if exists mst_tree'</span>;
<span style="color:rgb(0,51,179)">EXECUTE </span><span style="color:rgb(6,125,23)">'create table mst_tree as (WITH t1 as (SELECT (ST_Dump($1)).geom AS geom ),
</span><span style="color:rgb(6,125,23)"> t2 as(select st_snaptogrid(geom,$2) geom from t1)
</span><span style="color:rgb(6,125,23)"> select geom, st_startpoint(geom) as src, st_endpoint(geom) as dest, st_length(geom) as weight from t2)' </span><span style="color:rgb(0,51,179)">using </span><span style="color:rgb(0,0,0)">tree</span>,<span style="color:rgb(0,0,0)">tolerance</span>;
<span style="color:rgb(0,51,179)">EXECUTE </span><span style="color:rgb(6,125,23)">'create table mst_visited as (select src as node, 1 as branch from mst_tree limit 0)'</span>;
<span style="color:rgb(0,51,179)">EXECUTE </span><span style="color:rgb(6,125,23)">'create table mst_edges as (select geom, weight, src, dest, 1 as mst from mst_tree limit 0)'</span>;
<span style="color:rgb(0,51,179)">with </span><span style="color:rgb(0,0,0)">nodes </span><span style="color:rgb(0,51,179)">as </span>(
<span style="color:rgb(0,51,179)">select </span>src <span style="color:rgb(0,51,179)">as </span><span style="color:rgb(0,0,0)">node
</span><span style="color:rgb(0,0,0)"> </span><span style="color:rgb(0,51,179)">from </span>mst_edges
<span style="color:rgb(0,51,179)">union all
</span><span style="color:rgb(0,51,179)"> select </span>dest <span style="color:rgb(0,51,179)">as </span><span style="color:rgb(0,0,0)">node
</span><span style="color:rgb(0,0,0)"> </span><span style="color:rgb(0,51,179)">from </span>mst_edges
),
<span style="color:rgb(0,0,0)">d_nodes </span><span style="color:rgb(0,51,179)">as </span>(
<span style="color:rgb(0,51,179)">select distinct </span><span style="color:rgb(0,0,0)">node
</span><span style="color:rgb(0,0,0)"> </span><span style="color:rgb(0,51,179)">from </span><span style="color:rgb(0,0,0)">nodes</span>)
<span style="color:rgb(0,51,179)">SELECT </span><span style="font-style:italic">COUNT</span>(<span style="color:rgb(0,0,0)">node</span>)
<span style="color:rgb(0,51,179)">from </span><span style="color:rgb(0,0,0)">d_nodes
</span><span style="color:rgb(0,0,0)"> </span><span style="color:rgb(0,51,179)">into </span><span style="color:rgb(0,0,0)">e</span>;
<span style="color:rgb(0,51,179)">for </span><span style="color:rgb(0,0,0)">edge </span><span style="color:rgb(0,51,179)">in execute </span><span style="color:rgb(6,125,23)">'SELECT geom, weight, src, dest FROM mst_tree ORDER by weight asc'
</span><span style="color:rgb(6,125,23)"> </span><span style="color:rgb(0,51,179)">LOOP
</span><span style="color:rgb(0,51,179)"> EXIT WHEN </span><span style="color:rgb(0,0,0)">e </span>= <span style="color:rgb(23,80,235)">1</span>;
<span style="color:rgb(0,51,179)">WITH </span><span style="color:rgb(0,0,0)">next_nodes </span><span style="color:rgb(0,51,179)">as </span>(
<span style="color:rgb(0,51,179)">select </span><span style="color:rgb(0,0,0)">edge</span>.src <span style="color:rgb(0,51,179)">as </span><span style="color:rgb(0,0,0)">node
</span><span style="color:rgb(0,0,0)"> </span><span style="color:rgb(0,51,179)">union all
</span><span style="color:rgb(0,51,179)"> select </span><span style="color:rgb(0,0,0)">edge</span>.dest <span style="color:rgb(0,51,179)">as </span><span style="color:rgb(0,0,0)">node
</span><span style="color:rgb(0,0,0)"> </span>), <span style="color:rgb(140,140,140);font-style:italic">-- put nodes in a single column
</span><span style="color:rgb(140,140,140);font-style:italic">
</span><span style="color:rgb(140,140,140);font-style:italic"> </span><span style="color:rgb(0,0,0)">new_nodes </span><span style="color:rgb(0,51,179)">as </span>(
<span style="color:rgb(0,51,179)">select </span><span style="color:rgb(0,0,0)">node </span><span style="color:rgb(0,51,179)">from </span><span style="color:rgb(0,0,0)">next_nodes </span><span style="color:rgb(0,51,179)">where </span><span style="color:rgb(0,0,0)">node </span><span style="color:rgb(0,51,179)">not in </span>(<span style="color:rgb(0,51,179)">select </span><span style="color:rgb(0,0,0)">node </span><span style="color:rgb(0,51,179)">from </span>mst_visited)
), <span style="color:rgb(140,140,140);font-style:italic">-- list of nodes not yet visited
</span><span style="color:rgb(140,140,140);font-style:italic">
</span><span style="color:rgb(140,140,140);font-style:italic"> </span><span style="color:rgb(0,0,0)">existing_nodes </span><span style="color:rgb(0,51,179)">as </span>(
<span style="color:rgb(0,51,179)">select </span><span style="color:rgb(0,0,0)">next_nodes</span>.<span style="color:rgb(0,0,0)">node</span>, branch
<span style="color:rgb(0,51,179)">from </span><span style="color:rgb(0,0,0)">next_nodes</span>,
mst_visited
<span style="color:rgb(0,51,179)">where </span><span style="color:rgb(0,0,0)">next_nodes</span>.<span style="color:rgb(0,0,0)">node </span>= mst_visited.node
), <span style="color:rgb(140,140,140);font-style:italic">-- list of nodes already visited
</span><span style="color:rgb(140,140,140);font-style:italic">
</span><span style="color:rgb(140,140,140);font-style:italic"> </span><span style="color:rgb(0,0,0)">cycle_test </span><span style="color:rgb(0,51,179)">as </span>(
<span style="color:rgb(0,51,179)">select case
</span><span style="color:rgb(0,51,179)"> when </span><span style="font-style:italic">count</span>(<span style="color:rgb(0,0,0)">node</span>) = <span style="color:rgb(23,80,235)">2 </span><span style="color:rgb(0,51,179)">and </span>(<span style="font-style:italic">max</span>(<span style="color:rgb(0,0,0)">branch</span>) = <span style="color:rgb(0,51,179);font-style:italic">min</span>(<span style="color:rgb(0,0,0)">branch</span>))
<span style="color:rgb(0,51,179)">then </span><span style="color:rgb(23,80,235)">2 </span><span style="color:rgb(140,140,140);font-style:italic">-- forms a loop, do not add to MST
</span><span style="color:rgb(140,140,140);font-style:italic"> </span><span style="color:rgb(0,51,179)">else </span><span style="color:rgb(23,80,235)">1 </span><span style="color:rgb(0,51,179)">end as </span><span style="color:rgb(0,0,0)">mst</span>, <span style="color:rgb(140,140,140);font-style:italic">-- not a loop, include edge in MST
</span><span style="color:rgb(140,140,140);font-style:italic"> </span><span style="font-style:italic">max</span>(<span style="color:rgb(0,0,0)">branch</span>) <span style="color:rgb(0,51,179)">as </span><span style="color:rgb(0,0,0)">max_branch</span>,
<span style="color:rgb(0,51,179);font-style:italic">min</span>(<span style="color:rgb(0,0,0)">branch</span>) <span style="color:rgb(0,51,179)">as </span><span style="color:rgb(0,0,0)">min_branch
</span><span style="color:rgb(0,0,0)"> </span><span style="color:rgb(0,51,179)">from </span><span style="color:rgb(0,0,0)">existing_nodes
</span><span style="color:rgb(0,0,0)"> </span>),
<span style="color:rgb(0,0,0)">v </span>(<span style="color:rgb(135,16,148)">nodes</span>) <span style="color:rgb(0,51,179)">as </span>(
<span style="color:rgb(0,51,179)">insert into </span>mst_visited (node, branch)
(<span style="color:rgb(0,51,179)">select </span><span style="color:rgb(0,0,0)">node</span>,
<span style="color:rgb(0,51,179)">case
</span><span style="color:rgb(0,51,179)"> when </span>(<span style="color:rgb(0,51,179)">select </span><span style="font-style:italic">count</span>(<span style="color:rgb(0,0,0)">node</span>) <span style="color:rgb(0,51,179)">from </span><span style="color:rgb(0,0,0)">new_nodes</span>) = <span style="color:rgb(23,80,235)">2
</span><span style="color:rgb(23,80,235)"> </span><span style="color:rgb(0,51,179)">then </span><span style="color:rgb(0,0,0)">e </span><span style="color:rgb(140,140,140);font-style:italic">--next_branch.n_b -- two new nodes, new branch
</span><span style="color:rgb(140,140,140);font-style:italic"> </span><span style="color:rgb(0,51,179)">else </span>(<span style="color:rgb(0,51,179)">select </span><span style="color:rgb(0,0,0)">branch </span><span style="color:rgb(0,51,179)">from </span><span style="color:rgb(0,0,0)">existing_nodes</span>) <span style="color:rgb(140,140,140);font-style:italic">-- add node to existing branch
</span><span style="color:rgb(140,140,140);font-style:italic"> </span><span style="color:rgb(0,51,179)">end
</span><span style="color:rgb(0,51,179)"> as </span><span style="color:rgb(0,0,0)">branch
</span><span style="color:rgb(0,0,0)"> </span><span style="color:rgb(0,51,179)">from </span><span style="color:rgb(0,0,0)">new_nodes</span><span style="color:rgb(140,140,140);font-style:italic">--,next_branch
</span><span style="color:rgb(140,140,140);font-style:italic"> </span>)
<span style="color:rgb(0,51,179)">returning </span><span style="font-style:italic">*
</span><span style="font-style:italic"> </span>),
<span style="color:rgb(0,0,0)">v_c</span>(<span style="color:rgb(135,16,148)">node</span>) <span style="color:rgb(0,51,179)">as </span>(
<span style="color:rgb(0,51,179)">update </span>mst_visited <span style="color:rgb(0,51,179)">set </span>branch = <span style="color:rgb(0,0,0)">d</span>.<span style="color:rgb(0,0,0)">min_branch
</span><span style="color:rgb(0,0,0)"> </span><span style="color:rgb(0,51,179)">from </span>(<span style="color:rgb(0,51,179)">select </span><span style="font-style:italic">* </span><span style="color:rgb(0,51,179)">from </span><span style="color:rgb(0,0,0)">cycle_test</span>) <span style="color:rgb(0,0,0)">d
</span><span style="color:rgb(0,0,0)"> </span><span style="color:rgb(0,51,179)">where </span>mst_visited.branch = <span style="color:rgb(0,0,0)">d</span>.<span style="color:rgb(0,0,0)">max_branch
</span><span style="color:rgb(0,0,0)"> </span><span style="color:rgb(0,51,179)">returning </span><span style="font-style:italic">*
</span><span style="font-style:italic"> </span>), <span style="color:rgb(140,140,140);font-style:italic">-- if two branches, combine branches (no effect if on same branch) This is the critical step to loop detection
</span><span style="color:rgb(140,140,140);font-style:italic">
</span><span style="color:rgb(140,140,140);font-style:italic">-- update edge table, set mst to 1 or 2
</span><span style="color:rgb(140,140,140);font-style:italic"> </span><span style="color:rgb(0,0,0)">u </span>(<span style="color:rgb(135,16,148)">node</span>) <span style="color:rgb(0,51,179)">as </span>(
<span style="color:rgb(0,51,179)">insert into </span>mst_edges (geom, weight, src, dest, mst)
(<span style="color:rgb(0,51,179)">select </span><span style="color:rgb(0,0,0)">edge</span>.geom, <span style="color:rgb(0,0,0)">edge</span>.weight, <span style="color:rgb(0,0,0)">edge</span>.src, <span style="color:rgb(0,0,0)">edge</span>.dest, <span style="color:rgb(0,0,0)">mst
</span><span style="color:rgb(0,0,0)"> </span><span style="color:rgb(0,51,179)">from </span><span style="color:rgb(0,0,0)">cycle_test
</span><span style="color:rgb(0,0,0)"> </span><span style="color:rgb(0,51,179)">where </span><span style="color:rgb(0,0,0)">mst </span>= <span style="color:rgb(23,80,235)">1</span>)
<span style="color:rgb(0,51,179)">returning </span><span style="font-style:italic">*
</span><span style="font-style:italic"> </span>)
<span style="color:rgb(0,51,179)">select </span><span style="color:rgb(0,0,0)">mst
</span><span style="color:rgb(0,0,0)"> </span><span style="color:rgb(0,51,179)">from </span><span style="color:rgb(0,0,0)">cycle_test
</span><span style="color:rgb(0,0,0)"> </span><span style="color:rgb(0,51,179)">into </span><span style="color:rgb(0,0,0)">i</span>;
<span style="color:rgb(0,51,179)">IF </span>(<span style="color:rgb(0,0,0)">i </span>= <span style="color:rgb(23,80,235)">1</span>) <span style="color:rgb(0,51,179)">then
</span><span style="color:rgb(0,51,179)"> </span><span style="color:rgb(0,0,0)">e </span>= <span style="color:rgb(0,0,0)">e </span>- <span style="color:rgb(23,80,235)">1</span>;
<span style="color:rgb(0,51,179)">END IF</span>;
<span style="color:rgb(0,51,179)">END LOOP</span>;
<span style="color:rgb(0,51,179)">EXECUTE </span><span style="color:rgb(6,125,23)">'drop table mst_visited'</span>;
<span style="color:rgb(0,51,179)">RETURN QUERY SELECT </span>geom, weight <span style="color:rgb(0,51,179)">from </span>mst_edges;
<span style="color:rgb(0,51,179)">END</span>;
<span style="color:rgb(6,125,23)">$BODY$</span>;
<span style="color:rgb(0,51,179)">select </span><span style="color:rgb(0,98,122);font-style:italic">mst</span>(<span style="color:rgb(0,98,122);font-style:italic">st_geometryfromtext</span>(
<span style="color:rgb(6,125,23)">'MULTILINESTRING((12.927 149.924,50.261 222.857),(12.927 149.924,122.094 70.04),(12.927 149.924,49.247 169.665),(49.26 169.665,178.334 165.312),(50.01 222.79,49.26 169.665),(64.208 197.562,173.254 269.374),(64.355 197.598,50.01 222.455),(64.355 197.598,178.499 165.263),(64.602 197.598,49.26 169.665),(122.093 70.04,49.26 169.665),(173.254 269.374,50.01 222.79),(173.254 269.374,232.314 286.445),(173.427 269.374,242.527 269.556),(178.427 165.263,242.527 269.556),(178.499 165.263,122.093 70.04),(178.499 165.263,173.254 269.374),(232.314 286.445,242.527 269.556),(232.314 286.437,285.906 259.373),(242.527 269.556,285.906 259.236),(285.906 259.373,122.026 70.04),(285.906 259.373,178.427 165.263))'</span>),
<span style="color:rgb(23,80,235)">10</span>);</pre>
<div>Am 01.07.2021 um 00:39 schrieb Bruce
Rindahl:<br>
</div>
<blockquote type="cite">
<div dir="ltr">Warning - Long post
<div><br>
</div>
<div>I have created a function that finds the minimum spanning
tree from a MultiLineString per the description at:</div>
<div><a href="https://docs.google.com/presentation/d/1iqTLwt95rEBISBcPoaA31_clqQBAJlaMegHNfNYHuuI/edit#slide=id.g6c6fcfd43d_0_85" target="_blank">https://docs.google.com/presentation/d/1iqTLwt95rEBISBcPoaA31_clqQBAJlaMegHNfNYHuuI/edit#slide=id.g6c6fcfd43d_0_85</a><br>
</div>
<div>I have it working in two parts but I can't merge them
together into one function.</div>
<div><br>
</div>
<div>First the data. I created a table wiki with just a
geometry column. Then I grabbed the SVG coordinates from the
Wiki link from above, tweaked it and got the following
MultiLineString:</div>
<div><br>
</div>
<div>MULTILINESTRING((12.927 149.924,50.261 222.857),(12.927
149.924,122.094 70.04),(12.927 149.924,49.247 169.665),(49.26
169.665,178.334 165.312),(50.01 222.79,49.26 169.665),(64.208
197.562,173.254 269.374),(64.355 197.598,50.01
222.455),(64.355 197.598,178.499 165.263),(64.602
197.598,49.26 169.665),(122.093 70.04,49.26 169.665),(173.254
269.374,50.01 222.79),(173.254 269.374,232.314
286.445),(173.427 269.374,242.527 269.556),(178.427
165.263,242.527 269.556),(178.499 165.263,122.093
70.04),(178.499 165.263,173.254 269.374),(232.314
286.445,242.527 269.556),(232.314 286.437,285.906
259.373),(242.527 269.556,285.906 259.236),(285.906
259.373,122.026 70.04),(285.906 259.373,178.427 165.263))<br>
</div>
<div><br>
</div>
<div>I then inserted it into the table wiki. Note the vertices
do not perfectly line up..</div>
<div><br>
</div>
<div>Step one - I need to create a table that defines the
geometry,starting and ending points of each line segment and
the line length. This is easy:</div>
<div><br>
</div>
<div>create table mst_tree as (<br>
WITH t1 as (SELECT (ST_Dump(geom)).geom AS geom from wiki),<br>
t2 as(select st_snaptogrid(geom,10) geom from t1)<br>
select geom, st_startpoint(geom) as src, st_endpoint(geom) as
dest, st_length(geom) as weight from t2<br>
)<br>
</div>
<div><br>
</div>
<div>Note the value of 10 in the st_snaptogrid - this assures
all the close nodes snap to each other and will need to be a
parameter to the function.</div>
<div><br>
</div>
<div>Next is the function that finds the Minimum Spanning Tree.
I used the Kruskal method - see:</div>
<div><a href="https://www.geeksforgeeks.org/kruskals-minimum-spanning-tree-algorithm-greedy-algo-2/" target="_blank">https://www.geeksforgeeks.org/kruskals-minimum-spanning-tree-algorithm-greedy-algo-2/</a><br>
</div>
<div><br>
</div>
<div>The full function is:</div>
<div>---------------------------------------------------------------</div>
<div><br>
</div>
<div>-- FUNCTION: public.mst()<br>
<br>
-- DROP FUNCTION public.mst();<br>
<br>
CREATE OR REPLACE FUNCTION public.mst(<br>
-- tree geometry,<br>
-- tolerance float<br>
)<br>
RETURNS TABLE(mst_geom geometry, mst_weight double
precision) <br>
LANGUAGE 'plpgsql'<br>
COST 100<br>
VOLATILE PARALLEL UNSAFE<br>
ROWS 1000<br>
<br>
AS $BODY$<br>
DECLARE<br>
edge RECORD;<br>
e integer;<br>
i integer := 0 ;<br>
BEGIN<br>
EXECUTE 'drop table if exists mst_edges';<br>
EXECUTE 'drop table if exists mst_visited';<br>
<br>
-- I am stuck here<br>
--EXECUTE 'drop table if exists mst_tree';<br>
--EXECUTE 'create table mst_tree as (WITH t1 as (SELECT
(ST_Dump(tree)) AS geom ),<br>
--t2 as(select st_snaptogrid(geom,tolerance) geom from t1)<br>
--select geom, st_startpoint(geom) as src, st_endpoint(geom)
as dest, st_length(geom) as weight from t2)';<br>
<br>
EXECUTE 'create table mst_visited as (select src as node,
1 as branch from mst_tree limit 0)';<br>
EXECUTE 'create table mst_edges as (select geom, weight,
src, dest, 1 as mst from mst_tree limit 0)';<br>
<br>
with nodes as (<br>
select src as node from mst_edges<br>
union all <br>
select dest as node from mst_edges<br>
),<br>
d_nodes as (<br>
select distinct node from nodes)<br>
SELECT COUNT(node) from d_nodes into e;<br>
<br>
for edge in execute 'SELECT geom, weight, src, dest FROM
mst_tree ORDER by weight asc' <br>
LOOP<br>
EXIT WHEN e = 1;<br>
<br>
WITH next_nodes as (<br>
select edge.src as node<br>
union all <br>
select edge.dest as node<br>
), -- put nodes in a single column<br>
<br>
new_nodes as (<br>
select node from next_nodes where node not in (select node
from mst_visited)<br>
), -- list of nodes not yet visited<br>
<br>
existing_nodes as (<br>
select next_nodes.node, branch from next_nodes, mst_visited<br>
where next_nodes.node = mst_visited.node<br>
), -- list of nodes already visited<br>
<br>
cycle_test as (<br>
select <br>
case<br>
when count(node) = 2 and (max(branch) = min(branch)) then 2 --
forms a loop, do not add to MST<br>
else 1 end as mst, -- not a loop, include edge in MST<br>
max(branch) as max_branch, min(branch) as min_branch<br>
from existing_nodes<br>
),<br>
<br>
v (nodes) as (<br>
insert into mst_visited (node,branch) <br>
(select node,<br>
case<br>
when (select count(node) from new_nodes) = 2 then e
--next_branch.n_b -- two new nodes, new branch<br>
else (select branch from existing_nodes) -- add node to
existing branch<br>
end<br>
as branch<br>
from new_nodes--,next_branch<br>
)<br>
returning *<br>
),<br>
<br>
v_c(node) as (<br>
update mst_visited set branch = d.min_branch<br>
from (select * from cycle_test) d<br>
where mst_visited.branch = d.max_branch<br>
returning *<br>
), -- if two branches, combine branches (no effect if on same
branch) This is the critical step to loop detection<br>
<br>
-- update edge table, set mst to 1 or 2 <br>
u (node) as (<br>
insert into mst_edges (geom,weight,src,dest,mst) <br>
(select edge.geom,edge.weight, edge.src, edge.dest, mst from
cycle_test<br>
where mst = 1)<br>
returning *<br>
)<br>
<br>
select mst from cycle_test into i;<br>
<br>
IF (i = 1) then<br>
e = e - 1;<br>
END IF;<br>
END LOOP;<br>
<br>
EXECUTE 'drop table mst_visited';<br>
RETURN QUERY SELECT geom,weight from mst_edges;<br>
<br>
END;<br>
$BODY$;<br>
<br>
ALTER FUNCTION public.mst()<br>
OWNER TO postgres;<br>
</div>
<div>-----------------------------------------------</div>
<div><br>
</div>
<div>If you create the table mst_tree from the first part and
then run:</div>
<div><br>
</div>
<div>SELECT * from mst()</div>
<div><br>
</div>
<div> you will get a table of each segment in the MST and the
associated weight (length) . If you want to get the result in
a MultiLine segment:</div>
<div><br>
</div>
<div>SELECT ST_Collect(ARRAY(SELECT mst_geom FROM mst6()));<br>
</div>
<div><br>
</div>
<div>Now I am stuck. How do I pass the geometry and tolerance
into the function If I uncomment the lines I get the error:</div>
<div><br>
</div>
<div><span style="font-family:"Source Code Pro",SFMono-Regular,Menlo,Monaco,Consolas,"Liberation Mono","Courier New",monospace;font-size:12.95px;white-space:pre-wrap">ERROR: column "geom" does not exist
LINE 1: ...te table mst_tree as (WITH t1 as (SELECT (ST_Dump(geom)).geo...
</span><span style="font-family:"Source Code Pro",SFMono-Regular,Menlo,Monaco,Consolas,"Liberation Mono","Courier New",monospace;font-size:12.95px;white-space:pre-wrap">QUERY: create table mst_tree as (WITH t1 as (SELECT (ST_Dump(geom)).geom AS geom),
t2 as(select st_snaptogrid(geom,$2) geom from t1)
select geom, st_startpoint(geom) as src, st_endpoint(geom) as dest, st_length(geom) as weight from t2)
</span><br>
</div>
<div>Any help will be appreciated</div>
<div><br>
</div>
<div><br>
</div>
<div><br>
</div>
<div><br>
</div>
<div><br>
</div>
<div><br>
</div>
</div>
<br>
<fieldset></fieldset>
<pre>_______________________________________________
postgis-users mailing list
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a>
<a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-users</a>
</pre>
</blockquote>
<pre cols="72">--
CYBERTEC PostgreSQL International GmbH
Gröhrmühlgasse 26, A-2700 Wiener Neustadt
Web: <a href="https://www.cybertec-postgresql.com" target="_blank">https://www.cybertec-postgresql.com</a></pre>
</div>
</blockquote></div>