3

I need to compute the average Z value of a 3D geometry in PostGIS. Two functions ST_ZMin and ST_ZMax are available to return the min and the max, but the function ST_ZAverage returning the average value does not exist unfortunately.

Is there a way to compute this average value?

Maybe there is an existing solution based on ST_NPoints, ST_PointN and ST_Z.

Improve this question
5
  • What kind of 3D geometry? Is it curved or in a single plane? I can think of a few possibilities, but they would depend on the characteristics of the input geometry. – John Powell Dec 9 '16 at 13:34
  • The geometry type is MultiPolygonZM. Z values for vertices composing the polygon outlines can be different. – julien Dec 9 '16 at 13:53
  • 1
    In that case Paul's answer. I was hoping for something more challenging :-) – John Powell Dec 9 '16 at 13:56
  • Indeed - the problem is really simpler with non polylines... by chance! But feel free to provide a more elaborated solution for the general case. – julien Dec 9 '16 at 14:01
  • You would need to have the SFCGAL extension set up, which I failed miserably to build last time I compiled Postgres, but then you could use ST_3DIntersection and a grid if you had a polyhedral surface, and take the average that way. – John Powell Dec 9 '16 at 14:09
4

Dump the points, get the average?

create table g ( id integer, geom geometry );
insert into g values (1, 'LINESTRING(0 0 0, 1 1 1, 2 2 2)');
insert into g values (2, 'LINESTRING(1 1 1, 2 2 2, 3 3 3)');

with pts as (
  select id, 
    st_z((st_dumppoints(geom)).geom) as z 
  from g
) 
select avg(z), id 
from pts 
group by id;

Or in subquery form, if you don't like CTEs:

select avg(z), id 
from (
  select id, 
    st_z((st_dumppoints(geom)).geom) as z 
  from g
) as pts 
group by id;

Trouble with this is that it's a vertex average, it doesn't account for the parts of the line in between the vertices, so the vertex density at particular locations will skew the average in favour of those locations. A more sophisticated result would require some quite complicated PL/PgSQL code.

Improve this answer
4
  • What if it is a curved surface? You would need to subdivide into smallish blocks, which you could do easily enough with two generate_series calls in x and y direction and then average, without resorting to plpgsql, as in the method here – John Powell Dec 9 '16 at 13:55
  • Thanks Paul for the answer. I was hoping a solution without having to create an auxiliary table with the points. Having such table would however make possible to compute other stats on the point Z values, which is nice. – julien Dec 9 '16 at 14:00
  • 2
    It's not an aux table, it's a CTE (basically a subquery, you can rewrite the query in subquery form if you like). I only created a table at the top to show my example. Also a little hack improvement: if you use ST_Segmentize() you can increase vertex density which will somewhat fix the concerns I have about skewing. – Paul Ramsey Dec 9 '16 at 14:15
  • Thanks for the explanation - I am not used to the "WITH .. AS" structure, sorry! – julien Dec 9 '16 at 14:56

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.