Skip to content

Instantly share code, notes, and snippets.

@cquest

cquest/polygon -> linestring Secret

Last active January 6, 2022 15:46
Show Gist options
  • Star 8 You must be signed in to star a gist
  • Fork 2 You must be signed in to fork a gist
  • Save cquest/66797473e5663bb4ba43 to your computer and use it in GitHub Desktop.
Save cquest/66797473e5663bb4ba43 to your computer and use it in GitHub Desktop.
Download ZIP
Requêtes postGIS de création de linestring de frontières en partant de polygones jointifs, puis de recréation de polygones avec simplification
Raw
polygon -> linestring
/* table des frontières (linestring) */
create table osm_limites (insee text[], way geometry(geometry,900913), lim10 geometry(geometry,900913), lim100 geometry(geometry,900913), lim250 geometry(geometry,900913), lim2000 geometry(geometry,900913));
create index on osm_limites using gin (insee);
create index on osm_limites using gist (way);
create index on osm_limites using gist (lim10);
create index on osm_limites using gist (lim100);
create index on osm_limites using gist (lim250);
create index on osm_limites using gist (lim2000);
/* table des polygones (normal, simplifié "10" et simplifié "100") */
create table osm_fla (insee text, way geometry, poly10 geometry, poly100 geometry, poly250 geometry, poly2000 geometry);
create index on osm_fla (insee);
create index on osm_fla using gist (way);
create index on osm_fla using gist (poly10);
create index on osm_fla using gist (poly100);
create index on osm_fla using gist (poly250);
create index on osm_fla using gist (poly2000);
/* table temporaire pour les limites */
create table osm_lim (insee text[], way geometry(geometry,900913));
create index on osm_lim using gin (insee);
create index on osm_lim using gist (way);
/* table temporaire de copie des polygones de communes */
create table osm_poly (insee text, way geometry);
create index on osm_poly (insee);
create index on osm_poly using gist (way);
/* copie des polygones à traiter -> 36758 insert en 15s */
/* les codes INSEE exclus sont ceux de Paris, Marseille et Lyon pour n'avoir que leurs arrondissements */
begin; truncate osm_poly; insert into osm_poly (select tags->'ref:INSEE', way from planet_osm_polygon where tags ? 'ref:INSEE' and admin_level in ('8','9') and tags->'ref:INSEE' not in ('75056','69123','13055') and boundary='administrative'); commit;
/* vérification de l'absence de chevauchements, de self-intersection, ou d'invalidité (ne doit rien retourner) -> 140s */
/* overlap */
select p1.insee, p2.insee from osm_poly p1 join osm_poly p2 on (st_overlaps(p1.way, p2.way)) where p1.insee<p2.insee order by p1.insee, p2.insee;
/* si on a des polygones en overlap, on force un recalcul des relations et ways correspondants */
begin;
update planet_osm_rels set pending=true from planet_osm_polygon p where p.tags ? 'ref:INSEE' AND id=-p.osm_id and not pending;
with w as (select unnest(parts) as way_id from planet_osm_rels where pending) update planet_osm_ways set pending=true from w where id=way_id and not pending;
commit;
/* puis attendre la fin du prochain cycle d'osm2pgsql et repartir à la copie des polygones */
select count(*) from planet_osm_rels where pending=true; /* doit retourner 0 i cycle terminé */
/* invalidité ou self-intersection */
select p1.insee, st_issimple(p1.way) as simple, st_isvalid(p1.way) as valid from osm_poly p1 where not st_issimple(p1.way) or not st_isvalid(p1.way) order by p1.insee;
/* on a bien toutes les communes ? */
select i.insee from insee_cog i left join osm_poly p on (p.insee=i.insee) where p.insee is null and i.insee not in ('13055','69123','75056','52379') order by i.insee;
/* génération des frontières entre communes limitrophes -> 134172 insert en 271s */
truncate table osm_lim;
insert
into
osm_lim
(select
array[p.insee,p2.insee] as insee ,
ST_LineMerge (st_intersection(p.way,p2.way)) as way
from
osm_poly p
join
osm_poly p2
on (p2.way && p.way
and p2.insee>p.insee) );
/* ajout des limites côtières et frontalières -> 2350 insert en 100s */
insert
into
osm_lim
(select
array[p.insee] as insee,
st_linemerge(st_difference(p.way, st_setsrid(ST_CollectionHomogenize(st_collect(l.way)), 900913))) as way
from
(select
p.insee as insee,
ST_ExteriorRing((st_dumprings((st_dump(p.way)).geom)).geom) as way
from
(select
insee
from
(select
i.insee,
ST_NumGeometries(ST_Polygonize(l.way)) as nb
from
osm_poly i
join
osm_lim l
on (l.insee @> array[i.insee])
group by
i.insee
order by
i.insee) as poly
where
nb=0) as m
join
osm_poly p
on (p.insee=m.insee)) as p
join
osm_lim l
on (l.insee @> array[p.insee])
group by
p.insee,
p.way);
/* ajout des iles (communes non limitrophes avec une autre) -> 10 insert en 14s */
insert
into
osm_lim
(select
array[insee] ,
st_linemerge(st_collect(way))
from
(select
i.insee ,
ST_ExteriorRing((st_dumprings((st_dump(p.way)).geom)).geom) as way
from
osm_poly i
left join
osm_lim l
on (l.insee @> array[i.insee])
join
osm_poly p
on (p.insee=i.insee)
where
l.insee is null) as lim
group by
insee);
/* nettoyage (26454 suppressions, 1s) */
delete from osm_lim where st_geometrytype(way)='ST_GeometryCollection';
/* 1ère génération des polygones de chaque commune -> 36678 insert en 66s */
truncate osm_fla;
insert
into
osm_fla
(select
i.insee,
ST_SetSRID(ST_BuildArea(ST_Collect(l.way)),900913),
NULL, NULL, NULL
from
osm_poly i
join
osm_lim l
on (l.insee @> array[i.insee])
group by
i.insee);
/* ajout des limites manquantes (exemple: cas particulier des communes côtières avec enclave) -> 23 insert en 7s */
insert
into
osm_lim
(select
array[p.insee] as insee,
st_linemerge(st_difference(p.way,
ST_SetSRID(ST_CollectionHomogenize(st_collect(l.way)), 900913))) as way
from
(select
p.insee as insee,
ST_ExteriorRing((st_dumprings((st_dump(p.way)).geom)).geom) as way
from
(select
f.insee
from
osm_poly p
left join
osm_fla f
on (p.insee=f.insee
and abs(st_area(p.way)-st_area(f.way))>0.001)) as m
join
osm_poly p
on (p.insee=m.insee)) as p
join
osm_lim l
on (l.insee @> array[p.insee])
group by
p.insee,
p.way);
delete from osm_lim where st_geometrytype(way)='ST_GeometryCollection';
/* regroupement des limites par couple insee -> 109078 insert en 7s */
truncate osm_limites;
insert into osm_limites (select insee, st_setsrid(st_collectionhomogenize(st_collect(way)),900913), null,null,null from osm_lim group by insee);
vacuum analyze osm_limites;
cluster osm_limites;
# truncate table osm_lim;
/* calcul des limites simplifiées (10, 100, 250) -> 109078 update en 93s */
update osm_limites
set lim10=st_simplifypreservetopology(way,10),
lim100=st_simplifypreservetopology(way,100),
lim250=st_simplifypreservetopology(way,250),
lim2000=st_simplifypreservetopology(way,2000);
/* génération des polygones des communes: originaux et simplifiés -> 36680 insert en 96s */
truncate osm_fla;
insert into osm_fla (select insee, ST_BuildArea(ST_Collect(way)),ST_BuildArea(ST_Polygonize(lim10)),ST_BuildArea(ST_Polygonize(lim100)),ST_BuildArea(ST_Polygonize(lim250)),NULL from (select unnest(insee) as insee, way, lim10, lim100, lim250 from osm_limites) as lim group by insee);
/* mise à jour des polygones de communes avec enclave -> 21 insert en 3s */
WITH poly AS
(select
f.insee, st_buildarea(st_collect(l.way)) as p,
ST_BuildArea(st_linemerge(st_collect(lim10))) as p10,
st_buildarea(st_linemerge(st_collect(lim100))) as p100,
st_buildarea(st_linemerge(st_collect(lim250))) as p250
FROM osm_fla f
JOIN osm_limites l ON (l.insee @> array[f.insee])
WHERE ST_NRings(f.way)>ST_NumGeometries(f.way)
GROUP BY f.insee)
UPDATE osm_fla o
SET poly10=p10, poly100=p100, poly250=p250
FROM poly
WHERE ST_NRings(o.way)>ST_NumGeometries(o.way) and poly.insee=o.insee;
/* contrôle: liste des polygones de communes manquants par rapport au COG/INSEE -> 2s */
select i.insee from insee_cog i left join osm_fla o on (o.insee=i.insee) where o.insee is null order by insee;
/* contrôle: liste des communes avec un polygone osm_fla différent de osm_poly -> 10s */
select
f.insee,
abs(st_area(geography(st_transform(p.way,4326)))-st_area(geography(st_transform(f.way, 4326)))) as surf_m2
from
osm_poly p
join
osm_fla f
on (
p.insee=f.insee
and abs(st_area(p.way)-st_area(f.way))>0.001
)
order by
f.insee;
/* contrôle: liste des communes avec un problème de topologie sur leur version simplifiée -> à mettre à jour */
select insee from osm_fla where st_nrings(poly10)=0 or st_nrings(poly100)=0 or st_nrings(poly250)=0 order by insee;
/* contrôle: liste des limites à revoir */
select insee, st_nrings(way), st_nrings(poly10) from osm_fla where poly10 is null or st_nrings(way)>st_nrings(poly10) or not st_isvalid(poly10) order by insee;
/* réduction de la simplification 10 -> original */
WITH lim AS
(select f.insee, l1.insee as insee1, l2.insee as insee2, l1.way as l10
from osm_fla f
join osm_limites l1 on (l1.insee @> array[f.insee])
join osm_limites l2 on (l2.insee @> array[f.insee] and l1.insee<l2.insee)
where (st_nrings(poly10)<st_nrings(f.way) or poly10 is null or not st_isvalid(poly10)) and l1.lim10 && l2.lim10)
UPDATE osm_limites l
SET lim10=l10
FROM lim
WHERE l.insee=lim.insee1;
WITH lim AS
(select f.insee, l1.insee as insee1, l2.insee as insee2, l2.way as l10
from osm_fla f
join osm_limites l1 on (l1.insee @> array[f.insee])
join osm_limites l2 on (l2.insee @> array[f.insee] and l1.insee<l2.insee)
where (st_nrings(poly10)<st_nrings(f.way) or poly10 is null or not st_isvalid(poly10)) and l1.lim10 && l2.lim10)
UPDATE osm_limites l
SET lim10=l10
FROM lim
WHERE l.insee=lim.insee2;
/* mise à jour des polygones impactés */
with fla as (select i.insee, ST_BuildArea(ST_Polygonize(lim10)) as p10 FROM (select distinct(unnest(l1.insee || l2.insee)) as insee
from osm_fla f
join osm_limites l1 on (l1.insee @> array[f.insee])
join osm_limites l2 on (l2.insee @> array[f.insee] and l1.insee<l2.insee)
where st_nrings(poly10)<st_nrings(f.way) or poly10 is null or not st_isvalid(poly10)) as i join osm_limites l on (l.insee @> array[i.insee]) group by i.insee) update osm_fla f SET poly10=p10 from fla where f.insee=fla.insee;
WITH poly AS
(select
f.insee, st_buildarea(st_collect(l.way)) as p,
st_buildarea(st_collect(lim10)) as p10
FROM osm_fla f
JOIN osm_limites l ON (l.insee @> array[f.insee])
WHERE ST_NRings(f.way)>ST_NumGeometries(f.way)
GROUP BY f.insee)
UPDATE osm_fla o
SET poly10=p10
FROM poly
WHERE ST_NRings(o.way)>ST_NumGeometries(o.way) and poly.insee=o.insee;
/* liste des communes dont le polygone se superpose (182s) */
select p1.insee, p2.insee, l1.insee, l2.insee from (select unnest(insee) as insee from osm_limites where array_length(insee,1)=1 order by insee) as c1 join (select unnest(insee) as insee from osm_limites where array_length(insee,1)=1 order by insee) as c2 on (c1.insee<c2.insee) join osm_fla p1 on (p1.insee=c1.insee) join osm_fla p2 on (p2.insee=c2.insee and ST_Overlaps(p1.poly10, p2.poly10)) join osm_limites l1 on (l1.insee @> array[p1.insee]) join osm_limites l2 on (l2.insee @> array[p2.insee] and ST_Crosses(l1.lim10, l2.lim10));
/* réduction de la simplification pour les polygones chevauchants 10 -> original */
WITH lim AS
(select l1.insee, l1.way as l10 from (select unnest(insee) as insee from osm_limites where array_length(insee,1)=1 order by insee) as c1 join (select unnest(insee) as insee from osm_limites where array_length(insee,1)=1 order by insee) as c2 on (c1.insee<c2.insee) join osm_fla p1 on (p1.insee=c1.insee) join osm_fla p2 on (p2.insee=c2.insee and ST_Overlaps(p1.poly10, p2.poly10)) join osm_limites l1 on (l1.insee @> array[p1.insee]) join osm_limites l2 on (l2.insee @> array[p2.insee] and ST_Crosses(l1.lim10, l2.lim10)))
UPDATE osm_limites l
SET lim10=l10
FROM lim
WHERE l.insee=lim.insee;
WITH lim AS
(select l2.insee, l2.way as l10 from (select unnest(insee) as insee from osm_limites where array_length(insee,1)=1 order by insee) as c1 join (select unnest(insee) as insee from osm_limites where array_length(insee,1)=1 order by insee) as c2 on (c1.insee<c2.insee) join osm_fla p1 on (p1.insee=c1.insee) join osm_fla p2 on (p2.insee=c2.insee and ST_Overlaps(p1.poly10, p2.poly10)) join osm_limites l1 on (l1.insee @> array[p1.insee]) join osm_limites l2 on (l2.insee @> array[p2.insee] and ST_Crosses(l1.lim10, l2.lim10)))
UPDATE osm_limites l
SET lim10=l10
FROM lim
WHERE l.insee=lim.insee;
with fla as (select f.insee, ST_BuildArea(ST_Polygonize(lim10)) as p10 FROM osm_fla f join osm_limites l on (l.insee @> array[f.insee]) group by f.insee) update osm_fla f SET poly10=p10 from fla where f.insee=fla.insee;
WITH poly AS
(select
f.insee, st_buildarea(st_collect(l.way)) as p,
st_buildarea(st_collect(lim10)) as p10
FROM osm_fla f
JOIN osm_limites l ON (l.insee @> array[f.insee])
WHERE ST_NRings(f.way)>ST_NumGeometries(f.way)
GROUP BY f.insee)
UPDATE osm_fla o
SET poly10=p10
FROM poly
WHERE ST_NRings(o.way)>ST_NumGeometries(o.way) and poly.insee=o.insee;
/* réduction de la simplification 100 -> 50 */
WITH lim AS
(select f.insee, l1.insee as insee1, l2.insee as insee2,st_simplifypreservetopology(l1.way,50) as l100
from osm_fla f
join osm_limites l1 on (l1.insee @> array[f.insee])
join osm_limites l2 on (l2.insee @> array[f.insee] and l1.insee<l2.insee)
where (st_nrings(poly100)<st_nrings(f.way) or poly100 is null) and l1.lim100 && l2.lim100)
UPDATE osm_limites l
SET lim100=l100
FROM lim
WHERE l.insee=lim.insee1;
WITH lim AS
(select f.insee, l1.insee as insee1, l2.insee as insee2,st_simplifypreservetopology(l2.way,50) as l100
from osm_fla f
join osm_limites l1 on (l1.insee @> array[f.insee])
join osm_limites l2 on (l2.insee @> array[f.insee] and l1.insee<l2.insee)
where (st_nrings(poly100)<st_nrings(f.way) or poly100 is null) and l1.lim100 && l2.lim100)
UPDATE osm_limites l
SET lim100=l100
FROM lim
WHERE l.insee=lim.insee2;
/* mise à jour des polygones impactés */
with fla as (select i.insee, ST_BuildArea(ST_Polygonize(lim100)) as p100 FROM (select distinct(unnest(l1.insee || l2.insee)) as insee
from osm_fla f
join osm_limites l1 on (l1.insee @> array[f.insee])
join osm_limites l2 on (l2.insee @> array[f.insee] and l1.insee<l2.insee)
where st_nrings(poly100)<st_nrings(f.way) or poly100 is null) as i join osm_limites l on (l.insee @> array[i.insee]) group by i.insee) update osm_fla f SET poly100=p100 from fla where f.insee=fla.insee;
WITH poly AS
(select
f.insee, st_buildarea(st_collect(l.way)) as p,
st_buildarea(st_linemerge(st_collect(lim100))) as p100
FROM osm_fla f
JOIN osm_limites l ON (l.insee @> array[f.insee])
WHERE ST_NRings(f.way)>ST_NumGeometries(f.way)
GROUP BY f.insee)
UPDATE osm_fla o
SET poly100=p100
FROM poly
WHERE ST_NRings(o.way)>ST_NumGeometries(o.way) and poly.insee=o.insee;
/* réduction de la simplification 100 -> 25 */
WITH lim AS
(select f.insee, l1.insee as insee1, l2.insee as insee2,st_simplifypreservetopology(l1.way,25) as l100
from osm_fla f
join osm_limites l1 on (l1.insee @> array[f.insee])
join osm_limites l2 on (l2.insee @> array[f.insee] and l1.insee<l2.insee)
where (st_nrings(poly100)<st_nrings(f.way) or poly100 is null) and l1.lim100 && l2.lim100)
UPDATE osm_limites l
SET lim100=l100
FROM lim
WHERE l.insee=lim.insee1;
WITH lim AS
(select f.insee, l1.insee as insee1, l2.insee as insee2,st_simplifypreservetopology(l2.way,25) as l100
from osm_fla f
join osm_limites l1 on (l1.insee @> array[f.insee])
join osm_limites l2 on (l2.insee @> array[f.insee] and l1.insee<l2.insee)
where (st_nrings(poly100)<st_nrings(f.way) or poly100 is null) and l1.lim100 && l2.lim100)
UPDATE osm_limites l
SET lim100=l100
FROM lim
WHERE l.insee=lim.insee2;
/* mise à jour des polygones impactés */
with fla as (select i.insee, ST_BuildArea(ST_Polygonize(lim100)) as p100 FROM (select distinct(unnest(l1.insee || l2.insee)) as insee
from osm_fla f
join osm_limites l1 on (l1.insee @> array[f.insee])
join osm_limites l2 on (l2.insee @> array[f.insee] and l1.insee<l2.insee)
where st_nrings(poly100)<st_nrings(f.way) or poly100 is null) as i join osm_limites l on (l.insee @> array[i.insee]) group by i.insee) update osm_fla f SET poly100=p100 from fla where f.insee=fla.insee;
WITH poly AS
(select
f.insee, st_buildarea(st_collect(l.way)) as p,
st_buildarea(st_collect(lim100)) as p100
FROM osm_fla f
JOIN osm_limites l ON (l.insee @> array[f.insee])
WHERE ST_NRings(f.way)>ST_NumGeometries(f.way)
GROUP BY f.insee)
UPDATE osm_fla o
SET poly100=p100
FROM poly
WHERE ST_NRings(o.way)>ST_NumGeometries(o.way) and poly.insee=o.insee;
/* réduction de la simplification pour les polygones chevauchants 100 -> 25 */
WITH lim AS
(select l1.insee, st_simplifypreservetopology(l1.way,25) as l100 from (select unnest(insee) as insee from osm_limites where array_length(insee,1)=1 order by insee) as c1 join (select unnest(insee) as insee from osm_limites where array_length(insee,1)=1 order by insee) as c2 on (c1.insee<c2.insee) join osm_fla p1 on (p1.insee=c1.insee) join osm_fla p2 on (p2.insee=c2.insee and ST_Overlaps(p1.poly100, p2.poly100)) join osm_limites l1 on (l1.insee @> array[p1.insee]) join osm_limites l2 on (l2.insee @> array[p2.insee] and ST_Crosses(l1.lim100, l2.lim100)))
UPDATE osm_limites l
SET lim100=l100
FROM lim
WHERE l.insee=lim.insee;
WITH lim AS
(select l2.insee, st_simplifypreservetopology(l2.way,25) as l100 from (select unnest(insee) as insee from osm_limites where array_length(insee,1)=1 order by insee) as c1 join (select unnest(insee) as insee from osm_limites where array_length(insee,1)=1 order by insee) as c2 on (c1.insee<c2.insee) join osm_fla p1 on (p1.insee=c1.insee) join osm_fla p2 on (p2.insee=c2.insee and ST_Overlaps(p1.poly100, p2.poly100)) join osm_limites l1 on (l1.insee @> array[p1.insee]) join osm_limites l2 on (l2.insee @> array[p2.insee] and ST_Crosses(l1.lim100, l2.lim100)))
UPDATE osm_limites l
SET lim100=l100
FROM lim
WHERE l.insee=lim.insee;
with fla as (select f.insee, ST_BuildArea(ST_Polygonize(lim100)) as p100 FROM osm_fla f join osm_limites l on (l.insee @> array[f.insee]) group by f.insee) update osm_fla f SET poly100=p100 from fla where f.insee=fla.insee;
WITH poly AS
(select
f.insee, st_buildarea(st_collect(l.way)) as p,
st_buildarea(st_collect(lim100)) as p100
FROM osm_fla f
JOIN osm_limites l ON (l.insee @> array[f.insee])
WHERE ST_NRings(f.way)>ST_NumGeometries(f.way)
GROUP BY f.insee)
UPDATE osm_fla o
SET poly100=p100
FROM poly
WHERE ST_NRings(o.way)>ST_NumGeometries(o.way) and poly.insee=o.insee;
/* réduction de la simplification 100 -> 10 */
WITH lim AS
(select f.insee, l1.insee as insee1, l2.insee as insee2,st_simplifypreservetopology(l1.way,10) as l100
from osm_fla f
join osm_limites l1 on (l1.insee @> array[f.insee])
join osm_limites l2 on (l2.insee @> array[f.insee] and l1.insee<l2.insee)
where (st_nrings(poly100)<st_nrings(f.way) or poly100 is null) and l1.lim100 && l2.lim100)
UPDATE osm_limites l
SET lim100=l100
FROM lim
WHERE l.insee=lim.insee1;
WITH lim AS
(select f.insee, l1.insee as insee1, l2.insee as insee2,st_simplifypreservetopology(l2.way,10) as l100
from osm_fla f
join osm_limites l1 on (l1.insee @> array[f.insee])
join osm_limites l2 on (l2.insee @> array[f.insee] and l1.insee<l2.insee)
where (st_nrings(poly100)<st_nrings(f.way) or poly100 is null) and l1.lim100 && l2.lim100)
UPDATE osm_limites l
SET lim100=l100
FROM lim
WHERE l.insee=lim.insee2;
/* mise à jour des polygones impactés */
with fla as (select i.insee, ST_BuildArea(ST_Polygonize(lim100)) as p100 FROM (select distinct(unnest(l1.insee || l2.insee)) as insee
from osm_fla f
join osm_limites l1 on (l1.insee @> array[f.insee])
join osm_limites l2 on (l2.insee @> array[f.insee] and l1.insee<l2.insee)
where st_nrings(poly100)<st_nrings(f.way) or poly100 is null) as i join osm_limites l on (l.insee @> array[i.insee]) group by i.insee) update osm_fla f SET poly100=p100 from fla where f.insee=fla.insee;
WITH poly AS
(select
f.insee, st_buildarea(st_collect(l.way)) as p,
st_buildarea(st_collect(lim100)) as p100
FROM osm_fla f
JOIN osm_limites l ON (l.insee @> array[f.insee])
WHERE ST_NRings(f.way)>ST_NumGeometries(f.way)
GROUP BY f.insee)
UPDATE osm_fla o
SET poly100=p100
FROM poly
WHERE ST_NRings(o.way)>ST_NumGeometries(o.way) and poly.insee=o.insee;
/* réduction de la simplification 100 -> original */
WITH lim AS
(select f.insee, l1.insee as insee1, l2.insee as insee2, l1.way as l100
from osm_fla f
join osm_limites l1 on (l1.insee @> array[f.insee])
join osm_limites l2 on (l2.insee @> array[f.insee] and l1.insee<l2.insee)
where (st_nrings(poly100)<st_nrings(f.way) or poly100 is null) and l1.lim100 && l2.lim100)
UPDATE osm_limites l
SET lim100=l100
FROM lim
WHERE l.insee=lim.insee1;
WITH lim AS
(select f.insee, l1.insee as insee1, l2.insee as insee2, l2.way as l100
from osm_fla f
join osm_limites l1 on (l1.insee @> array[f.insee])
join osm_limites l2 on (l2.insee @> array[f.insee] and l1.insee<l2.insee)
where (st_nrings(poly100)<st_nrings(f.way) or poly100 is null) and l1.lim100 && l2.lim100)
UPDATE osm_limites l
SET lim100=l100
FROM lim
WHERE l.insee=lim.insee2;
/* mise à jour des polygones impactés */
with fla as (select i.insee, ST_BuildArea(ST_Polygonize(lim100)) as p100 FROM (select distinct(unnest(l1.insee || l2.insee)) as insee
from osm_fla f
join osm_limites l1 on (l1.insee @> array[f.insee])
join osm_limites l2 on (l2.insee @> array[f.insee] and l1.insee<l2.insee)
where st_nrings(poly100)<st_nrings(f.way) or poly100 is null) as i join osm_limites l on (l.insee @> array[i.insee]) group by i.insee) update osm_fla f SET poly100=p100 from fla where f.insee=fla.insee;
WITH poly AS
(select
f.insee, st_buildarea(st_collect(l.way)) as p,
st_buildarea(st_collect(lim100)) as p100
FROM osm_fla f
JOIN osm_limites l ON (l.insee @> array[f.insee])
WHERE ST_NRings(f.way)>ST_NumGeometries(f.way)
GROUP BY f.insee)
UPDATE osm_fla o
SET poly100=p100
FROM poly
WHERE ST_NRings(o.way)>ST_NumGeometries(o.way) and poly.insee=o.insee;
/* réduction de la simplification 100 -> original */
WITH lim AS
(select f.insee, l1.insee as insee1, l2.insee as insee2, l1.way as l100
from osm_fla f
join osm_limites l1 on (l1.insee @> array[f.insee])
join osm_limites l2 on (l2.insee @> array[f.insee] and l1.insee<l2.insee)
where (st_nrings(poly100)<st_nrings(f.way) or poly100 is null or not st_isvalid(poly100)) and l1.lim100 && l2.lim100)
UPDATE osm_limites l
SET lim100=l100
FROM lim
WHERE l.insee=lim.insee1;
WITH lim AS
(select f.insee, l1.insee as insee1, l2.insee as insee2, l2.way as l100
from osm_fla f
join osm_limites l1 on (l1.insee @> array[f.insee])
join osm_limites l2 on (l2.insee @> array[f.insee] and l1.insee<l2.insee)
where (st_nrings(poly100)<st_nrings(f.way) or poly100 is null or not st_isvalid(poly100)) and l1.lim100 && l2.lim100)
UPDATE osm_limites l
SET lim100=l100
FROM lim
WHERE l.insee=lim.insee2;
/* mise à jour des polygones impactés */
with fla as (select i.insee, ST_BuildArea(ST_Polygonize(lim100)) as p100 FROM (select distinct(unnest(l1.insee || l2.insee)) as insee
from osm_fla f
join osm_limites l1 on (l1.insee @> array[f.insee])
join osm_limites l2 on (l2.insee @> array[f.insee] and l1.insee<l2.insee)
where st_nrings(poly100)<st_nrings(f.way) or poly100 is null or not st_isvalid(poly100)) as i join osm_limites l on (l.insee @> array[i.insee]) group by i.insee) update osm_fla f SET poly100=p100 from fla where f.insee=fla.insee;
WITH poly AS
(select
f.insee, st_buildarea(st_collect(l.way)) as p,
st_buildarea(st_collect(lim100)) as p100
FROM osm_fla f
JOIN osm_limites l ON (l.insee @> array[f.insee])
WHERE ST_NRings(f.way)>ST_NumGeometries(f.way)
GROUP BY f.insee)
UPDATE osm_fla o
SET poly100=p100
FROM poly
WHERE ST_NRings(o.way)>ST_NumGeometries(o.way) and poly.insee=o.insee;
/* réduction de la simplification pour les polygones chevauchants 100 -> original */
WITH lim AS (select l1.insee, l1.way as l100 from (select distinct(unnest(array[p1.insee, p2.insee])) as insee from osm_fla p1 join osm_fla p2 on (st_overlaps(p1.poly100, p2.poly100)) where p1.insee<p2.insee) as c1 join osm_limites l1 on (l1.insee @> array[c1.insee]) join osm_limites l2 on (st_crosses(l1.lim100, l2.lim100))) UPDATE osm_limites l SET lim100=l100 FROM lim WHERE l.insee=lim.insee;
WITH lim AS (select l2.insee, l2.way as l100 from (select distinct(unnest(array[p1.insee, p2.insee])) as insee from osm_fla p1 join osm_fla p2 on (st_overlaps(p1.poly100, p2.poly100)) where p1.insee<p2.insee) as c1 join osm_limites l1 on (l1.insee @> array[c1.insee]) join osm_limites l2 on (st_crosses(l1.lim100, l2.lim100))) UPDATE osm_limites l SET lim100=l100 FROM lim WHERE l.insee=lim.insee;
with fla as (select f.insee, ST_BuildArea(ST_Polygonize(lim100)) as p100 FROM osm_fla f join osm_limites l on (l.insee @> array[f.insee]) group by f.insee) update osm_fla f SET poly100=p100 from fla where f.insee=fla.insee;
WITH poly AS
(select
f.insee, st_buildarea(st_collect(l.way)) as p,
st_buildarea(st_collect(lim100)) as p100
FROM osm_fla f
JOIN osm_limites l ON (l.insee @> array[f.insee])
WHERE ST_NRings(f.way)>ST_NumGeometries(f.way)
GROUP BY f.insee)
UPDATE osm_fla o
SET poly100=p100
FROM poly
WHERE ST_NRings(o.way)>ST_NumGeometries(o.way) and poly.insee=o.insee;
/* CONTROLE: liste des communes en chevauchement -> doit être vide */
select p1.insee, p2.insee, l1.insee, l2.insee from (select unnest(insee) as insee from osm_limites where array_length(insee,1)=1 order by insee) as c1 join (select unnest(insee) as insee from osm_limites where array_length(insee,1)=1 order by insee) as c2 on (c1.insee<c2.insee) join osm_fla p1 on (p1.insee=c1.insee) join osm_fla p2 on (p2.insee=c2.insee and ST_Overlaps(p1.poly100, p2.poly100)) join osm_limites l1 on (l1.insee @> array[p1.insee]) join osm_limites l2 on (l2.insee @> array[p2.insee] and ST_Crosses(l1.lim100, l2.lim100));
/* CONTROLE: liste des communes avec un problème de topologie ou une différence suite à la simplification */
select insee, st_nrings(way), st_nrings(poly100) from osm_fla where poly100 is null or st_nrings(way)>st_nrings(poly100) or not st_isvalid(poly100) order by insee;
/* réduction de la simplification 250 -> 100 */
WITH lim AS
(select f.insee, l1.insee as insee1, l2.insee as insee2,st_simplifypreservetopology(l1.way,100) as l250
from osm_fla f
join osm_limites l1 on (l1.insee @> array[f.insee])
join osm_limites l2 on (l2.insee @> array[f.insee] and l1.insee<l2.insee)
where (st_nrings(poly250)<st_nrings(f.way) or poly250 is null) and l1.lim250 && l2.lim250)
UPDATE osm_limites l
SET lim250=l250
FROM lim
WHERE l.insee=lim.insee1;
WITH lim AS
(select f.insee, l1.insee as insee1, l2.insee as insee2,st_simplifypreservetopology(l2.way,100) as l250
from osm_fla f
join osm_limites l1 on (l1.insee @> array[f.insee])
join osm_limites l2 on (l2.insee @> array[f.insee] and l1.insee<l2.insee)
where (st_nrings(poly250)<st_nrings(f.way) or poly250 is null) and l1.lim250 && l2.lim250)
UPDATE osm_limites l
SET lim250=l250
FROM lim
WHERE l.insee=lim.insee2;
/* mise à jour des polygones impactés */
with fla as (select i.insee, ST_BuildArea(ST_Polygonize(lim250)) as p250 FROM (select distinct(unnest(l1.insee || l2.insee)) as insee
from osm_fla f
join osm_limites l1 on (l1.insee @> array[f.insee])
join osm_limites l2 on (l2.insee @> array[f.insee] and l1.insee<l2.insee)
where st_nrings(poly250)<st_nrings(f.way) or poly250 is null) as i join osm_limites l on (l.insee @> array[i.insee]) group by i.insee) update osm_fla f SET poly250=p250 from fla where f.insee=fla.insee;
WITH poly AS
(select
f.insee, st_buildarea(st_collect(l.way)) as p,
st_buildarea(st_collect(lim250)) as p250
FROM osm_fla f
JOIN osm_limites l ON (l.insee @> array[f.insee])
WHERE ST_NRings(f.way)>ST_NumGeometries(f.way)
GROUP BY f.insee)
UPDATE osm_fla o
SET poly250=p250
FROM poly
WHERE ST_NRings(o.way)>ST_NumGeometries(o.way) and poly.insee=o.insee;
/* liste des communes en chevauchement */
select p1.insee, p2.insee, l1.insee, l2.insee from (select unnest(insee) as insee from osm_limites where array_length(insee,1)=1 order by insee) as c1 join (select unnest(insee) as insee from osm_limites where array_length(insee,1)=1 order by insee) as c2 on (c1.insee<c2.insee) join osm_fla p1 on (p1.insee=c1.insee) join osm_fla p2 on (p2.insee=c2.insee and ST_Overlaps(p1.poly250, p2.poly250)) join osm_limites l1 on (l1.insee @> array[p1.insee]) join osm_limites l2 on (l2.insee @> array[p2.insee] and ST_Crosses(l1.lim250, l2.lim250));
/* réduction de la simplification pour les polygones chevauchants 250 -> 100 */
WITH lim AS
(select l1.insee, st_simplifypreservetopology(l1.way,100) as l250 from (select unnest(insee) as insee from osm_limites where array_length(insee,1)=1 order by insee) as c1 join (select unnest(insee) as insee from osm_limites where array_length(insee,1)=1 order by insee) as c2 on (c1.insee<c2.insee) join osm_fla p1 on (p1.insee=c1.insee) join osm_fla p2 on (p2.insee=c2.insee and ST_Overlaps(p1.poly250, p2.poly250)) join osm_limites l1 on (l1.insee @> array[p1.insee]) join osm_limites l2 on (l2.insee @> array[p2.insee] and ST_Crosses(l1.lim250, l2.lim250)))
UPDATE osm_limites l
SET lim250=l250
FROM lim
WHERE l.insee=lim.insee;
WITH lim AS
(select l2.insee, st_simplifypreservetopology(l2.way,100) as l250 from (select unnest(insee) as insee from osm_limites where array_length(insee,1)=1 order by insee) as c1 join (select unnest(insee) as insee from osm_limites where array_length(insee,1)=1 order by insee) as c2 on (c1.insee<c2.insee) join osm_fla p1 on (p1.insee=c1.insee) join osm_fla p2 on (p2.insee=c2.insee and ST_Overlaps(p1.poly250, p2.poly250)) join osm_limites l1 on (l1.insee @> array[p1.insee]) join osm_limites l2 on (l2.insee @> array[p2.insee] and ST_Crosses(l1.lim250, l2.lim250)))
UPDATE osm_limites l
SET lim250=l250
FROM lim
WHERE l.insee=lim.insee;
with fla as (select f.insee, ST_BuildArea(ST_Polygonize(lim250)) as p250 FROM osm_fla f join osm_limites l on (l.insee @> array[f.insee]) group by f.insee) update osm_fla f SET poly250=p250 from fla where f.insee=fla.insee;
WITH poly AS
(select
f.insee, st_buildarea(st_collect(l.way)) as p,
st_buildarea(st_collect(lim250)) as p250
FROM osm_fla f
JOIN osm_limites l ON (l.insee @> array[f.insee])
WHERE ST_NRings(f.way)>ST_NumGeometries(f.way)
GROUP BY f.insee)
UPDATE osm_fla o
SET poly250=p250
FROM poly
WHERE ST_NRings(o.way)>ST_NumGeometries(o.way) and poly.insee=o.insee;
/* réduction de la simplification 250 -> 50 */
WITH lim AS
(select f.insee, l1.insee as insee1, l2.insee as insee2,st_simplifypreservetopology(l1.way,50) as l250
from osm_fla f
join osm_limites l1 on (l1.insee @> array[f.insee])
join osm_limites l2 on (l2.insee @> array[f.insee] and l1.insee<l2.insee)
where (st_nrings(poly250)<st_nrings(f.way) or poly250 is null) and l1.lim250 && l2.lim250)
UPDATE osm_limites l
SET lim250=l250
FROM lim
WHERE l.insee=lim.insee1;
WITH lim AS
(select f.insee, l1.insee as insee1, l2.insee as insee2,st_simplifypreservetopology(l2.way,50) as l250
from osm_fla f
join osm_limites l1 on (l1.insee @> array[f.insee])
join osm_limites l2 on (l2.insee @> array[f.insee] and l1.insee<l2.insee)
where (st_nrings(poly250)<st_nrings(f.way) or poly250 is null) and l1.lim250 && l2.lim250)
UPDATE osm_limites l
SET lim250=l250
FROM lim
WHERE l.insee=lim.insee2;
/* mise à jour des polygones impactés */
with fla as (select i.insee, ST_BuildArea(ST_Polygonize(lim250)) as p250 FROM (select distinct(unnest(l1.insee || l2.insee)) as insee
from osm_fla f
join osm_limites l1 on (l1.insee @> array[f.insee])
join osm_limites l2 on (l2.insee @> array[f.insee] and l1.insee<l2.insee)
where st_nrings(poly250)<st_nrings(f.way) or poly250 is null) as i join osm_limites l on (l.insee @> array[i.insee]) group by i.insee) update osm_fla f SET poly250=p250 from fla where f.insee=fla.insee;
WITH poly AS
(select
f.insee, st_buildarea(st_collect(l.way)) as p,
st_buildarea(st_collect(lim250)) as p250
FROM osm_fla f
JOIN osm_limites l ON (l.insee @> array[f.insee])
WHERE ST_NRings(f.way)>ST_NumGeometries(f.way)
GROUP BY f.insee)
UPDATE osm_fla o
SET poly250=p250
FROM poly
WHERE ST_NRings(o.way)>ST_NumGeometries(o.way) and poly.insee=o.insee;
/* réduction de la simplification 250 -> 25 */
WITH lim AS
(select f.insee, l1.insee as insee1, l2.insee as insee2,st_simplifypreservetopology(l1.way,25) as l250
from osm_fla f
join osm_limites l1 on (l1.insee @> array[f.insee])
join osm_limites l2 on (l2.insee @> array[f.insee] and l1.insee<l2.insee)
where (st_nrings(poly250)<st_nrings(f.way) or poly250 is null) and l1.lim250 && l2.lim250)
UPDATE osm_limites l
SET lim250=l250
FROM lim
WHERE l.insee=lim.insee1;
WITH lim AS
(select f.insee, l1.insee as insee1, l2.insee as insee2,st_simplifypreservetopology(l2.way,25) as l250
from osm_fla f
join osm_limites l1 on (l1.insee @> array[f.insee])
join osm_limites l2 on (l2.insee @> array[f.insee] and l1.insee<l2.insee)
where (st_nrings(poly250)<st_nrings(f.way) or poly250 is null) and l1.lim250 && l2.lim250)
UPDATE osm_limites l
SET lim250=l250
FROM lim
WHERE l.insee=lim.insee2;
/* mise à jour des polygones impactés */
with fla as (select i.insee, ST_BuildArea(ST_Polygonize(lim250)) as p250 FROM (select distinct(unnest(l1.insee || l2.insee)) as insee
from osm_fla f
join osm_limites l1 on (l1.insee @> array[f.insee])
join osm_limites l2 on (l2.insee @> array[f.insee] and l1.insee<l2.insee)
where st_nrings(poly250)<st_nrings(f.way) or poly250 is null) as i join osm_limites l on (l.insee @> array[i.insee]) group by i.insee) update osm_fla f SET poly250=p250 from fla where f.insee=fla.insee;
WITH poly AS
(select
f.insee, st_buildarea(st_collect(l.way)) as p,
st_buildarea(st_collect(lim250)) as p250
FROM osm_fla f
JOIN osm_limites l ON (l.insee @> array[f.insee])
WHERE ST_NRings(f.way)>ST_NumGeometries(f.way)
GROUP BY f.insee)
UPDATE osm_fla o
SET poly250=p250
FROM poly
WHERE ST_NRings(o.way)>ST_NumGeometries(o.way) and poly.insee=o.insee;
/* réduction de la simplification pour les polygones chevauchants 250 -> 25 */
WITH lim AS
(select l1.insee, st_simplifypreservetopology(l1.way,25) as l250 from (select unnest(insee) as insee from osm_limites where array_length(insee,1)=1 order by insee) as c1 join (select unnest(insee) as insee from osm_limites where array_length(insee,1)=1 order by insee) as c2 on (c1.insee<c2.insee) join osm_fla p1 on (p1.insee=c1.insee) join osm_fla p2 on (p2.insee=c2.insee and ST_Overlaps(p1.poly250, p2.poly250)) join osm_limites l1 on (l1.insee @> array[p1.insee]) join osm_limites l2 on (l2.insee @> array[p2.insee] and ST_Crosses(l1.lim250, l2.lim250)))
UPDATE osm_limites l
SET lim250=l250
FROM lim
WHERE l.insee=lim.insee;
WITH lim AS
(select l2.insee, st_simplifypreservetopology(l2.way,25) as l250 from (select unnest(insee) as insee from osm_limites where array_length(insee,1)=1 order by insee) as c1 join (select unnest(insee) as insee from osm_limites where array_length(insee,1)=1 order by insee) as c2 on (c1.insee<c2.insee) join osm_fla p1 on (p1.insee=c1.insee) join osm_fla p2 on (p2.insee=c2.insee and ST_Overlaps(p1.poly250, p2.poly250)) join osm_limites l1 on (l1.insee @> array[p1.insee]) join osm_limites l2 on (l2.insee @> array[p2.insee] and ST_Crosses(l1.lim250, l2.lim250)))
UPDATE osm_limites l
SET lim250=l250
FROM lim
WHERE l.insee=lim.insee;
with fla as (select f.insee, ST_BuildArea(ST_Polygonize(lim250)) as p250 FROM osm_fla f join osm_limites l on (l.insee @> array[f.insee]) group by f.insee) update osm_fla f SET poly250=p250 from fla where f.insee=fla.insee;
WITH poly AS
(select
f.insee, st_buildarea(st_collect(l.way)) as p,
st_buildarea(st_collect(lim250)) as p250
FROM osm_fla f
JOIN osm_limites l ON (l.insee @> array[f.insee])
WHERE ST_NRings(f.way)>ST_NumGeometries(f.way)
GROUP BY f.insee)
UPDATE osm_fla o
SET poly250=p250
FROM poly
WHERE ST_NRings(o.way)>ST_NumGeometries(o.way) and poly.insee=o.insee;
/* réduction de la simplification 250 -> 10 */
WITH lim AS
(select f.insee, l1.insee as insee1, l2.insee as insee2,st_simplifypreservetopology(l1.way,10) as l250
from osm_fla f
join osm_limites l1 on (l1.insee @> array[f.insee])
join osm_limites l2 on (l2.insee @> array[f.insee] and l1.insee<l2.insee)
where (st_nrings(poly250)<st_nrings(f.way) or poly250 is null) and l1.lim250 && l2.lim250)
UPDATE osm_limites l
SET lim250=l250
FROM lim
WHERE l.insee=lim.insee1;
WITH lim AS
(select f.insee, l1.insee as insee1, l2.insee as insee2,st_simplifypreservetopology(l2.way,10) as l250
from osm_fla f
join osm_limites l1 on (l1.insee @> array[f.insee])
join osm_limites l2 on (l2.insee @> array[f.insee] and l1.insee<l2.insee)
where (st_nrings(poly250)<st_nrings(f.way) or poly250 is null) and l1.lim250 && l2.lim250)
UPDATE osm_limites l
SET lim250=l250
FROM lim
WHERE l.insee=lim.insee2;
/* mise à jour des polygones impactés */
with fla as (select i.insee, ST_BuildArea(ST_Polygonize(lim250)) as p250 FROM (select distinct(unnest(l1.insee || l2.insee)) as insee
from osm_fla f
join osm_limites l1 on (l1.insee @> array[f.insee])
join osm_limites l2 on (l2.insee @> array[f.insee] and l1.insee<l2.insee)
where st_nrings(poly250)<st_nrings(f.way) or poly250 is null) as i join osm_limites l on (l.insee @> array[i.insee]) group by i.insee) update osm_fla f SET poly250=p250 from fla where f.insee=fla.insee;
WITH poly AS
(select
f.insee, st_buildarea(st_collect(l.way)) as p,
st_buildarea(st_collect(lim250)) as p250
FROM osm_fla f
JOIN osm_limites l ON (l.insee @> array[f.insee])
WHERE ST_NRings(f.way)>ST_NumGeometries(f.way)
GROUP BY f.insee)
UPDATE osm_fla o
SET poly250=p250
FROM poly
WHERE ST_NRings(o.way)>ST_NumGeometries(o.way) and poly.insee=o.insee;
/* réduction de la simplification 250 -> original */
WITH lim AS
(select f.insee, l1.insee as insee1, l2.insee as insee2,l1.way as l250
from osm_fla f
join osm_limites l1 on (l1.insee @> array[f.insee])
join osm_limites l2 on (l2.insee @> array[f.insee] and l1.insee<l2.insee)
where (st_nrings(poly250)<st_nrings(f.way) or poly250 is null) and l1.lim250 && l2.lim250)
UPDATE osm_limites l
SET lim250=l250
FROM lim
WHERE l.insee=lim.insee1;
WITH lim AS
(select f.insee, l1.insee as insee1, l2.insee as insee2,l2.way as l250
from osm_fla f
join osm_limites l1 on (l1.insee @> array[f.insee])
join osm_limites l2 on (l2.insee @> array[f.insee] and l1.insee<l2.insee)
where (st_nrings(poly250)<st_nrings(f.way) or poly250 is null) and l1.lim250 && l2.lim250)
UPDATE osm_limites l
SET lim250=l250
FROM lim
WHERE l.insee=lim.insee2;
/* mise à jour des polygones impactés */
with fla as (select i.insee, ST_BuildArea(ST_Polygonize(lim250)) as p250 FROM (select distinct(unnest(l1.insee || l2.insee)) as insee
from osm_fla f
join osm_limites l1 on (l1.insee @> array[f.insee])
join osm_limites l2 on (l2.insee @> array[f.insee] and l1.insee<l2.insee)
where st_nrings(poly250)<st_nrings(f.way) or poly250 is null) as i join osm_limites l on (l.insee @> array[i.insee]) group by i.insee) update osm_fla f SET poly250=p250 from fla where f.insee=fla.insee;
WITH poly AS
(select
f.insee, st_buildarea(st_collect(l.way)) as p,
st_buildarea(st_collect(lim250)) as p250
FROM osm_fla f
JOIN osm_limites l ON (l.insee @> array[f.insee])
WHERE ST_NRings(f.way)>ST_NumGeometries(f.way)
GROUP BY f.insee)
UPDATE osm_fla o
SET poly250=p250
FROM poly
WHERE ST_NRings(o.way)>ST_NumGeometries(o.way) and poly.insee=o.insee;
/* réduction de la simplification 250 -> original */
WITH lim AS
(select f.insee, l1.insee as insee1, l2.insee as insee2,l1.way as l250
from osm_fla f
join osm_limites l1 on (l1.insee @> array[f.insee])
join osm_limites l2 on (l2.insee @> array[f.insee] and l1.insee<l2.insee)
where (st_nrings(poly250)<st_nrings(f.way) or poly250 is null or not st_isvalid(poly250)) and l1.lim250 && l2.lim250)
UPDATE osm_limites l
SET lim250=l250
FROM lim
WHERE l.insee=lim.insee1;
WITH lim AS
(select f.insee, l1.insee as insee1, l2.insee as insee2,l2.way as l250
from osm_fla f
join osm_limites l1 on (l1.insee @> array[f.insee])
join osm_limites l2 on (l2.insee @> array[f.insee] and l1.insee<l2.insee)
where (st_nrings(poly250)<st_nrings(f.way) or poly250 is null or not st_isvalid(poly250)) and l1.lim250 && l2.lim250)
UPDATE osm_limites l
SET lim250=l250
FROM lim
WHERE l.insee=lim.insee2;
/* mise à jour des polygones impactés */
with fla as (select i.insee, ST_BuildArea(ST_Polygonize(lim250)) as p250 FROM (select distinct(unnest(l1.insee || l2.insee)) as insee
from osm_fla f
join osm_limites l1 on (l1.insee @> array[f.insee])
join osm_limites l2 on (l2.insee @> array[f.insee] and l1.insee<l2.insee)
where st_nrings(poly250)<st_nrings(f.way) or poly250 is null or not st_isvalid(poly250)) as i join osm_limites l on (l.insee @> array[i.insee]) group by i.insee) update osm_fla f SET poly250=p250 from fla where f.insee=fla.insee;
WITH poly AS
(select
f.insee, st_buildarea(st_collect(l.way)) as p,
st_buildarea(st_collect(lim250)) as p250
FROM osm_fla f
JOIN osm_limites l ON (l.insee @> array[f.insee])
WHERE ST_NRings(f.way)>ST_NumGeometries(f.way)
GROUP BY f.insee)
UPDATE osm_fla o
SET poly250=p250
FROM poly
WHERE ST_NRings(o.way)>ST_NumGeometries(o.way) and poly.insee=o.insee;
/* réduction de la simplification pour les polygones chevauchants 250 -> original */
WITH lim AS
(select l1.insee,l1.way as l250 from (select unnest(insee) as insee from osm_limites where array_length(insee,1)=1 order by insee) as c1 join (select unnest(insee) as insee from osm_limites where array_length(insee,1)=1 order by insee) as c2 on (c1.insee<c2.insee) join osm_fla p1 on (p1.insee=c1.insee) join osm_fla p2 on (p2.insee=c2.insee and ST_Overlaps(p1.poly250, p2.poly250)) join osm_limites l1 on (l1.insee @> array[p1.insee]) join osm_limites l2 on (l2.insee @> array[p2.insee] and ST_Crosses(l1.lim250, l2.lim250)))
UPDATE osm_limites l
SET lim250=l250
FROM lim
WHERE l.insee=lim.insee;
WITH lim AS
(select l2.insee,l2.way as l250 from (select unnest(insee) as insee from osm_limites where array_length(insee,1)=1 order by insee) as c1 join (select unnest(insee) as insee from osm_limites where array_length(insee,1)=1 order by insee) as c2 on (c1.insee<c2.insee) join osm_fla p1 on (p1.insee=c1.insee) join osm_fla p2 on (p2.insee=c2.insee and ST_Overlaps(p1.poly250, p2.poly250)) join osm_limites l1 on (l1.insee @> array[p1.insee]) join osm_limites l2 on (l2.insee @> array[p2.insee] and ST_Crosses(l1.lim250, l2.lim250)))
UPDATE osm_limites l
SET lim250=l250
FROM lim
WHERE l.insee=lim.insee;
with fla as (select f.insee, ST_BuildArea(ST_Polygonize(lim250)) as p250 FROM osm_fla f join osm_limites l on (l.insee @> array[f.insee]) group by f.insee) update osm_fla f SET poly250=p250 from fla where f.insee=fla.insee;
WITH poly AS
(select
f.insee, st_buildarea(st_collect(l.way)) as p,
st_buildarea(st_collect(lim250)) as p250
FROM osm_fla f
JOIN osm_limites l ON (l.insee @> array[f.insee])
WHERE ST_NRings(f.way)>ST_NumGeometries(f.way)
GROUP BY f.insee)
UPDATE osm_fla o
SET poly250=p250
FROM poly
WHERE ST_NRings(o.way)>ST_NumGeometries(o.way) and poly.insee=o.insee;
/* réduction de la simplification pour les polygones chevauchants 250 -> original */
WITH lim AS
(select l1.insee,l1.way as l250 from (select unnest(insee) as insee from osm_limites where array_length(insee,1)=1 order by insee) as c1 join (select unnest(insee) as insee from osm_limites where array_length(insee,1)=1 order by insee) as c2 on (c1.insee<c2.insee) join osm_fla p1 on (p1.insee=c1.insee) join osm_fla p2 on (p2.insee=c2.insee and ST_Overlaps(p1.poly250, p2.poly250)) join osm_limites l1 on (l1.insee @> array[p1.insee]) join osm_limites l2 on (l2.insee @> array[p2.insee] and ST_Crosses(l1.lim250, l2.lim250)))
UPDATE osm_limites l
SET lim250=l250
FROM lim
WHERE l.insee=lim.insee;
WITH lim AS
(select l2.insee,l2.way as l250 from (select unnest(insee) as insee from osm_limites where array_length(insee,1)=1 order by insee) as c1 join (select unnest(insee) as insee from osm_limites where array_length(insee,1)=1 order by insee) as c2 on (c1.insee<c2.insee) join osm_fla p1 on (p1.insee=c1.insee) join osm_fla p2 on (p2.insee=c2.insee and ST_Overlaps(p1.poly250, p2.poly250)) join osm_limites l1 on (l1.insee @> array[p1.insee]) join osm_limites l2 on (l2.insee @> array[p2.insee] and ST_Crosses(l1.lim250, l2.lim250)))
UPDATE osm_limites l
SET lim250=l250
FROM lim
WHERE l.insee=lim.insee;
with fla as (select f.insee, ST_BuildArea(ST_Polygonize(lim250)) as p250 FROM osm_fla f join osm_limites l on (l.insee @> array[f.insee]) group by f.insee) update osm_fla f SET poly250=p250 from fla where f.insee=fla.insee;
WITH poly AS
(select
f.insee, st_buildarea(st_collect(l.way)) as p,
st_buildarea(st_collect(lim250)) as p250
FROM osm_fla f
JOIN osm_limites l ON (l.insee @> array[f.insee])
WHERE ST_NRings(f.way)>ST_NumGeometries(f.way)
GROUP BY f.insee)
UPDATE osm_fla o
SET poly250=p250
FROM poly
WHERE ST_NRings(o.way)>ST_NumGeometries(o.way) and poly.insee=o.insee;
/* réduction de la simplification pour les polygones chevauchants 250 -> original */
WITH lim AS
(select l1.insee,l1.way as l250 from (select unnest(insee) as insee from osm_limites where array_length(insee,1)=1 order by insee) as c1 join (select unnest(insee) as insee from osm_limites where array_length(insee,1)=1 order by insee) as c2 on (c1.insee<c2.insee) join osm_fla p1 on (p1.insee=c1.insee) join osm_fla p2 on (p2.insee=c2.insee and ST_Overlaps(p1.poly250, p2.poly250)) join osm_limites l1 on (l1.insee @> array[p1.insee]) join osm_limites l2 on (l2.insee @> array[p2.insee] and ST_Crosses(l1.lim250, l2.lim250)))
UPDATE osm_limites l
SET lim250=l250
FROM lim
WHERE l.insee=lim.insee;
WITH lim AS
(select l2.insee,l2.way as l250 from (select unnest(insee) as insee from osm_limites where array_length(insee,1)=1 order by insee) as c1 join (select unnest(insee) as insee from osm_limites where array_length(insee,1)=1 order by insee) as c2 on (c1.insee<c2.insee) join osm_fla p1 on (p1.insee=c1.insee) join osm_fla p2 on (p2.insee=c2.insee and ST_Overlaps(p1.poly250, p2.poly250)) join osm_limites l1 on (l1.insee @> array[p1.insee]) join osm_limites l2 on (l2.insee @> array[p2.insee] and ST_Crosses(l1.lim250, l2.lim250)))
UPDATE osm_limites l
SET lim250=l250
FROM lim
WHERE l.insee=lim.insee;
with fla as (select f.insee, ST_BuildArea(ST_Polygonize(lim250)) as p250 FROM osm_fla f join osm_limites l on (l.insee @> array[f.insee]) group by f.insee) update osm_fla f SET poly250=p250 from fla where f.insee=fla.insee;
WITH poly AS
(select
f.insee, st_buildarea(st_collect(l.way)) as p,
st_buildarea(st_collect(lim250)) as p250
FROM osm_fla f
JOIN osm_limites l ON (l.insee @> array[f.insee])
WHERE ST_NRings(f.way)>ST_NumGeometries(f.way)
GROUP BY f.insee)
UPDATE osm_fla o
SET poly250=p250
FROM poly
WHERE ST_NRings(o.way)>ST_NumGeometries(o.way) and poly.insee=o.insee;
/* CONTROLE: liste des communes se chevauchant -> doit être vide */
select p1.insee, p2.insee, l1.insee, l2.insee from (select unnest(insee) as insee from osm_limites where array_length(insee,1)=1 order by insee) as c1 join (select unnest(insee) as insee from osm_limites where array_length(insee,1)=1 order by insee) as c2 on (c1.insee<c2.insee) join osm_fla p1 on (p1.insee=c1.insee) join osm_fla p2 on (p2.insee=c2.insee and ST_Overlaps(p1.poly250, p2.poly250)) join osm_limites l1 on (l1.insee @> array[p1.insee]) join osm_limites l2 on (l2.insee @> array[p2.insee] and ST_Crosses(l1.lim250, l2.lim250));
/* CONTROLE: communes avec un problème de topologie, ou une différence suite à la simplification */
select insee, st_nrings(way), st_nrings(poly250) from osm_fla where poly250 is null or st_nrings(way)>st_nrings(poly250) or not st_isvalid(poly250) order by insee;
/* simplification à 1000m */
/* copie des limites à 100m : 109258 en 18s */
update osm_limites set lim2000=lim250;
/* simplification des limites qui n'avaient pas été changées : 109010 en 38s */
update osm_limites set lim2000=st_simplifypreservetopology(lim2000,2000) where lim250 = st_simplifypreservetopology(way,250);
/* première version des polygones simplifiés : 36758 en 32s */
with fla as (select f.insee, ST_BuildArea(ST_Polygonize(lim2000)) as p2000 FROM osm_fla f join osm_limites l on (l.insee @> array[f.insee]) group by f.insee) update osm_fla f SET poly2000=p2000 from fla where f.insee=fla.insee;
WITH poly AS
(select
f.insee, st_buildarea(st_collect(l.way)) as p,
st_buildarea(st_collect(lim2000)) as p2000
FROM osm_fla f
JOIN osm_limites l ON (l.insee @> array[f.insee])
WHERE ST_NRings(f.way)>ST_NumGeometries(f.way)
GROUP BY f.insee)
UPDATE osm_fla o
SET poly2000=p2000
FROM poly
WHERE ST_NRings(o.way)>ST_NumGeometries(o.way) and poly.insee=o.insee;
/* communes avec un problème de topologie */
select insee, st_nrings(way), st_nrings(poly2000) from osm_fla where poly2000 is null or st_nrings(way)>st_nrings(poly2000) or not st_isvalid(poly2000) order by insee;
/* réduction de la simplification 2000 -> 500 */
WITH lim AS
(select f.insee, l1.insee as insee1, l2.insee as insee2,st_simplifypreservetopology(l1.way,500) as simp
from osm_fla f
join osm_limites l1 on (l1.insee @> array[f.insee])
join osm_limites l2 on (l2.insee @> array[f.insee] and l1.insee<l2.insee)
where (st_nrings(poly2000)<st_nrings(f.way) or poly2000 is null) and l1.lim2000 && l2.lim2000)
UPDATE osm_limites l
SET lim2000=simp
FROM lim
WHERE l.insee=lim.insee1 and st_npoints(simp)<st_npoints(lim2000);
WITH lim AS
(select f.insee, l1.insee as insee1, l2.insee as insee2,st_simplifypreservetopology(l2.way,500) as simp
from osm_fla f
join osm_limites l1 on (l1.insee @> array[f.insee])
join osm_limites l2 on (l2.insee @> array[f.insee] and l1.insee<l2.insee)
where (st_nrings(poly2000)<st_nrings(f.way) or poly2000 is null) and l1.lim2000 && l2.lim2000)
UPDATE osm_limites l
SET lim2000=simp
FROM lim
WHERE l.insee=lim.insee2 and st_npoints(simp)<st_npoints(lim2000);
/* mise à jour des polygones impactés */
with fla as (select i.insee, ST_BuildArea(ST_Polygonize(lim2000)) as p2000 FROM (select distinct(unnest(l1.insee || l2.insee)) as insee
from osm_fla f
join osm_limites l1 on (l1.insee @> array[f.insee])
join osm_limites l2 on (l2.insee @> array[f.insee] and l1.insee<l2.insee)
where st_nrings(poly2000)<st_nrings(f.way) or poly2000 is null) as i join osm_limites l on (l.insee @> array[i.insee]) group by i.insee) update osm_fla f SET poly2000=p2000 from fla where f.insee=fla.insee;
WITH poly AS
(select
f.insee, st_buildarea(st_collect(l.way)) as p,
st_buildarea(st_collect(lim2000)) as p2000
FROM osm_fla f
JOIN osm_limites l ON (l.insee @> array[f.insee])
WHERE ST_NRings(f.way)>ST_NumGeometries(f.way)
GROUP BY f.insee)
UPDATE osm_fla o
SET poly2000=p2000
FROM poly
WHERE ST_NRings(o.way)>ST_NumGeometries(o.way) and poly.insee=o.insee;
/* réduction de la simplification pour les polygones chevauchants 2000 -> 500 */
WITH lim AS (select l1.insee, st_simplifypreservetopology(l1.way,500) as l2000 from (select distinct(unnest(array[p1.insee, p2.insee])) as insee from osm_fla p1 join osm_fla p2 on (st_overlaps(p1.poly2000, p2.poly2000)) where p1.insee<p2.insee) as c1 join osm_limites l1 on (l1.insee @> array[c1.insee]) join osm_limites l2 on (l1.lim2000 && l2.lim2000)) UPDATE osm_limites l SET lim2000=l2000 FROM lim WHERE l.insee=lim.insee and st_npoints(l2000)<st_npoints(lim2000);
WITH lim AS (select l2.insee, st_simplifypreservetopology(l2.way,500) as l2000 from (select distinct(unnest(array[p1.insee, p2.insee])) as insee from osm_fla p1 join osm_fla p2 on (st_overlaps(p1.poly2000, p2.poly2000)) where p1.insee<p2.insee) as c1 join osm_limites l1 on (l1.insee @> array[c1.insee]) join osm_limites l2 on (l1.lim2000 && l2.lim2000)) UPDATE osm_limites l SET lim2000=l2000 FROM lim WHERE l.insee=lim.insee and st_npoints(l2000)<st_npoints(lim2000);
with fla as (select f.insee, ST_BuildArea(ST_Polygonize(lim2000)) as p2000 FROM osm_fla f join osm_limites l on (l.insee @> array[f.insee]) group by f.insee) update osm_fla f SET poly2000=p2000 from fla where f.insee=fla.insee;
WITH poly AS
(select
f.insee, st_buildarea(st_collect(l.way)) as p,
st_buildarea(st_collect(lim2000)) as p2000
FROM osm_fla f
JOIN osm_limites l ON (l.insee @> array[f.insee])
WHERE ST_NRings(f.way)>ST_NumGeometries(f.way)
GROUP BY f.insee)
UPDATE osm_fla o
SET poly2000=p2000
FROM poly
WHERE ST_NRings(o.way)>ST_NumGeometries(o.way) and poly.insee=o.insee;
/* réduction de la simplification 2000 -> 100 */
WITH lim AS
(select f.insee, l1.insee as insee1, l2.insee as insee2,st_simplifypreservetopology(l1.way,100) as simp
from osm_fla f
join osm_limites l1 on (l1.insee @> array[f.insee])
join osm_limites l2 on (l2.insee @> array[f.insee] and l1.insee<l2.insee)
where (st_nrings(poly2000)<st_nrings(f.way) or poly2000 is null) and l1.lim2000 && l2.lim2000)
UPDATE osm_limites l
SET lim2000=simp
FROM lim
WHERE l.insee=lim.insee1 and st_npoints(simp)<st_npoints(lim2000);
WITH lim AS
(select f.insee, l1.insee as insee1, l2.insee as insee2,st_simplifypreservetopology(l2.way,100) as simp
from osm_fla f
join osm_limites l1 on (l1.insee @> array[f.insee])
join osm_limites l2 on (l2.insee @> array[f.insee] and l1.insee<l2.insee)
where (st_nrings(poly2000)<st_nrings(f.way) or poly2000 is null) and l1.lim2000 && l2.lim2000)
UPDATE osm_limites l
SET lim2000=simp
FROM lim
WHERE l.insee=lim.insee2 and st_npoints(simp)<st_npoints(lim2000);
/* mise à jour des polygones impactés */
with fla as (select i.insee, ST_BuildArea(ST_Polygonize(lim2000)) as p2000 FROM (select distinct(unnest(l1.insee || l2.insee)) as insee
from osm_fla f
join osm_limites l1 on (l1.insee @> array[f.insee])
join osm_limites l2 on (l2.insee @> array[f.insee] and l1.insee<l2.insee)
where st_nrings(poly2000)<st_nrings(f.way) or poly2000 is null) as i join osm_limites l on (l.insee @> array[i.insee]) group by i.insee) update osm_fla f SET poly2000=p2000 from fla where f.insee=fla.insee;
WITH poly AS
(select
f.insee, st_buildarea(st_collect(l.way)) as p,
st_buildarea(st_collect(lim2000)) as p2000
FROM osm_fla f
JOIN osm_limites l ON (l.insee @> array[f.insee])
WHERE ST_NRings(f.way)>ST_NumGeometries(f.way)
GROUP BY f.insee)
UPDATE osm_fla o
SET poly2000=p2000
FROM poly
WHERE ST_NRings(o.way)>ST_NumGeometries(o.way) and poly.insee=o.insee;
/* réduction de la simplification pour les polygones chevauchants 2000 -> 100 */
WITH lim AS (select l1.insee, st_simplifypreservetopology(l1.way,100) as l2000 from (select distinct(unnest(array[p1.insee, p2.insee])) as insee from osm_fla p1 join osm_fla p2 on (st_overlaps(p1.poly2000, p2.poly2000)) where p1.insee<p2.insee) as c1 join osm_limites l1 on (l1.insee @> array[c1.insee]) join osm_limites l2 on (st_crosses(l1.lim2000, l2.lim2000))) UPDATE osm_limites l SET lim2000=l2000 FROM lim WHERE l.insee=lim.insee and st_npoints(l2000)<st_npoints(lim2000);
WITH lim AS (select l2.insee, st_simplifypreservetopology(l2.way,100) as l2000 from (select distinct(unnest(array[p1.insee, p2.insee])) as insee from osm_fla p1 join osm_fla p2 on (st_overlaps(p1.poly2000, p2.poly2000)) where p1.insee<p2.insee) as c1 join osm_limites l1 on (l1.insee @> array[c1.insee]) join osm_limites l2 on (st_crosses(l1.lim2000, l2.lim2000))) UPDATE osm_limites l SET lim2000=l2000 FROM lim WHERE l.insee=lim.insee and st_npoints(l2000)<st_npoints(lim2000);
with fla as (select f.insee, ST_BuildArea(ST_Polygonize(lim2000)) as p2000 FROM osm_fla f join osm_limites l on (l.insee @> array[f.insee]) group by f.insee) update osm_fla f SET poly2000=p2000 from fla where f.insee=fla.insee;
WITH poly AS
(select
f.insee, st_buildarea(st_collect(l.way)) as p,
st_buildarea(st_collect(lim2000)) as p2000
FROM osm_fla f
JOIN osm_limites l ON (l.insee @> array[f.insee])
WHERE ST_NRings(f.way)>ST_NumGeometries(f.way)
GROUP BY f.insee)
UPDATE osm_fla o
SET poly2000=p2000
FROM poly
WHERE ST_NRings(o.way)>ST_NumGeometries(o.way) and poly.insee=o.insee;
/* réduction de la simplification 2000 -> 25 */
WITH lim AS
(select f.insee, l1.insee as insee1, l2.insee as insee2,st_simplifypreservetopology(l1.way,25) as simp
from osm_fla f
join osm_limites l1 on (l1.insee @> array[f.insee])
join osm_limites l2 on (l2.insee @> array[f.insee] and l1.insee<l2.insee)
where (st_nrings(poly2000)<st_nrings(f.way) or poly2000 is null) and l1.lim2000 && l2.lim2000)
UPDATE osm_limites l
SET lim2000=simp
FROM lim
WHERE l.insee=lim.insee1 and st_npoints(simp)<st_npoints(lim2000);
WITH lim AS
(select f.insee, l1.insee as insee1, l2.insee as insee2,st_simplifypreservetopology(l2.way,25) as simp
from osm_fla f
join osm_limites l1 on (l1.insee @> array[f.insee])
join osm_limites l2 on (l2.insee @> array[f.insee] and l1.insee<l2.insee)
where (st_nrings(poly2000)<st_nrings(f.way) or poly2000 is null) and l1.lim2000 && l2.lim2000)
UPDATE osm_limites l
SET lim2000=simp
FROM lim
WHERE l.insee=lim.insee2 and st_npoints(simp)<st_npoints(lim2000);
/* mise à jour des polygones impactés */
with fla as (select i.insee, ST_BuildArea(ST_Polygonize(lim2000)) as p2000 FROM (select distinct(unnest(l1.insee || l2.insee)) as insee
from osm_fla f
join osm_limites l1 on (l1.insee @> array[f.insee])
join osm_limites l2 on (l2.insee @> array[f.insee] and l1.insee<l2.insee)
where st_nrings(poly2000)<st_nrings(f.way) or poly2000 is null) as i join osm_limites l on (l.insee @> array[i.insee]) group by i.insee) update osm_fla f SET poly2000=p2000 from fla where f.insee=fla.insee;
WITH poly AS
(select
f.insee, st_buildarea(st_collect(l.way)) as p,
st_buildarea(st_collect(lim2000)) as p2000
FROM osm_fla f
JOIN osm_limites l ON (l.insee @> array[f.insee])
WHERE ST_NRings(f.way)>ST_NumGeometries(f.way)
GROUP BY f.insee)
UPDATE osm_fla o
SET poly2000=p2000
FROM poly
WHERE ST_NRings(o.way)>ST_NumGeometries(o.way) and poly.insee=o.insee;
/* réduction de la simplification pour les polygones chevauchants 2000 -> 25 */
WITH lim AS (select l1.insee, st_simplifypreservetopology(l1.way,25) as l2000 from (select distinct(unnest(array[p1.insee, p2.insee])) as insee from osm_fla p1 join osm_fla p2 on (st_overlaps(p1.poly2000, p2.poly2000)) where p1.insee<p2.insee) as c1 join osm_limites l1 on (l1.insee @> array[c1.insee]) join osm_limites l2 on (st_crosses(l1.lim2000, l2.lim2000))) UPDATE osm_limites l SET lim2000=l2000 FROM lim WHERE l.insee=lim.insee;
WITH lim AS (select l2.insee, st_simplifypreservetopology(l2.way,25) as l2000 from (select distinct(unnest(array[p1.insee, p2.insee])) as insee from osm_fla p1 join osm_fla p2 on (st_overlaps(p1.poly2000, p2.poly2000)) where p1.insee<p2.insee) as c1 join osm_limites l1 on (l1.insee @> array[c1.insee]) join osm_limites l2 on (st_crosses(l1.lim2000, l2.lim2000))) UPDATE osm_limites l SET lim2000=l2000 FROM lim WHERE l.insee=lim.insee;
with fla as (select f.insee, ST_BuildArea(ST_Polygonize(lim2000)) as p2000 FROM osm_fla f join osm_limites l on (l.insee @> array[f.insee]) group by f.insee) update osm_fla f SET poly2000=p2000 from fla where f.insee=fla.insee;
WITH poly AS
(select
f.insee, st_buildarea(st_collect(l.way)) as p,
st_buildarea(st_collect(lim2000)) as p2000
FROM osm_fla f
JOIN osm_limites l ON (l.insee @> array[f.insee])
WHERE ST_NRings(f.way)>ST_NumGeometries(f.way)
GROUP BY f.insee)
UPDATE osm_fla o
SET poly2000=p2000
FROM poly
WHERE ST_NRings(o.way)>ST_NumGeometries(o.way) and poly.insee=o.insee;
/* réduction de la simplification 2000 -> original (à répéter) */
WITH lim AS
(select f.insee, l1.insee as insee1, l2.insee as insee2,l1.way as simp
from osm_fla f
join osm_limites l1 on (l1.insee @> array[f.insee])
join osm_limites l2 on (l2.insee @> array[f.insee] and l1.insee<l2.insee)
where (st_nrings(poly2000)<st_nrings(f.way) or poly2000 is null or not st_isvalid(poly2000)) and l1.lim2000 && l2.lim2000)
UPDATE osm_limites l
SET lim2000=simp
FROM lim
WHERE l.insee=lim.insee1;
WITH lim AS
(select f.insee, l1.insee as insee1, l2.insee as insee2,l2.way as simp
from osm_fla f
join osm_limites l1 on (l1.insee @> array[f.insee])
join osm_limites l2 on (l2.insee @> array[f.insee] and l1.insee<l2.insee)
where (st_nrings(poly2000)<st_nrings(f.way) or poly2000 is null or not st_isvalid(poly2000)) and l1.lim2000 && l2.lim2000)
UPDATE osm_limites l
SET lim2000=simp
FROM lim
WHERE l.insee=lim.insee2;
/* mise à jour des polygones impactés */
with fla as (select i.insee, ST_BuildArea(ST_Polygonize(lim2000)) as p2000 FROM (select distinct(unnest(l1.insee || l2.insee)) as insee
from osm_fla f
join osm_limites l1 on (l1.insee @> array[f.insee])
join osm_limites l2 on (l2.insee @> array[f.insee] and l1.insee<l2.insee)
where st_nrings(poly2000)<st_nrings(f.way) or poly2000 is null or not st_isvalid(poly2000)) as i join osm_limites l on (l.insee @> array[i.insee]) group by i.insee) update osm_fla f SET poly2000=p2000 from fla where f.insee=fla.insee;
WITH poly AS
(select
f.insee, st_buildarea(st_collect(l.way)) as p,
st_buildarea(st_collect(lim2000)) as p2000
FROM osm_fla f
JOIN osm_limites l ON (l.insee @> array[f.insee])
WHERE ST_NRings(f.way)>ST_NumGeometries(f.way)
GROUP BY f.insee)
UPDATE osm_fla o
SET poly2000=p2000
FROM poly
WHERE ST_NRings(o.way)>ST_NumGeometries(o.way) and poly.insee=o.insee;
/* réduction de la simplification pour les polygones chevauchants 2000 -> original */
WITH lim AS (select l1.insee, l1.way as l2000 from (select distinct(unnest(array[p1.insee, p2.insee])) as insee from osm_fla p1 join osm_fla p2 on (st_overlaps(p1.poly2000, p2.poly2000)) where p1.insee<p2.insee) as c1 join osm_limites l1 on (l1.insee @> array[c1.insee]) join osm_limites l2 on (st_crosses(l1.lim2000, l2.lim2000))) UPDATE osm_limites l SET lim2000=l2000 FROM lim WHERE l.insee=lim.insee;
WITH lim AS (select l2.insee, l2.way as l2000 from (select distinct(unnest(array[p1.insee, p2.insee])) as insee from osm_fla p1 join osm_fla p2 on (st_overlaps(p1.poly2000, p2.poly2000)) where p1.insee<p2.insee) as c1 join osm_limites l1 on (l1.insee @> array[c1.insee]) join osm_limites l2 on (st_crosses(l1.lim2000, l2.lim2000))) UPDATE osm_limites l SET lim2000=l2000 FROM lim WHERE l.insee=lim.insee;
with fla as (select f.insee, ST_BuildArea(ST_Polygonize(lim2000)) as p2000 FROM osm_fla f join osm_limites l on (l.insee @> array[f.insee]) group by f.insee) update osm_fla f SET poly2000=p2000 from fla where f.insee=fla.insee;
WITH poly AS
(select
f.insee, st_buildarea(st_collect(l.way)) as p,
st_buildarea(st_collect(lim2000)) as p2000
FROM osm_fla f
JOIN osm_limites l ON (l.insee @> array[f.insee])
WHERE ST_NRings(f.way)>ST_NumGeometries(f.way)
GROUP BY f.insee)
UPDATE osm_fla o
SET poly2000=p2000
FROM poly
WHERE ST_NRings(o.way)>ST_NumGeometries(o.way) and poly.insee=o.insee;
/* il reste des communes sans géométrie ? */
select insee, st_nrings(poly10) as rings10, st_nrings(poly100) as rings100, st_nrings(poly250) as rings250, st_nrings(poly2000) as rings2000 from osm_fla where st_nrings(poly10)=0 or st_nrings(poly100)=0 or st_nrings(poly250)=0 or st_nrings(poly2000)=0 order by insee;
/* vérification de l'absence de chevauchements (ne doit rien retourner) */
select p1.insee, p2.insee from osm_fla p1 join osm_fla p2 on (st_overlaps(p1.poly10, p2.poly10)) where p1.insee<p2.insee;
select p1.insee, p2.insee from osm_fla p1 join osm_fla p2 on (st_overlaps(p1.poly100, p2.poly100)) where p1.insee<p2.insee;
select p1.insee, p2.insee from osm_fla p1 join osm_fla p2 on (st_overlaps(p1.poly250, p2.poly250)) where p1.insee<p2.insee;
select p1.insee, p2.insee from osm_fla p1 join osm_fla p2 on (st_overlaps(p1.poly2000, p2.poly2000)) where p1.insee<p2.insee;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment