1

I'm triying to calculate the average elevation of some multilinestring points.

Is there a fastest way than this pgsql function?

CREATE OR REPLACE FUNCTION topstation.sig_moyenne_z(ingeom geometry)
  RETURNS double precision AS
$BODY$
DECLARE 
    geom2 geometry;
    sum double precision;
    max double precision;
BEGIN       
    sum:=0;
    geom2=ST_LineMerge(inGeom);
    max:=ST_NPoints(geom2);
    IF max > 1 THEN

    FOR i IN 1..max
    LOOP
        sum := sum + ST_Z(ST_PointN(geom2,i));
    END LOOP;
    RETURN sum / max;
     ELSE
    return 9999;
     END IF;
END;
$BODY$
  LANGUAGE plpgsql IMMUTABLE
  COST 100;
Improve this question
2

Maybe

Select avg(z_val) from 
(select st_z((st_dumppoints(geom)).geom) z_val from the_table) a
Improve this answer
3
  • That and you may not need to do every point in your linestring. Samples are more than enough, it'll buy you a lot of time this way especially if said linestrings are defined by a lot of them. – Moreau Colin Jul 1 '16 at 6:57
  • sorry I did not detailled what I aimed to do. I'm controlling contours and I nead every point of the line. – aurel_nc Jul 5 '16 at 0:27
  • Great clue. I tried it and it gave me the average elevation of the whole set of contour. What about this: SELECT avg(z_val) FROM (select gid, st_z((ST_DumpPoints(geom)).geom) z_val from a_table)as a GROUP BY gid order by 1 – aurel_nc Jul 5 '16 at 0:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.