sfkeller

name	postgresql-best-practices
description	Comprehensive PostgreSQL best practices for database design, schema management, data types, indexing, query optimization, security, and maintenance. Use when working with PostgreSQL databases for: (1) Designing schemas and tables, (2) Choosing appropriate data types, (3) Creating and optimizing indexes, (4) Writing efficient SQL queries, (5) Implementing security and permissions, (6) Setting up backups and maintenance, (7) PostgreSQL Extensions. See PostgreSQL version at bottom. Written according to the Skill Creator guide. Contributors: Stefan Keller
license	Public Domain

PostgreSQL Best Practices

Best practices for PostgreSQL, covering schema design, data types, indexing, queries, security, and maintenance.

sfkeller

"""
QGIS Dynamic Filtering Script

Usage:
0. Ensure your vector dataset contains a ranking field, then load it into QGIS.
1. Adapt 'LAYER_NAME' and 'SQL_FILTER_TEMPLATE' in the 'Constants' section below.
2. Open the Python console in QGIS, then open and execute this script.
3. To disable filtering, copy and paste the following into the Python console:
   
   disable_dynamic_filter()

sfkeller

sfkeller

create extension if not exists ltree;
create table tree( id serial primary key, letter char, path ltree );
insert into tree (letter, path) values ('A', 'A'), ('B', 'A.B'), ('C', 'A.C'), ('D', 'A.C.D'), ('E', 'A.C.E'), ('F', 'A.C.F'), ('G', 'A.B.G');

with recursive tree_hierarchy as (
    select 
	    id, letter, path, 1 as level, letter::text as full_path
    from tree
    where nlevel(path) = 1 -- start from the root node(s)
    union all -- union

sfkeller

sfkeller

(* Overpass QL EBNF Grammar *)

(* Query *)
query                  ::= ( global_option )* ( statement )+ ( output_statement )*

(* Global Options *)
global_option          ::= '[' option ( ';' option )* ']'
option                 ::= 'out:' format
                         | 'timeout:' number
                         | 'maxsize:' number

sfkeller

CustID,Lastname,Firstname,Date_Birth,Nat,Gender,Kanton,Street,ZipCd,Place,Domicile_Country,Phone,Latitude,Longitude
101,Hauser,Pascal,20.05.1973,CH,M,ZH,Röschibachstr. 77,8037,Zürich,CH,044 530 81 97,47.383364,8.529977
102,Ragginger,Jean-Pierre,29.09.1983,CH,M,ZH,Saatlenzelg 24,8050,Zürich,CH,044 290 10 52,47.358601,8.531804
103,Faist,Jenny,08.10.1969,CH,F,ZH,Köschenrütistr. 69,8052,Zürich,CH,044 882 63 32,47.378758,8.527037
104,Silberer,Lucas,24.06.1977,CH,M,AG,Oberbodenstr. 10,5415,Nussbaumen AG   ,CH,056 126 14 33,47.34379,8.278959
105,Fillinger,Claude,21.07.1975,CH,M,ZH,Rieterstr. 93,8002,Zürich,CH,044 623 40 22,47.371534,8.521665
106,Baillie,Marianne,11.08.1980,F,F,TG,Zielweg 5,8580,Amriswil,CH,071 125 36 56,46.248447,6.120923
107,Isler,Ruth,21.11.1979,CH,F,ZH,Weihermattstrasse 48,8902,Urdorf,CH,044 141 97 32,47.585746,7.580571
108,Vlassidis,Stamatis,22.12.1970,GR,M,ZH,Brunnacherstr. 34,8174,Stadel b. Niederglatt,CH,044 790 05 07,47.386621,8.056205
109,Ambühler,Urs,29.07.1977,CH,M,ZH,Gerechtigkeitsgasse 

sfkeller

var temperatureDataset = ee.Image('NASA/ASTER_GED/AG100_003'); // https://developers.google.com/earth-engine/datasets/catalog/NASA_ASTER_GED_AG100_003

var nightLightsDataset = ee.ImageCollection('NOAA/VIIRS/DNB/MONTHLY_V1/VCMSLCFG') // https://developers.google.com/earth-engine/datasets/catalog/NOAA_VIIRS_DNB_MONTHLY_V1_VCMSLCFG?hl=en
  .filterDate('2021-01-01')
  .first();
  
var temperature = temperatureDataset.select('temperature').divide(100); // dataset is in 1/100 kelvin
var temperatureAverage = temperature.reduceNeighborhood(
  ee.Reducer.mean(),                  // calculate the mean
  ee.Kernel.square(2000, 'meters'),   // of a square with 'radius' of 2000 m (which is 4000 * 4000 m)

sfkeller

Infotafel

Willkommen am OST-Campus Rapperswil-Jona!

HINWEIS: Der virtuelle Empfang der Konferenz FOSSGIS 2021 ist im Gebäude 1!

Es gibt auf dem eigentlichen Campus Rapperswil der Fachhochschule Ostschweiz (OST) sieben grosse Gebäude, nummeriert mit 1,2,3,4,5,6,8 (also ohne die 7). Das Gebäude 1 ist das grösste. Im Gebäude 4 ist die Mensa und die Aula.

sfkeller

--
-- FUNCTION hstore_match_key_prefix(...)
-- 
--drop function hstore_match_key_prefix(hstore, text, text);
create or replace function hstore_match_key_prefix(tags hstore, key_prefix text, value_value text default 'yes')
returns text[] 
as $$ 
select array_agg(substring(key, key_prefix||':#"%#"', '#')) 
from (select (each(tags)).key as key, (each(tags)).value as value) as "alias"
where key like key_prefix||':%' and value = value_value