Sorry it didn't work :-( . Can you check that temp_id is not always 1? It is more that this approach is flawed _depending on the raster resolution and the small turn in your line (fixe = simplify line). _anyway, you are constructing a new line from centroid, and not putting the height information on the old line (see end of the message)).
Can you try to spatially filter your line : with the same code, change just the beginning WITH line AS -- From an arbitrary line (SELECT ST_Simplify(geom,2*your_raster_cell_size) from foot_cl where (sheet='AT24' AND sid=463)), cells AS .... A safer way to do it would be for instance: find the pixel under the line transform those pixels into polygons along with height value (ST_PixelAsPolygons) cut the line with those polygons (create new points at each enter/exit of pixels) add the height information of each polygon into the line parts. renode the line by assembling in the correct order the line parts. A suggestion of code for it: ----------------------------- WITH rast AS ( --getting a raster, and a diag line for this raster SELECT * --, ST_Intersection(rast,line)--unnest(regexp_split_to_array(ST_Summary(rast),'\n')) FROM test_raster.test_temp_raster,ST_SetSRID(ST_GeomFromText('Linestring(651050. 6860677,651055 6860682)') ,931008) AS line WHERE rid = 273143 LIMIT 1 ) ,pix_under_line AS ( --get the pixels that are covered by the line, transform the pixels into square (pylgon), keep the value of the band 1 (heigth is supposed to be here) --this is suboptimal and could be replacer by using st_intersection SELECT pix.*, line FROM rast,ST_PixelAsPolygons(rast,1) AS pix WHERE ST_Intersects(pix.geom,line)=TRUE --AND pix.val!=0 --if raster from interpolation, no need to keep wrong parts ) ,cutting_line_with_pix AS (--splitting the line with the pixels in order to obtain multiple parts of the line, each covering one pixel --(see https://github.com/Remi-C/PPPP_utilities/tree/master/postgis for rc_split_multi) SELECT ST_Dump(rc_Split_multi(min(line), ST_Union(ST_Boundary(geom)),0.01)) AS splitted_line FROM pix_under_line ) ,splitted_line AS ( --filtering the obtained parts of line to remove ghost created by precision error and point-line SELECT (splitted_line).path, (splitted_line).geom, ST_AsText( (splitted_line).geom) FROM cutting_line_with_pix WHERE ST_Length((splitted_line).geom)>0.001 ) ,sl_and_pix AS ( --for each parts of line, get the pixel polygon that it covers, along with the value of the pixel. Add this value to the parts of line SELECT DISTINCT ON (path) sl.*, pul.*, ST_AddMeasure(sl.geom,pul.val,pul.val) AS l_heigth FROM splitted_line AS sl, pix_under_line AS pul WHERE ST_Intersects(sl.geom,pul.geom)=TRUE ORDER BY path ASC, ST_Length(sl.geom) DESC )--fusion the line parts to create a single line (don't use ST_Union, it drops the M value) SELECT ST_Astext(ST_MakeLine(l_heigth ORDER BY path ASC) ) FROM sl_and_pix ----------------------------- Cheers, Rémi-C 2014-05-13 1:08 GMT+02:00 georgew <gws...@hotmail.com>: > thanks Remi, attached is the result after running your code, no change > unfortunately. > I had to make some slight changes to the code to make it work. The image > has > the code. > However you also say that my approach is not as robust as it could be. Any > suggestions on how to make it more robust? > > > > newline.png <http://postgis.17.x6.nabble.com/file/n5006277/newline.png> > > > > -- > View this message in context: > http://postgis.17.x6.nabble.com/ST-MakeLine-woes-tp5006266p5006277.html > Sent from the PostGIS - User mailing list archive at Nabble.com. > _______________________________________________ > postgis-users mailing list > postgis-users@lists.osgeo.org > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users >
_______________________________________________ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users