cquest

            name            | ref |  nb  
----------------------------+-----+------
 Aquitaine                  |     | 6933
 Midi-Pyrénées              |     | 3857
 Centre-Val de Loire        |     | 2953
 Provence-Alpes-Côte d'Azur |     | 2917
 Rhône-Alpes                |     | 2199
 Basse-Normandie            |     | 1937
 Pays de la Loire           |     | 1666
 Haute-Normandie            |     | 1436

cquest

#!/bin/bash
# exemple: ./bigaddok.sh in.csv out.csv "columns=numero columns=voie"
# needs httpie (pip install httpie)

lines=`wc -l $1| grep ^[0-9]* -o`
maxlines=10000
export chunks=`echo "$lines/$maxlines"|bc`
api="http://api-adresse.data.gouv.fr/search/csv/"

for chunk in `seq 0 $chunks`; do

cquest

cquest

#!/bin/bash
# script to opt-out data from adresse-francaise.com
# usage: ./adresse-francaise-optout.sh <insee>
insee=$1
fantoir=`curl -s "http://adresse-francaise.com/search.php?c=$insee" | egrep '<a href="street.php.i=[0-9][0-9AB][0-9]{3}[0-9A-Z][0-9]{3}' -o | sed 's/^.*=//'`
for f in $fantoir; do
echo $f
tel=`curl "http://adresse-francaise.com/street.php?i=$f" -s | egrep "<td>[0-9]{10}" -o | sed 's/<td>//'`
for t in $tel; do
curl 'http://adresse-francaise.com/btob.php' -H 'Host: adresse-francaise.com' --compressed -H 'Referer: http://adresse-francaise.com/contact.php' -H 'Connection: keep-alive' --data "phone=$t" -s > /dev/null

cquest

cquest

/* changements heure hiver > été depuis 1981 le dernier dimanche de mars à 1h UTC soit 2h (hiver) / 3h (été) en France */
select april::timestamptz
  - interval '1 day' * case when extract(dow from april)=0 then 7 else extract(dow from april) end
from (select generate_series('1981-04-01 01:00:00+00','2050-04-01 01:00:00+00', interval '1 year') as april) as m;

/* changements heure été > hiver de 1981 à 1995 le dernier dimanche de septambre à 1h UTC soit 2h (hiver) / 3h (été) en France */
select sept::timestamptz
  - interval '1 day' * case when extract(dow from sept)=0 then 7 else extract(dow from sept) end
from (select generate_series('1981-10-01 01:00:00+00','1995-10-01 01:00:00+00', interval '1 year') as sept) as m;

cquest

select count(*), sum(st_length(st_transform(r.way,4326)::geography)), r.service from planet_osm_line r join planet_osm_polygon p on (st_intersects(r.way,p.way)) where r.railway='rail' and p.osm_id=-2202162 group by 3;

 count |       sum        |   service    
-------+------------------+--------------
 54495 |  52672699.848765 | 
     6 | 863.600613716276 | industrial
    18 | 31704.5442901028 | main
   657 | 63642.8733992808 | crossover
 14463 |  5710487.2771534 | yard
 10976 | 4281735.35639498 | spur

cquest

-- SELECT * FROM db_size;  -- list tables/index with the disk space occupied
-- SELECT * FROM db_bloat; -- compute lost space in data/index
-- SELECT * FROM db_stats; -- show size of data/index + number of reads (data) or index scans
-- SELECT * FROM db_index_list ; -- show original CREATE INDEX statement

CREATE OR REPLACE VIEW db_bloat AS
SELECT current_database() AS current_database,
    sml.schemaname,
    sml.tablename,
    round(

cquest

cquest

#!/bin/bash
# This bash script copies pictures from Mapillary folders stored on an iPhone
# then update the pictures EXIF data to get the GPS and timestamp
# written by Christian Quest - August 2016
# requires jq and exiftool, if missing: apt install jq exiftool
# tested on Ubuntu 16.04


# copy Mapillary internal json data (contains GPS info, and more)
rsync /run/user/1000/gvfs/afc*/com.mapillary.app/cameras/internal/ internal -a