MarHoff

-- Open These views with QGIS
--   You can label the ticks view with this expression : "CASE WHEN m % 5 = 0 THEN h END" to get hour numbers
--   Set categorized styles on typ and unit to set different styles for dials, and for the hands
-- Please tweet your best clock designs with #PostGISClock hashtag

create or replace view ticks as
select 
	m as id
	, case when m % 5 = 0 then 'h' else 'm' end as typ
	, m / 5 as h

MarHoff

SELECT actual, cheflieu, cdc, rang, reg, dep, com, ar, ct, modif, pole, 
       tncc, artmaj, ncc, artmin, nccenr, articlct, nccct
  FROM insee_cog.france2016 WHERE subst(dep,1,2) <> substr(pole,1,2);
  
 actual | cheflieu | cdc | rang | reg | dep | com | ar | ct | modif | pole  | tncc | artmaj |       ncc        | artmin |      nccenr      | articlct | nccct
--------+----------+-----+------+-----+-----+-----+----+----+-------+-------+------+--------+------------------+--------+------------------+----------+-------
 2      |          |     |      | 32  | 02  | 285 |    |    | 1     | 59545 | 1    |        | ESCAUFOURT       |        | Escaufourt       |          |
 3      |          |     |      | 24  | 28  | 131 |    |    | 1     | 91016 | 0    |        | DOMMERVILLE      |        | Dommerville      |          |
 2      |          |     |      | 27  | 39  | 012 |    |    | 1     | 25527 | 1    |        | ANTORPE          |        | Antorpe          |          |
 2      |          |     |      | 44  | 51  | 4

MarHoff

-- Functions to create and draw histograms with PostgreSQL.
-- psql> select * from show_histogram((select histogram(length(email), 0, 32, 6) FROM auth_user limit 100));
--  bucket |                range                | count |              bar               |             cumbar             | cumsum |         cumpct         
-- --------+-------------------------------------+-------+--------------------------------+--------------------------------+--------+------------------------
--       0 | [0,5.33333333333333)                |     1 |                                |                                |      1 | 0.00273224043715846995
--       1 | [5.33333333333333,10.6666666666667) |     5 | =                              |                                |      6 | 0.01639344262295081967
--       2 | [10.6666666666667,16)               |   149 | ============================== | =============                  |    155 | 0.42349726775956284153
--       3 | [16,21.3333333333333)               |   145 | =========

MarHoff

CREATE OR REPLACE FUNCTION ia_sigmoid(
    v_value numeric,
    v_turn numeric,
    v_slope numeric)
  RETURNS numeric AS
'SELECT  1./(1.+ exp(((-v_slope*v_value)+v_slope*v_turn))); '
LANGUAGE sql ;

MarHoff

#!/bin/bash
###############################################
# To use: 
# wget https://raw.github.com/gist/4411254
# chmod 777 github-ubuntu.sh
# ./github-ubuntu.sh
###############################################
echo "*****************************************"
echo " Step 1: Check your keys"
echo "*****************************************"

MarHoff

WITH tcolor as (SELECT * FROM (VALUES
(1,'#a6cee3'::text),
(2,'#1f78b4'),
(3,'#b2df8a'),
(4,'#33a02c'),
(5,'#fb9a99'),
(6,'#e31a1c'),
(7,'#fdbf6f'),
(8,'#ff7f00'),
(9,'#cab2d6'),

MarHoff

git clone git@github.com:theory/pgtap.git
cd pgtap/
git checkout v0.97.0
sudo apt-get install libtap-parser-sourcehandler-pgtap-perl

make
sudo -u postgres make installcheck
sudo -u postgres make install

psql -U postgres -c "CREATE EXTENSION pgtap"

MarHoff

#!/bin/bash

set -eux

sudo apt-get update

packages="postgresql-$PGVERSION postgresql-server-dev-$PGVERSION postgresql-common"

# bug: http://www.postgresql.org/message-id/20130508192711.GA9243@msgid.df7cb.de
sudo update-alternatives --remove-all postmaster.1.gz

MarHoff

<?xml version="1.0" ?><message:StructureSpecificData xmlns:ss="http://www.sdmx.org/resources/sdmxml/schemas/v2_1/data/structurespecific" xmlns:footer="http://www.sdmx.org/resources/sdmxml/schemas/v2_1/message/footer" xmlns:ns1="urn:sdmx:org.sdmx.infomodel.datastructure.Dataflow=FR1:TXCHO-DEP(1.0):ObsLevelDim:TIME_PERIOD" xmlns:message="http://www.sdmx.org/resources/sdmxml/schemas/v2_1/message" xmlns:common="http://www.sdmx.org/resources/sdmxml/schemas/v2_1/common" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><message:Header><message:ID>TXCHO-DEP_1488291220415</message:ID><message:Test>false</message:Test><message:Prepared>2017-02-28T14:13:40</message:Prepared><message:Sender id="FR1"><common:Name xml:lang="fr">Institut national de la statistique et des études économiques</common:Name></message:Sender><message:Structure structureID="FR1_TXCHO-DEP_1_0" namespace="urn:sdmx:org.sdmx.infomodel.datastructure.Dataflow=FR1:TXCHO-DEP(1.0):ObsLevelDim:TIME_PERIOD" dimensionAtObservation="TIME_PERIOD"><common:S

MarHoff

-- Given that "mytable" have at least two field "id" and "geom" (Polygon/Multipolygon)

WITH
temp AS (
        SELECT distinct (st_dump(st_intersection(a.geom, b.geom))).geom AS geom --Use of distinct to remove symetrical shapes and st_dump().geom to get standard types
           FROM mytable a JOIN mytable b 
           ON a.id <> b.id AND st_intersects(a.geom, b.geom) --Filter to only test distinct object that intersects
          WHERE st_geometrytype(st_intersection(a.geom, b.geom)) = 'ST_GeometryCollection'::text --Filter to only include collections because areas are returned as collections
        )