cadastre_electorale.sql

drop table if exists tmpGeoAdresses;
create table tmpGeoAdresses(id text,nom_voie text,id_fantoir text,
			     numero text, rep text,code_insee text,code_post text,
			     alias text,nom_ld text,x float,y float,commune text,fant_voie text,
			     fant_ld text,lat numeric,lon numeric);



create table bureauxAdresse(bureau text, adresse text, cp text, ville text);

create extension pg_trgm;
create extension postgis;
SET pg_trgm.similarity_threshold = 0.8;


drop table if exists bureauxAdresseWithGeo;

create sequence adresseid;

create table bureauxAdresseWithGeo as
select nextval('adresseid'), b.bureau, b.adresse, cp, ville, id as idadminEtatfr, code_insee, fant_voie, x, y, lat, lon, st_setsrid(ST_Point(lon, lat),4326) as geopoint
from bureauxAdresse b , tmpGeoAdresses g
where b.cp = g.code_post and similarity(b.adresse, g.numero || ' ' || g.rep || ' ' || g.nom_voie) > 0.99;
--and b.adresse like (g.numero || ' ' || g.rep || ' ' || g.nom_voie) 



select bureau, count(geopoint), ST_Astext(st_concavehull(st_collect(geopoint),0.99)) from bureauxAdresseWithGeo group by bureau;