cquest

-- update des codes FANTOIR sans clé rivoli sur données opendata
with f as (select fantoir, cle_rivoli from cumul_adresses join fantoir_voie on (code_insee=insee_com and concat(code_insee,id_voie)=fantoir) where source = 'OD-ACCM-Arles' group by fantoir, cle_rivoli) update cumul_adresses c set fantoir = concat(f.fantoir,f.cle_rivoli) from f where c.fantoir=f.fantoir and source like 'OD%';


-- update des voie_osm reprises depuis source cadastre
with v as (select od.fantoir, cu.voie_osm from cumul_adresses od join cumul_adresses cu on (cu.fantoir=od.fantoir and cu.voie_osm is not null and cu.voie_osm != '') where od.source = 'OD-ACCM-Arles' and od.voie_osm is null group by od.fantoir, cu.voie_osm) update cumul_adresses c set voie_osm=v.voie_osm from v where c.voie_osm is null and c.fantoir=v.fantoir and c.source like 'OD%';

-- update nom de voies depuis voie_cadastre
with v as (select od.fantoir, cu.voie_cadastre from cumul_adresses od join cumul_adresses cu on (cu.fantoir=od.fantoir and cu.source not like 'O

cquest

WITH insee AS
  (SELECT tags->'ref:INSEE' AS ref_insee,
          way
   FROM planet_osm_polygon
   WHERE tags ? 'ref:INSEE'
     AND tags->'ref:INSEE' < '97100' /* DOM à traiter à part pour la projection */
     AND admin_level IN ('8',
                         '9')
     AND boundary='administrative')
SELECT insee.ref_insee,

cquest

\copy 
(SELECT *
FROM (WITH insee AS
        (SELECT tags->'ref:INSEE' AS ref_insee,
                way
         FROM planet_osm_polygon
         WHERE tags ? 'ref:INSEE'
           AND admin_level IN ('8',
                               '9')
           AND boundary='administrative')

cquest

/* 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 */

cquest

(SELECT box, floor(10*log(count(*)+1)) AS nb
 FROM planet_osm_point
  JOIN
   (SELECT ST_setSRID(ST_Makebox2D(ST_MakePoint(st_xmin(!bbox!)+x*!pixel_width!, st_ymin(!bbox!)+!pixel_height!*y),ST_MakePoint(st_xmin(!bbox!)+(x+1)*!pixel_width!,st_ymin(!bbox!)+!pixel_height!*(y+1))),900913) AS box
    FROM
     (SELECT generate_series(0,255) AS x) AS h,
       (SELECT generate_series(0,255) AS y) AS v) AS b ON (way && b.box) group by b.box) AS density

cquest

{
  "2733246950": {
    "name": "Cafex",
    "contact:phone": "+33 2 99 23 44 88",
    "contact:fax": "+33 2 99 36 56 02",
    "website": "www.cafex.fr",
    "wheelchair": "yes",
    "opening_hours": "Mo-Th 08:15-12:00, 13:15-17:45; Fr 08:15-12:00, 13:15-17:30",
    "office": "accountant"
  },

cquest

#!/bin/sh

# script pour graphes munin de la BANO sur le décompte global des adresses

case $1 in
   config)
        cat <<'EOM'
graph_title BANO - Contenu
graph_vlabel BANO
graph_category bano

cquest

Codes postaux issus des tags addr:postcode sur les relations des communes au 8/12/2014

select insee,nom_commune,  cp as cp_poste, tags->'addr:postcode' as cp_osm from poste_cp p left join planet_osm_polygon c on (tags->'ref:INSEE'=insee) where tags ? 'ref:INSEE' and admin_level='8' and tags ? 'addr:postcode' and tags->'addr:postcode' not like '%' || cp || '%';


insee |       nom_commune       | cp_poste |         cp_osm          
-------+-------------------------+----------+-------------------------
 01050 | BOISSEY                 | 01380    | 01190
 01269 | NANTUA                  | 01460    | 01130
 03250 | ST PIERRE LAVAL         | 042620   | 42620

cquest

select *, round(100*pop_sans_route::numeric/population::numeric,1), round(100*pop_sans_bati::numeric/population::numeric,1) from (select tags->'ref:INSEE' as dep, count(*) as carreaux, sum(case when highways=0 then ind_c else 0 end) as pop_sans_route, sum(case when buildings=0 then ind_c else 0 end) as pop_sans_bati, sum(ind_c) as population from planet_osm_polygon d join insee_menages on (wkb_geometry && way) where boundary='administrative' and tags ? 'ref:INSEE' and admin_level='6' group by 1 order by 1) as stat;

 dep | carreaux | pop_sans_route | pop_sans_bati | population | %rout | %bati 
-----+----------+----------------+---------------+------------+-------+-------
 01  |    59177 |            903 |          8654 |    2551428 |   0.0 |   0.3
 02  |    35369 |           4994 |        272866 |    1099138 |   0.5 |  24.8
 03  |    46147 |           7685 |        161065 |     498835 |   1.5 |  32.3
 04  |    20406 |           4087 |          6506 |     309063 |   1.3 |   2.1
 05  |    15939 |           1462

cquest

# transforme une liste de commune en liste de way formant le périmètre
#
# exemple: sh jorf2json.sh 90 'Denney, Eloie, Evette-Salbert, Offemont, Roppe, Sermamagny, Valdoie, Vétrigne'
#

# transformation de la liste des communes ',' en | pour overpass
c=`echo $2 | sed 's/æ/ae/g' | sed 's/ et de /, /' | sed 's/, /$|^/g'`

# construction de la requête overpass
d=`echo "relation['ref:INSEE'~'^$1.*'][name~'^$c$'][admin_level=8][boundary=administrative];out;"`