Hi,
Some time ago, I wrote a PLPGSQL function that creates urban areas from a
buildings table.
The attached function could help you to solve your issues and test several
thresholds when adapted to your own database. I'm sorry it's written in a weird
French-English style but the queries should help you to understand the variable
names meaning. Of course, feel free to ask any question.
The hole issue is directly adressed line 55 to line 85. It uses st_dumprings
along with st_exterrioring and st_buildarea and compares hole surface to the
"surf_trou" threshold.
HTH,
Hug
--
----- Mail original -----
De: "Luca Bertoncello" <lucab...@lucabert.de>
À: "PostGIS Users Discussion" <postgis-users@lists.osgeo.org>
Envoyé: Lundi 18 Janvier 2021 21:37:44
Objet: Re: [postgis-users] Creating a boundary of an aggregate of buildings
Am 18.01.2021 um 15:09 schrieb Tomas Straupis:
> I've tested on Vilnius and get fine results with coefficients given
> below (but for you numbers could be different depending on which is
> your target scale).
Hi again,
I think I'm on the right way...
I can select the boundaries by size, so I can display almost what I need.
The very problem is that I have many "holes"...
You can see the example: in Dresden there are many holes in the
building, but they are "little", and I want to fill them, leaving the
holes greater than X...
Do you have an idea how to do that?
Thanks
Luca Bertoncello
(lucab...@lucabert.de)
[Fichier texte:ATT00001]
CREATE OR REPLACE FUNCTION stations.tache_urb_ind(
character varying,
character varying,
double precision,
double precision,
double precision,
double precision)
RETURNS text
LANGUAGE 'plpgsql'
COST 100
VOLATILE
AS $BODY$
DECLARE
table_bat ALIAS FOR $1; -- table de batiments en entrée
table_tache ALIAS FOR $2; -- tache urbaine en sortie
dist_dilat ALIAS FOR $3; --distance de dilatation
dist_eros ALIAS FOR $4; --distance d'érosion (positif)
surf_trou ALIAS FOR $5; --surface des trous à boucher
seuil_isole ALIAS FOR $6; --seuil du nombre de bati pour définir le bati isolé
query varchar;
ret text:='';
ind varchar(5);
BEGIN
SET search_path to stations, spatial, public, aptv ;
query := 'drop table if exists '||table_tache||';
create table '||table_tache||' (the_geom geometry, gid integer, indicatif_station varchar(5), type_bat varchar(50));';
EXECUTE query;
FOR ind IN (select distinct indicatif_station from stations.definition_indicatif order by 1) LOOP
--creation du buffer et union
RAISE NOTICE 'Traitement en cours : %', ind;
RAISE NOTICE 'creation du buffer et union';
query:= 'drop table if exists bufun;
CREATE TABLE bufun AS
SELECT
(ST_DUMP (ST_UNION (ST_BUFFER(the_geom, '||dist_dilat||')))).geom as the_geom,
(ST_DUMP (ST_UNION (ST_BUFFER(the_geom, '||dist_dilat||')))).path as gid
FROM
'||table_bat||'
WHERE indicatif_station = '''||ind||''';';
EXECUTE query;
create index idx_bufun_geom on bufun using GIST(the_geom);
RAISE NOTICE 'remplissage des enclaves';
query:=
'drop table if exists trou;
CREATE TABLE trou AS
(select
gid,
st_union(the_geom) as the_geom
from
(with a as (
select
(st_dumprings(the_geom)).geom as the_geom,
(st_dumprings(the_geom)).path
from bufun
)
select distinct gid, a.the_geom
from a, bufun b
where 0 < any (path)
and st_within(a.the_geom, st_buildarea(st_exteriorring(b.the_geom)))
and st_area(a.the_geom) <= '||surf_trou||'
UNION
select gid, the_geom from bufun
) AS foo
group by gid);';
EXECUTE query;
create index trou_geom on bufun using GIST(the_geom);
create index trou_gid on trou (gid);
RAISE NOTICE 'erosion des contours';
query:= 'drop table if exists erode;
CREATE TABLE erode AS
(SELECT
(ST_DUMP (ST_BUFFER (trou.the_geom, (-1*'||dist_eros||')))).geom as the_geom,
(ST_DUMP (ST_BUFFER (trou.the_geom, (-1*'||dist_eros||')))).path[1] as gid
from trou);';
EXECUTE query;
create index erode_geom on erode using GIST(the_geom);
create index erode_gid on erode(gid);
RAISE NOTICE 'attribution du caractère';
query:= 'delete from '||table_tache||' where indicatif_station = '''||ind||''';
insert into '||table_tache||'
SELECT erode.the_geom, erode.gid, '''||ind||''',
(CASE WHEN (count(b.the_geom))<'||seuil_isole||' THEN ''isole'' ELSE ''groupe'' END) AS type_bat
FROM erode, '||table_bat||' b
WHERE ST_INTERSECTS (b.the_geom, erode.the_geom)
AND indicatif_station = '''||ind||'''
GROUP BY
1,2,3;';
EXECUTE QUERY;
RAISE NOTICE 'nettoyage';
query:= 'DROP TABLE erode ; DROP TABLE bufun ; DROP TABLE trou ;';
EXECUTE query;
END LOOP;
query :='
create index idx_'||table_tache||'_geom on '||table_tache||' using GIST(the_geom);
create index idx_'||table_tache||'_gid on '||table_tache||'(gid);';
EXECUTE query;
ret := 'fin du traitement, la table '||table_tache||' a été créée';
return ret;
END;
$BODY$;_______________________________________________
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users
