<div dir="ltr"><div><div><div><div>Hey,<br></div>your original code was not correct, as far as I can tell without being able to test it!<br></div><div>As far as I can tell the blog query is not correct either (the last statement may mix the order as it is not enforced).<br>
</div><div>Another problem is that the points are going to be ill ordered as soon as the line is not straight (<pre><span class="">ORDER</span> <span class="">BY</span> <span class="">ST_Distance</span><span class="">(</span><span class="">ST_StartPoint</span><span class="">(</span><span class="">line</span><span class="">.</span><span class="">geom</span><span class="">),</span> <span class="">cells</span><span class="">.</span><span class="">geom</span><span class="">))</span></pre>
order by distance from start of the line to cells, the correct way to do it would rather be :<br></div><div>ORDER BY ST_LineLocatePoint(line.geom, cells.geom) ASC<br>, that is for each cell centroid, project it on line, then order the cells centroid by the order of those projected points on the line.)<br>
</div><div>Please note that in both case it may fail if the raster cells are bigger than the line turns.<br></div><div>I don't feel this is a very robust way to solve your problem anyway.<br></div></div><br>I can't test it so I'm not sure, but you could try the following to enforce the order :<br>
<br><br><pre> <span class="">WITH</span> <span class="">line</span> <span class="">AS</span>
<span class="">-- From an arbitrary line</span>
<span class="">(</span><span class="">SELECT</span> <span class="">'SRID=32632;LINESTRING (348595 4889225,352577 4887465,354784 4883841)'</span><span class="">::</span><span class="">geometry</span> <span class="">AS</span> <span class="">geom</span><span class="">),</span>
<span class="">cells</span> <span class="">AS</span>
<span class="">-- Get DEM elevation for each intersected cell</span>
<span class="">(</span><span class="">SELECT</span> <span class="">ST_Centroid</span><span class="">((</span><span class="">ST_Intersection</span><span class="">(</span><span class="">mnt</span><span class="">.</span><span class="">rast</span><span class="">,</span> <span class="">line</span><span class="">.</span><span class="">geom</span><span class="">)).</span><span class="">geom</span><span class="">)</span> <span class="">AS</span> <span class="">geom</span><span class="">,</span>
<span class="">(</span><span class="">ST_Intersection</span><span class="">(</span><span class="">mnt</span><span class="">.</span><span class="">rast</span><span class="">,</span> <span class="">line</span><span class="">.</span><span class="">geom</span><span class="">)).</span><span class="">val</span> <span class="">AS</span> <span class="">val</span>
<span class="">FROM</span> <span class="">mnt</span><span class="">,</span> <span class="">line</span>
<span class="">WHERE</span> <span class="">ST_Intersects</span><span class="">(</span><span class="">mnt</span><span class="">.</span><span class="">rast</span><span class="">,</span> <span class="">line</span><span class="">.</span><span class="">geom</span><span class="">)),</span>
<span class="">-- Instantiate 3D points, ordered on line</span>
<span class="">points3d</span> <span class="">AS</span>
<span class="">(</span><span class="">SELECT</span> <span class="">ST_SetSRID</span><span class="">(</span><span class="">ST_MakePoint</span><span class="">(</span><span class="">ST_X</span><span class="">(</span><span class="">cells</span><span class="">.</span><span class="">geom</span><span class="">),</span> <span class="">ST_Y</span><span class="">(</span><span class="">cells</span><span class="">.</span><span class="">geom</span><span class="">),</span> <span class="">val</span><span class="">),</span> <span class="">32632</span><span class="">)</span> <span class="">AS</span> <span class="">geom</span>
<span class="">FROM</span> <span class="">cells</span><span class="">,</span> <span class="">line<b><span style="color:rgb(255,0,0)">, row_number() over() as temp_id</span></b></span>
<span style="color:rgb(255,0,0)"><b><span class="">--ORDER</span> <span class="">BY</span> <span class="">ST_Distance</span><span class="">(</span><span class="">ST_StartPoint</span><span class="">(</span><span class="">line</span><span class="">.</span><span class="">geom</span><span class="">),</span> <span class="">cells</span><span class="">.</span><span class="">geom</span><span class="">))</span><span class=""></span><br>
</b></span><span class=""><span style="color:rgb(255,0,0)"><b> ORDER BY ST_LineLocatePoint(line.geom, cells.geom) ASC</b></span><br>-- Build 3D line from 3D points</span>
<span class="">SELECT</span> <span class="">ST_MakeLine</span><span class="">(</span><span class="">geom</span><span class=""> <b><span style="color:rgb(255,0,0)">ORDER BY temp_id ASC</span></b>)</span> <span class="">FROM</span> <span class="">points3d</span><span class="">;<br>
<br></span></pre> <br></div>Cheers,<br>Rémi<br><div><div><br></div></div></div><div class="gmail_extra"><br><br><div class="gmail_quote">2014-05-12 3:01 GMT+02:00 georgew <span dir="ltr"><<a href="mailto:gws293@hotmail.com" target="_blank">gws293@hotmail.com</a>></span>:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">Thank you Remi, I assume that means that my original code was correct<br>
(although it produces unwanted results).<br>
I am slowly reaching the conclusion that the problem is with<br>
st_intersection(), which traverses the raster in an order which is not<br>
compatible with my line shape and any attempt to create order by using a<br>
sequence number will only cement the wrong order. If my assumption is<br>
correct, maybe there is no solution to this problem.<br>
But I am not a GIS person, so I'll defer to those who know what they are<br>
talking about.<br>
<br>
<br>
<br>
<br>
--<br>
View this message in context: <a href="http://postgis.17.x6.nabble.com/ST-MakeLine-woes-tp5006266p5006274.html" target="_blank">http://postgis.17.x6.nabble.com/ST-MakeLine-woes-tp5006266p5006274.html</a><br>
<div class="HOEnZb"><div class="h5">Sent from the PostGIS - User mailing list archive at Nabble.com.<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>
</div></div></blockquote></div><br></div>