<div dir="ltr"><div class="gmail_extra">Hi, </div><div class="gmail_extra"><br></div><div class="gmail_extra">Thank you Stephen/Ian for your advice. I have spent the morning on this and have tried rewriting my function (below) using ST_Dump and pgr_createTopology. This seems to generate records but each entry seems to have the same source and target (I also need to calculate the distance to apply pgrouting). The networkreach functions might be useful if these could be applied at both ends, and a little beyond my ability to understand. Any ideas on what am I doing wrong below?</div>
<div class="gmail_extra"><br></div><div class="gmail_extra">Regards</div><div class="gmail_extra">Adam</div><div class="gmail_extra"><br></div><div class="gmail_extra"><br></div><div class="gmail_extra"><div class="gmail_extra">
<br></div><div class="gmail_extra">-- Create noded network from existing network but with regular spacing between nodes</div><div class="gmail_extra">CREATE OR REPLACE FUNCTION createSpacedNodeNetwork(edgeTableName text, geomFieldName text, spacing double precision) </div>
<div class="gmail_extra"><span class="" style="white-space:pre">      </span>RETURNS TABLE (</div><div class="gmail_extra"><span class="" style="white-space:pre">      </span>    id integer,</div><div class="gmail_extra">            source bigint,</div>
<div class="gmail_extra">            target bigint,</div><div class="gmail_extra">            distance double precision,</div><div class="gmail_extra">            the_geom geometry</div><div class="gmail_extra"><span class="" style="white-space:pre">        </span>) AS</div>
<div class="gmail_extra">$BODY$</div><div class="gmail_extra">DECLARE</div><div class="gmail_extra"><span class="" style="white-space:pre">       </span>-- nothing to declare here</div><div class="gmail_extra"><span class="" style="white-space:pre">   </span>geom1 geometry;</div>
<div class="gmail_extra">BEGIN</div><div class="gmail_extra"><br></div><div class="gmail_extra"><span class="" style="white-space:pre">     </span>-- Create a temporary table for the network whilst we process it</div><div class="gmail_extra">
<span class="" style="white-space:pre">       </span>-- DROP on commit so we do not get problems reusing it again in this session</div><div class="gmail_extra"><span class="" style="white-space:pre"> </span>DROP TABLE IF EXISTS temp_edge_table;</div>
<div class="gmail_extra"><span class="" style="white-space:pre">      </span>CREATE TABLE temp_edge_table (</div><div class="gmail_extra"><span class="" style="white-space:pre">       </span>    id serial,</div><div class="gmail_extra">
            source bigint,</div><div class="gmail_extra">            target bigint,</div><div class="gmail_extra">            distance double precision,</div><div class="gmail_extra">            the_geom geometry</div><div class="gmail_extra">
<span class="" style="white-space:pre">       </span>); -- ON COMMIT DROP;</div><div class="gmail_extra"><br></div><div class="gmail_extra"><span class="" style="white-space:pre"> </span>-- Segementise and insert into temp table, note use of ST_Dump to split multipart </div>
<div class="gmail_extra"><span class="" style="white-space:pre">      </span>EXECUTE 'INSERT INTO temp_edge_table(the_geom) SELECT (ST_Dump(ST_Segmentize(' || geomFieldName || ', ' || spacing || '))).geom FROM ' || edgeTableName;</div>
<div class="gmail_extra"><br></div><div class="gmail_extra"><span class="" style="white-space:pre">       </span>-- Now create topography</div><div class="gmail_extra"><br></div><div class="gmail_extra"><span class="" style="white-space:pre">      </span>PERFORM  pgr_createTopology('temp_edge_table', 0.00001, 'the_geom', 'id');</div>
<div class="gmail_extra"><br></div><div class="gmail_extra"><br></div><div class="gmail_extra"><span class="" style="white-space:pre">        </span>--Return temp table results to calling function</div><div class="gmail_extra"><span class="" style="white-space:pre">      </span>RETURN QUERY SELECT * FROM temp_edge_table;</div>
<div class="gmail_extra">END;</div><div class="gmail_extra">$BODY$</div><div class="gmail_extra">LANGUAGE plpgsql;</div><div class="gmail_extra"><br></div><div class="gmail_extra">DROP TABLE IF EXISTS roads_spacednodes;</div>
<div class="gmail_extra">SELECT * INTO roads_spacednodes FROM createSpacedNodeNetwork('roads','geom',0.050);</div><div class="gmail_extra"><br></div><div class="gmail_extra">SELECT * FROM roads_spacednodes order by source, target, id;</div>
<div><br></div></div></div>