cquest

Ingredients

• a decent DEM
• gdal
• some computer

compute grayscale hillshade using gdaldem

gdaldem hillshade -compute_edges your_dem_file hillshade.tif

color-ramp to compute transparency (alpha channel)

In another bowl, prepare the following shade.ramp file:

cquest

SELECT N.nspname || '.' || C.relname AS "relation",
    CASE WHEN reltype = 0
        THEN pg_size_pretty(pg_total_relation_size(C.oid)) || ' (index)'
        ELSE pg_size_pretty(pg_total_relation_size(C.oid)) || ' (' ||  pg_size_pretty(pg_relation_size(C.oid)) || ' data)'
    END AS "size (data)",
    COALESCE(T.tablespace, I.tablespace, '') AS "tablespace"
FROM pg_class C
LEFT JOIN pg_namespace N ON  (N.oid = C.relnamespace)
LEFT JOIN pg_tables T ON (T.tablename = C.relname)
LEFT JOIN pg_indexes I ON (I.indexname = C.relname)

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

exiftool -n -g -json \
  -imagewidth \
  -imageheight \
  -composite:gpslatitude \
  -composite:gpslongitude \
  *jpg \
  | jq --compact-output --arg urlBase http://mysite.net/myphotos/ \
  '{
    "type": "FeatureCollection",
    "features": 

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