cquest

select co.tags->'ref:INSEE', count(*) as nb from planet_osm_polygon co join planet_osm_polygon epci on (st_overlaps(co.way,epci.way)) where co.admin_level='8' and co.tags ? 'ref:INSEE' and epci.boundary='local_authority' and epci.tags ? 'local_authority:FR' group by 1 order by 2 desc;

insee_com | nb 
----------+----
 39537    |  3
 14371    |  3
 60196    |  2
 61050    |  2
 61168    |  2
 61483    |  2

cquest

import sys
from bs4 import BeautifulSoup


carto = BeautifulSoup(open(sys.argv[1]),'xml')
total = 0

for style in carto.find_all("Style"):
  rules = style.find_all("Rule")
  total = total + len(rules)

cquest

#!/bin/bash
# usage: ./addok2xml.sh "39 quai andré citroën Paris"
# dépend de: httpie et jq (sudo apt install httpie jq)

http "http://api-adresse.data.gouv.fr/search/?limit=1&q=$1" | \
jq --raw-output '"<geo><latitude>\(.features[0].geometry.coordinates[1])</latitude><longitude>\(.features[0].geometry.coordinates[0])</longitude><score>\(.features[0].properties.score)</score><id>\(.features[0].properties.id)</id></geo>"'

cquest

import csv
import sys
import requests
import json

# Usage: python csv2geocode.py inputcsv inputcols geo_cols
# Ex: python csv2geocode.py example.csv numero,voie,codep,ville longitude,latitude,score,type,label,citycode

cols = sys.argv[2].split(',')
print(cols)

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

cquest

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

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

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