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 / 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 / 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 / gist:355f48120b579e6c15cd6c827e686291
Last active May 6, 2021 13:38
postgis-on-wsl
chmod a+x ~
apt update
apt install build-essential autoconf automake libtool postgresql-server-dev-all libxml2-dev libgeos-dev libproj-dev libxml2-utils xsltproc bison postgresql-10
service postgresql start
su postgres -c 'createuser root'
su postgres -c 'psql -d postgres -c "alter user root superuser;"'
git clone git@github.com:postgis/postgis.git
cd postgis
./autogen.sh
./configure --without-raster
@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
@Komzpa
Komzpa / osm_users_greedy_map.sql
Created September 14, 2019 20:06
create or replace procedure trim_osm_users_h3()
language plpgsql
as
$$
declare
cur_rec record;
counter integer;
total_rec integer;
last_seen timestamptz;
last_cluster timestamptz;
@Komzpa
Komzpa / gist:3270d910805aa8cd554e8ad767d584a5
Created June 22, 2019 19:11
-----------------------------------------------------------------
pg_upgrade run on Sat Jun 22 22:08:45 2019
-----------------------------------------------------------------
command: "/usr/lib/postgresql/11/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "/etc/postgresql/11/main" -o "-p 5432 -b -c listen_addresses='' -c unix_socket_permissions=0700 -c unix_socket_directories='/var/log/postgresql/pg_upgradecluster-11-12-main.LvO0'" start >> "pg_upgrade_server.log" 2>&1
waiting for server to start....2019-06-22 22:08:45.798 +03 [28771] LOG: listening on Unix socket "/var/log/postgresql/pg_upgradecluster-11-12-main.LvO0/.s.PGSQL.5432"
2019-06-22 22:08:45.952 +03 [28772] LOG: database system was shut down at 2019-06-22 22:08:44 +03
2019-06-22 22:08:45.959 +03 [28771] LOG: database system is ready to accept connections
done
server started
@Komzpa
Komzpa / count_xox_games.sql
Last active June 2, 2019 23:29
create or replace function is_done(g int[])
returns boolean
as
$$
select exists(
select
from (
select array [g[1], g[3], g[5], g[7], g[9]]::int[] as moves
union all
select array [g[2], g[4], g[6], g[8]]::int[] as moves
@Komzpa
Komzpa / http_get.sql
Created April 28, 2019 20:14
create or replace function http_get(url text)
returns text
as $$
import urllib.request
return urllib.request.urlopen(url).read().decode('utf-8')
$$
language plpython3u
parallel safe
cost 10000;
@Komzpa
Komzpa / gist:7f2390db76dab8f28578b06d2000b047
Created November 30, 2018 16:05
SELECT ST_Area(the_geom) As sqft, ST_Area(ST_Transform(the_geom,26986)) As sqm
FROM (SELECT
ST_GeomFromText('POLYGON((743238 2967416,743238 2967450,
743265 2967450,743265.625 2967416,743238 2967416))',2249) ) As foo(the_geom);
sqft | sqm
---------+------------------
928.625 | 86.2724304199219
19:03:21 [kom] > SELECT ST_Area(the_geom) As sqft, ST_Area(ST_Transform(the_geom,26986)) As sqm
[more] - > FROM (SELECT
@Komzpa
Komzpa / gist:c511677e6777468866addf97ae1dc69a
Created November 28, 2018 06:47
kom@nucat:~/proj/postgis/regress/00-regress-install/lib$ ldd *.so
postgis-3.so:
linux-vdso.so.1 (0x00007ffc989cc000)
libgeos_c.so.1 => /usr/lib/x86_64-linux-gnu/libgeos_c.so.1 (0x00007fb47e95e000)
libproj.so.13 => /usr/lib/x86_64-linux-gnu/libproj.so.13 (0x00007fb47e8e7000)
libjson-c.so.3 => /lib/x86_64-linux-gnu/libjson-c.so.3 (0x00007fb47e6dc000)
libxml2.so.2 => /usr/lib/x86_64-linux-gnu/libxml2.so.2 (0x00007fb47e534000)
libSFCGAL.so.1 => /usr/lib/x86_64-linux-gnu/libSFCGAL.so.1 (0x00007fb47dd00000)
libc.so.6 => /lib/x86_64-linux-gnu/libc.so.6 (0x00007fb47db16000)
libgeos-3.7.0.so => /usr/lib/x86_64-linux-gnu/libgeos-3.7.0.so (0x00007fb47d957000)