CREATE OR REPLACE FUNCTION cncggetisosurface(geomdepart geometry,tolerance float,altitude double precision)
RETURNS geometry AS
$$
DECLARE
idcarredepart integer:=0;
carredepart geometry;
altituderef double precision:=0;
tempidgrid integer:=0;
tempgeom geometry;
tempaltitude double precision:=0;
toutlageometrieavant geometry;
toutlageometrie geometry;
isendofstory boolean :=false;
BEGIN
IF GeometryType(geomdepart) = 'POINT' THEN
FOR tempidgrid,tempaltitude,tempgeom IN (SELECT idgrid_elevation,mns_lidar, the_geom FROM l3d_grid1m_elevation
WHERE ST_DWITHIN(the_geom,geomdepart, 0.01)
) LOOP
IF tempaltitude > altituderef THEN
idcarredepart := tempidgrid;
altituderef := tempaltitude ;
carredepart := tempgeom;
END IF;
END LOOP;
--au depart etait le carre
toutlageometrie := carredepart;
ELSE
--ensuite etait la geom carre,polygone etc...
toutlageometrie := geomdepart;
END IF;
WHILE NOT isendofstory LOOP
toutlageometrieavant := toutlageometrie;
FOR tempaltitude,tempgeom IN (SELECT mns_lidar, the_geom FROM l3d_grid1m_elevation
WHERE ST_TOUCHES(the_geom,toutlageometrie)
) LOOP
IF abs(tempaltitude - altituderef)<=tolerance THEN
toutlageometrie := st_union(toutlageometrie, tempgeom);
END IF;
END LOOP;
IF ST_EQUALS(toutlageometrie,toutlageometrieavant) THEN
isendofstory := true;
ELSE
toutlageometrie := cncggetisosurface(toutlageometrie ,tolerance,altituderef );
END IF;
END LOOP;
RETURN toutlageometrie;
END;
$$
LANGUAGE plpgsql;
Et ensuite il suffit de tester avec un :
SELECT st_astext(cncggetisosurface('SRID=21781;POINT(538608 153109)'::geometry,0.50,0.0));
Commentaires