Skip to content

Instantly share code, notes, and snippets.

View Komzpa's full-sized avatar

Darafei Praliaskouski Komzpa

151 followers · 12 following
View GitHub Profile
@Komzpa
Komzpa / elevation_by_contours.sql
Created December 2, 2017 12:35
with params as (
select 'SRID=4326;POINT(0 0)' :: gemetry as geom
)
select
a.altitude,
a.distance,
b.altitude, -- may be null, then do not interpolate
b.distance -- may be null, then do not interpolate
from (-- closest linestring
select
@Komzpa
Komzpa / progressbar.sql
Created May 19, 2017 15:18
drop function if exists progressbar( double precision, double precision, timestamptz );
create or replace function progressbar(
done_count float,
total_count float,
start_time timestamptz default now()
)
returns setof void
as $$
declare
display_progressbar boolean;
@Komzpa
Komzpa / gevel_postgis.md
Last active May 7, 2021 00:35
Visualize PostGIS index using Gevel in Postgres 9.6

Visualize PostGIS index using Gevel in Postgres 9.6:

git clone git://sigaev.ru/gevel
cd gevel
git checkout bd8b8b031a8049a6e7c18c00946bfbd99d75d27f
USE_PGXS=1 make
sudo USE_PGXS=1 make install
psql -f /usr/share/postgresql/9.6/contrib/gevel.sql
@Komzpa
Komzpa / shortest_path_for_mapping_buildings.sql
Created January 2, 2017 16:21
drop table if exists building_travel_curve;
create table building_travel_curve as (
select
ST_MakeLine(ST_Centroid(p.way)
order by tsp.seq) as geom,
1 as id
from pgr_tsp(
$$
select
osm_id :: int4 as id,
@Komzpa
Komzpa / setting up osm apidb
Last active January 24, 2023 08:34
given:
- Postgres database gis, user gis, accepting trust connections on localhost
# grab software
sudo apt install osmosis curl wget unzip
# import schema
curl https://raw.githubusercontent.com/openstreetmap/openstreetmap-website/master/db/structure.sql | psql
# get latest osmosis wit non-broken pbf support
wget http://bretth.dev.openstreetmap.org/osmosis-build/osmosis-latest.zip
unzip osmosis-latest.zip
@Komzpa
Komzpa / gist:f5350a5ef34570447eb01d08e9055768
Created December 14, 2016 20:59
gis=> drop type if exists apple;
DROP TYPE
gis=> create type apple as (amount numeric(1,0));
CREATE TYPE
gis=> select '(1)'::apple;
apple
-------
(1)
(1 row)
@Komzpa
Komzpa / aaa.sql
Created October 19, 2016 11:32
compare case and nullif
create or replace function aaa(aaa integer)
returns text as $$
begin
raise notice 'passed1';
return aaa :: text || '😼';
end
$$ language 'plpgsql' strict;
select case when aaa(1) = '1'
then null
@Komzpa
Komzpa / gist:8505274b2e9ba8cacf96587c6901212b
Created October 7, 2016 20:07
gis=> create or replace function 😼ify ( 😼 integer )
gis-> returns text as $$
gis$> select 😼::text||'😼'
gis$> $$ language 'sql' strict;
CREATE FUNCTION
gis=>
gis=> create operator 😼(
gis(> leftarg = integer,
gis(> procedure = 😼ify
gis(> );
@Komzpa
Komzpa / gist:2d3873f88c439d7cbb4de8efa4e058db
Created October 7, 2016 17:42
create or replace function _final_median(anyarray)
returns float8 as $$
with q as
(
select val
from unnest($1) val
where VAL is not null
order by 1
),
cnt as
@Komzpa
Komzpa / upgrade-postgres-9.4-to-9.5-to-9.6-to-10.md
Last active January 15, 2021 18:04 — forked from dideler/upgrade-postgres-9.3-to-9.4.md
Upgrading PostgreSQL from 9.4 to 9.5 to 9.6 to 10 when upgrading Ubuntu 14.10 to 16.04

TL;DR

9.4 -> 9.5:

sudo pg_dropcluster 9.5 main --stop
sudo service postgresql stop
sudo pg_upgradecluster -m upgrade -k 9.4 main
sudo su postgres -c "/usr/lib/postgresql/9.5/bin/vacuumdb --all --analyze-in-stages"
sudo pg_dropcluster 9.4 main