Skip to content

Instantly share code, notes, and snippets.

Avatar

Yuri Astrakhan nyurik

View GitHub Profile
@nyurik
nyurik / optimized-mbtiles.sql
Created Jun 10, 2022
Some ideas on optimizing mbtiles file storage size with a single 32-bit index instead of z/x/y
View optimized-mbtiles.sql
create table map
(
tile_index INTEGER,
tmp_zoom INTEGER GENERATED ALWAYS AS ((tile_index & 0xFC000000) >> 26) VIRTUAL,
tile_column INTEGER GENERATED ALWAYS AS (CASE
WHEN tmp_zoom <= 13 THEN (tile_index & 0x3FFE000) >> 13
ELSE (tile_index & 0x3FFF8000) >> 15 END) VIRTUAL,
tile_row INTEGER GENERATED ALWAYS AS (CASE
WHEN tmp_zoom <= 13 THEN tile_index & 0x1FFF
ELSE tile_index & 0x7FFF END) VIRTUAL,
@nyurik
nyurik / types.rs
Last active Feb 21, 2022
Multidimensional Geo-types with separate Metadata
View types.rs
use num_traits::{Float, Num, NumCast};
use std::fmt::Debug;
trait CoordinateType: Default + Num + Copy + NumCast + PartialOrd + Debug {}
impl<T: Default + Num + Copy + NumCast + PartialOrd + Debug> CoordinateType for T {}
trait CoordNum: CoordinateType {}
impl<T: CoordinateType + Debug> CoordNum for T {}
trait CoordFloat: CoordNum + Float {}
@nyurik
nyurik / types.rs
Last active Feb 21, 2022
Multidimensional Geo-types
View types.rs
use num_traits::{Float, Num, NumCast};
use std::fmt::Debug;
trait CoordinateType: Default + Num + Copy + NumCast + PartialOrd + Debug {}
impl<T: Default + Num + Copy + NumCast + PartialOrd + Debug> CoordinateType for T {}
trait CoordNum: CoordinateType {}
impl<T: CoordinateType + Debug> CoordNum for T {}
trait CoordFloat: CoordNum + Float {}
@nyurik
nyurik / denormalize_osm_data.md
Last active Feb 15, 2022
Convenient OSM data
View denormalize_osm_data.md

OpenStreetMap data is heavily normalized, making it very hard to process. Modeled on a relational database, it seems to have missed the second part of the "Normalize until it hurts; denormalize until it works" proverb.

Each node has an ID, and every way and relation uses an ID to reference that node. This means that every data consumer must keep an enrmous cache of 8 billion node IDs and corresponding lat,lng pairs while processing input data. In most cases, node ID gets discarded right after parsing.

I would like to propose a new easy to process data strucutre, for both bulk downloads and streaming update use cases.

Target audience

  • YES -- Data consumers who transform OSM data into something else, i.e. tiles, shapes, analytical reports, etc.
@nyurik
nyurik / is_tf_in_pr.py
Created Apr 28, 2021
A script to detect when Terraform projects or depended modules are part of a GIT pull request change
View is_tf_in_pr.py
#!/usr/bin/env python3
# A script to detect when Terraform projects or depended modules are part of a GIT pull request change
#
# Usage: python3 pr_tf_changes.py <branch> <dir>...
#
# <branch> GIT branch to compare using git diff branch... shell call
# <dir> One or more directories to monitor, including all sub-dirs, relative to repo's root
#
# Set DEBUG env var to see additional debugging information
# If match is found, exitcode is 0, otherwise 1
@nyurik
nyurik / to-logical.py
Last active Jul 18, 2022
Script to convert SCSS files from physical to logical values for RTL and vertical languages
View to-logical.py
#
# This script converts margins, padding, and borders to logical values,
# allowing RTL and vertical languages to show correctly.
# Supports both *.css and *.scss files.
#
# Some renames are not yet implemented widely, and may require CSS plugin
# https://github.com/csstools/postcss-logical
# They have been commented out for now, but feel free to try them out.
#
# Full spec: https://drafts.csswg.org/css-logical/
@nyurik
nyurik / OptimizeLabelGrid.sql
Last active May 19, 2020
Optimizing LabelGrid - the result is worse than before??
View OptimizeLabelGrid.sql
------- Testing:
-- git clone https://github.com/openmaptiles/openmaptiles
-- git checkout upgrade-v5-pg12
-- place this file in the openmaptiles/ dir as "test-func.sql"
-- use make start-db to create a new database (in docker)
-- use make bash to start tools (another docker)
-- test with this command. The test call is taken from the openmaptiles-tools/tests/sql/LabelGrid.sql test. Note the "volatile" keyword - without it the query planner will optimize away multiple calls with the same value.
-- profile-pg-func --file test-func.sql "LabelGrid_pgsql(ST_GeomFromText('POINT(100 -100)',900913), 64*9.5546285343)" "LabelGrid_sql(ST_GeomFromText('POINT(100 -100)',900913), 64*9.5546285343)"
-- The results are not that great:
@nyurik
nyurik / getmvt.sql
Created Oct 18, 2019
OpenMapTiles getmvt(z,x,y) function with empty tile detection
View getmvt.sql
CREATE OR REPLACE FUNCTION getmvt(zoom integer, x integer, y integer)
RETURNS bytea AS $$
SELECT STRING_AGG(mvtl, '') AS mvt FROM (
SELECT IsEmpty, count(*) OVER () AS LayerCount, mvtl FROM (
SELECT CASE zoom <= 8 WHEN TRUE THEN FALSE ELSE ST_WITHIN(ST_GeomFromText('POLYGON(
(0 4096,0 0,4096 0,4096 4096,0 4096))', 3857), ST_COLLECT(mvtgeometry)) END AS IsEmpty, ST_AsMVT(tile, 'water', 4096, 'mvtgeometry') as mvtl FROM (SELECT ST_AsMVTGeom(geometry, TileBBox(zoom, x, y), 4096, 4, true) AS mvtgeometry, class, intermittent FROM layer_water(TileBBox(zoom, x, y), zoom) WHERE ST_AsMVTGeom(geometry, TileBBox(zoom, x, y), 4096, 4, true) IS NOT NULL) AS tile HAVING COUNT(*) > 0
UNION ALL
SELECT FALSE AS IsEmpty, ST_AsMVT(tile, 'waterway', 4096, 'mvtgeometry') as mvtl FROM (SELECT ST_AsMVTGeom(geometry, TileBBox(zoom, x, y), 4096, 4, true) AS mvtgeometry, name, name_en, name_de, NULLIF(tags->'name:ar', '') AS "name:ar", NULLIF(tags->'name:az', '') AS "name:az", NULLIF(tags->'name:be', '') AS "name:be", NULLIF(t
View composer.local.json
{
"extra": {
"merge-plugin": {
"include": [
"extensions/Wikibase/composer.json",
"../settings.d/composer/*.json"
]
}
}
}
View OSM wiki tag cleanup.py
# -*- coding: utf-8 -*-
from __future__ import unicode_literals
fixes['osmlinks'] = {
'regex': True,
'nocase': True,
'msg': {
# '_default': 'Param cleanup, remove obsolete lang parameters - template detects it automatically',
'_default': 'Tag template cleanup - format combinations, use proper {{Tag}} template with params, remove kl= and vl= (handled automatically)',
},