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;"`

cquest

jorf;canton;nom;bureau
JORFTEXT000028621797;001-01;Ambérieu-en-Bugey;Ambérieu-en-Bugey
JORFTEXT000028621797;001-02;Attignat;Attignat
JORFTEXT000028621797;001-03;Bellegarde-sur-Valserine;Bellegarde-sur-Valserine
JORFTEXT000028621797;001-04;Belley;Belley
JORFTEXT000028621797;001-05;Bourg-en-Bresse-1;Bourg-en-Bresse
JORFTEXT000028621797;001-06;Bourg-en-Bresse-2;Bourg-en-Bresse
JORFTEXT000028621797;001-07;Ceyzériat;Ceyzériat
JORFTEXT000028621797;001-08;Châtillon-sur-Chalaronne;Châtillon-sur-Chalaronne
JORFTEXT000028621797;001-09;Gex;Gex

cquest

cquest

# exemple de reformattage automatique de données .xls en .csv
# ce script utilise curl, wget, grep, tail, head ainsi que in2csv, csvcut, csvstat provenant de csvkit
# script écrit par Christian quest - [email protected]
# explications à lire sur https://cquest.hackpad.com/Formats-ouverts-pour-donnes-ouvertes-scriptons-la-conversion-Jw1EVjsWgX3

# récupération des fichiers .xls
curl 'https://www.data.gouv.fr/fr/datasets/53bddb28a3a7292f66115a14/' -s | grep href.*xls -o | grep http.*xls -o | wget -i - --quiet

# extraction première ligne d'entête du fichier csv
for f in *.xls; do in2csv $f | tail -n +5 | head -n 1 | csvcut -C 1 >> temp; done; head -n 1 temp > domaines.csv; rm temp

cquest

curl 'https://www.data.gouv.fr/fr/datasets/donnees-d-execution-budgetaire-des-collectivites-territoriales-3/' -s | grep href.*xls -o | grep http.*xls -o | wget -i - --quiet
for f in *.xls; do in2csv $f | head -n 1 > $f.csv; done
for f in *.xls; do for s in `seq -w 01 19` 2A 2B `seq -w 21 95`; do in2csv --sheet "Departement_$s""_" $f | tail -n +2 >> $f.csv; done; done
for f in *.xls; do for s in 971 972 973 974 976; do in2csv --sheet "Departement_$s" $f | tail -n +2 >> $f.csv; done; done

cquest

[
  {
    "op": "core/text-transform",
    "description": "Text transform on cells in column SUP_ID using expression value.toNumber()",
    "engineConfig": {
      "facets": [],
      "mode": "row-based"
    },
    "columnName": "SUP_ID",
    "expression": "value.toNumber()",

cquest

# get votes from the map (curl)
# extract the lines containing values and reformat as csv (grep, sed)

echo "lat,lon,votes,location,country" > votes.csv
curl 'http://allourideas.org/globalopendataindex15/voter_map?type=votes' \
 | grep "data.setValue" votes.html \
 | sed 's/.*data.setValue([0-9]*, //;s/);//;s/, /,/' \
 | sed 'N;s/\n[1-3]//' | sed 'N;s/\n[1-3]//' \
 | sed 's/, \(..\)"$/",\1/;s/ ,"/,"/;s/^0,//' \
>> votes.csv

cquest

# récupération des données sur https://www.referidf.com/
mkdir kml -f
for p in {1..4}; do
  for m in "meuble" "non-meuble"; do
    for d in "inf1946" "1946-1970" "1971-1990" "sup1990"; do
      curl -q "https://www.referidf.com/kml/drihl_medianes_$p%5f$d%5f$m.kml?t=20150327" > kml/drihl_$p_$d_$m.kml
    done
  done
done