[postgis-users] ST_MakeLine() woes

[Rémi Cura]

Hey,
your original code was not correct, as far as I can tell without being able
to test it!
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).
Another problem is that the points are going to be ill ordered as soon as
the line is not straight (

ORDER BY ST_Distance(ST_StartPoint(line.geom), cells.geom))

order by distance from start of the line to cells, the correct way to do it
would rather be :
ORDER BY ST_LineLocatePoint(line.geom, cells.geom) ASC
, 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.)
Please note that in both case it may fail if the raster cells are bigger
than the line turns.
I don't feel this is a very robust way to solve your problem anyway.

I can't test it so I'm not sure, but you could try the following to enforce
the order :


 WITH line AS
    -- From an arbitrary line
    (SELECT 'SRID=32632;LINESTRING (348595 4889225,352577
4887465,354784 4883841)'::geometry AS geom),
  cells AS
    -- Get DEM elevation for each intersected cell
    (SELECT ST_Centroid((ST_Intersection(mnt.rast, line.geom)).geom) AS geom,
    (ST_Intersection(mnt.rast, line.geom)).val AS val
     FROM mnt, line
     WHERE ST_Intersects(mnt.rast, line.geom)),
    -- Instantiate 3D points, ordered on line
  points3d AS
    (SELECT ST_SetSRID(ST_MakePoint(ST_X(cells.geom),
ST_Y(cells.geom), val), 32632) AS geom
     FROM cells, line*, row_number() over() as temp_id*

*--ORDER BY ST_Distance(ST_StartPoint(line.geom), cells.geom))**
ORDER BY ST_LineLocatePoint(line.geom, cells.geom) ASC*
-- Build 3D line from 3D pointsSELECT ST_MakeLine(geom *ORDER BY
temp_id ASC*) FROM points3d;


Cheers,
Rémi



2014-05-12 3:01 GMT+02:00 georgew <gws293 at hotmail.com>:

> Thank you Remi, I assume that means that my original code was correct
> (although it produces unwanted results).
> I am slowly reaching the conclusion that the problem is with
> st_intersection(), which traverses the raster in an order which is not
> compatible with my line shape and any attempt to create order by using a
> sequence number will only cement the wrong order. If my assumption is
> correct, maybe there is no solution to this problem.
> But I am not a GIS person, so I'll defer to those who know what they are
> talking about.
>
>
>
>
> --
> View this message in context:
> http://postgis.17.x6.nabble.com/ST-MakeLine-woes-tp5006266p5006274.html
> Sent from the PostGIS - User mailing list archive at Nabble.com.
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20140512/c1e2c599/attachment.html>