Nettoyage du fichier accidents

cleanup_accidents.sql

/* création de la table */
create table accidents (num_acc text, adr text, gps text, lat float, long float, dep text, insee_com text, postal_code text, nom_comm text, nom_dept text, nom_region text);

/* import des data */
\copy accidents from '/home/cquest/for_geoloc.csv' csv header;

/* ajout de la colonne des distances lat/lon à la commune */
alter table accidents add column dist float;

/* calcul des distances des lat/long au point le plus proche du polygone de la commune */
update accidents set dist = st_length(st_transform(ST_ShortestLine(wkb_geometry, st_setsrid(st_point(long,lat),4326)),2154))/1000 from communes where insee=insee_com and lat!=0 and insee_com < '97100';

/* cas particulier des latitude > 90° */
update accidents set lat=0, long=0 where num_acc in ('201200006667','201200006668'); /* lat > 90 */

/* mise à jour des accidents sans coordonnée (lat=0) et sans adresse avec le lat/lon du centroid de la commune */
update accidents set lat=st_y(st_centroid(wkb_geometry)), long=st_x(st_centroid(wkb_geometry)) from communes where (insee_com=insee) and lat=0 and adr is null;UPDATE

/* inversion des lat/lon */
with u as (select num_acc as u_acc, lat, long from (select accidents.*, st_length(st_transform(ST_ShortestLine(wkb_geometry, st_setsrid(st_point(long,lat),4326)),2154))/1000 as dist, st_length(st_transform(ST_ShortestLine(wkb_geometry, st_setsrid(st_point(lat,long),4326)),2154))/1000 as dist2 from accidents join communes on (insee=insee_com and st_contains(wkb_geometry, st_setsrid(st_point(long,lat),4326))=false) where lat!=0 and insee_com < '97100') as ll where dist2=0) update accidents set lat=u.long, long=u.lat from u where num_acc=u_acc;

/* lat/long inversé avec long décalé d'une décimale */
with u as (select num_acc as u_acc, lat, long from (select accidents.*, st_length(st_transform(ST_ShortestLine(wkb_geometry, st_setsrid(st_point(long,lat),4326)),2154))/1000 as dist, st_length(st_transform(ST_ShortestLine(wkb_geometry, st_setsrid(st_point(lat,long*10),4326)),2154))/1000 as dist2 from accidents join communes on (insee=insee_com and st_contains(wkb_geometry, st_setsrid(st_point(long,lat),4326))=false) where lat!=0 and long<9 and insee_com < '97100') as ll where dist2=0) update accidents set lat=u.long, long=u.lat from u where num_acc=u_acc;

/* calcul des distances des lat/long au point le plus proche du polygone de la commune */
update accidents set dist = st_length(st_transform(ST_ShortestLine(wkb_geometry, st_setsrid(st_point(long,lat),4326)),2154))/1000 from communes where insee=insee_com and lat!=0 and insee_com < '97100';

\copy (select * from accidents order by num_acc) to '/home/cquest/accidents.csv' csv header;