This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 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 | ========= |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 ; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#!/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 "*****************************************" |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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'), |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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" |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#!/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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<?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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 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 | |
) |
OlderNewer