Merging two isolines into one in PostGIS

Question

How can I sum two isolines A and B (closed linestrings with values) into one isoline C with summed values on common parts?

For each line string there is a value and I would like to sum on common part.

If it is easier I can try to sum isopolygons made from isolines.

Answer 1

I'm assuming that what you are looking for is an overlay of the isopolygons, with resultant polygons attributed with the sum of the parent polygons. If so, the usual technique is to union/node the linework, polygonize it, and then use a point-in-polygon query for each resultant to find it's parents.

This is a good blog post describing this technique. There are many GISE questions about this as well, such as this and this.

Answer 2

Rafal,

you are silent and I have to guess if I understood correctly the auto translation of your question...

So run the script as CTE:

WITH
    tbla AS (SELECT id, h, (ST_Dump(geom)).geom geom FROM relief),
    tblb AS (WITH btbl AS (SELECT (ST_Dump(geom)).geom geom FROM tbla),
    intervals AS (SELECT generate_series (0, 501) as steps)
    SELECT steps AS stp, ST_LineInterpolatePoint(geom, steps/(SELECT count(steps)::float-1 FROM intervals)) geom FROM btbl, intervals GROUP BY intervals.steps, geom),
    tblc AS (SELECT ((ST_Dump(ST_VoronoiPolygons(ST_Collect(geom)))).geom) geom FROM tblb),
    tbld AS (SELECT ST_ExteriorRing(ST_Union(a.geom)) geom FROM tblc a JOIN tbla b ON ST_Intersects(a.geom, b.geom) GROUP BY b.geom),
    tble AS (SELECT (a.geom) geom FROM tbld a, tbld b WHERE ST_Length(a.geom)<ST_Length(b.geom)),
    tblf AS (SELECT ST_MakeLine(pt1, pt2) geom FROM (SELECT ST_PointN(geom, generate_series(1, ST_NPoints(geom)-1)) pt1, ST_PointN(geom, generate_series(2, ST_NPoints(geom))) pt2 FROM tble) AS geom),
    tblg AS (SELECT ST_MakeLine(ST_Centroid(geom)) geom FROM tblf),
    tblh AS (SELECT ST_MakeLine(ST_AddPoint(geom, ST_StartPoint(geom))) AS geom FROM tblg),
    tbli AS (SELECT ST_BuildArea(ST_Union(geom)) geom FROM tbla),
    tblj AS (SELECT b.id, b.h, (a.geom) geom FROM tbli a, tbla b WHERE ST_Intersects(a.geom, b.geom))
    (SELECT SUM(b.h), a.geom FROM tblh a, tblj b WHERE ST_Intersects(a.geom, b.geom) GROUP BY a.geom)

See the result.

The script is called: ST_MergingTwoIsolinesOneAverage.

I hope that my answer will be useful to someone and if necessary,

adjust the number of points on the lines...🙂

CREATE OR REPLACE FUNCTION ST_MergingTwoIsolinesOneAverage(
geom GEOMETRY,
    n integer)
RETURNS GEOMETRY AS
$BODY$
WITH
    tbla AS (SELECT (ST_Dump(geom)).geom geom),
    tblb AS (SELECT generate_series (0, n) as steps),
    tblc AS (SELECT steps AS stp, ST_LineInterpolatePoint(geom, steps/(SELECT count(steps)::float-1 FROM tblb)) geom FROM tbla, tblb GROUP BY tblb.steps, geom),
    tbld AS (SELECT ((ST_Dump(ST_VoronoiPolygons(ST_Collect(geom)))).geom) geom FROM tblc),
    tble AS (SELECT ST_ExteriorRing(ST_Union(a.geom)) geom FROM tbld a JOIN tbla b ON ST_Intersects(a.geom, b.geom) GROUP BY b.geom),
    tblf AS (SELECT (a.geom) geom FROM tble a, tble b WHERE ST_Length(a.geom)<ST_Length(b.geom)),
    tblg AS (SELECT ST_MakeLine(pnt1, pnt2) geom FROM (SELECT ST_PointN(geom, generate_series(1, ST_NPoints(geom)-1)) pnt1, ST_PointN(geom, generate_series(2, ST_NPoints(geom))) pnt2 FROM tblf) AS geom),
    tblh AS (SELECT ST_MakeLine(ST_Centroid(geom)) geom FROM tblg)
    SELECT ST_MakeLine(ST_AddPoint(geom, ST_StartPoint(geom))) AS geom FROM tblh;
$BODY$
LANGUAGE SQL

Run

SELECT ST_MergingTwoIsolinesOneAverage(ST_Union(geom), 501) geom FROM <name_table>

Do not use for simple figures like: a square, a triangle, etc.