Dennis Bauszus dbauszus-glx

dbauszus-glx / cluster_1.sql

Created April 20, 2018 17:06

Cluster comparisson

	SELECT
	dbscan_cid,
	st_union(geom) geom
	FROM (
	SELECT
	ST_ClusterDBSCAN(geom, 0.23, 1) OVER () dbscan_cid,
	geom
	FROM gb_retailpoint
	) kmeans GROUP BY dbscan_cid;

dbauszus-glx / cluster_2.sql

Created April 20, 2018 17:21

KMeans DBScan nested group by to json.

	SELECT
	SUM(count)::integer count,
	JSON_Agg(JSON_Build_Object(cat, count)) cat,
	ST_AsGeoJson(ST_PointOnSurface(ST_Union(geom))) geomj
	FROM (
	SELECT
	COUNT(cat) count,
	ST_Union(geom) geom,
	cat,
	kmeans_cid,

dbauszus-glx / mvt_creation.sql

Last active December 25, 2019 19:12

Create an MVT with properties from PostGIS and store in MVT cache table.

	INSERT INTO dev.natural_earth_countries__mvts (z, x, y, mvt, tile)
	SELECT
	5,
	10,
	13,
	ST_AsMVT(tile, 'COUNTRIES', 4096, 'geom') mvt,
	ST_MakeEnvelope(
	${-m + (x * r)},
	${ m - (y * r)},
	${-m + (x * r) + r},

dbauszus-glx / mvt_tile_cache_schema.sql

Created November 30, 2018 12:26

	create table if not exists natural_earth_countries__mvts
	(
	z integer not null,
	x integer not null,
	y integer not null,
	mvt bytea,
	tile geometry(Polygon,3857),
	constraint dev_natural_earth_countries__mvts_z_x_y_pk
	primary key (z, x, y)
	)

dbauszus-glx / delete_cached_mvt.js

Last active November 30, 2018 15:05

	module.exports = async (layer, table, id) => {

	const q = `
	DELETE FROM ${table}__mvts
	WHERE
	ST_Intersects(
	tile,
	(SELECT ${layer.geom_3857} FROM ${table} WHERE ${layer.qID} = $1)
	);`;

dbauszus-glx / xyz_root.js

Last active April 17, 2019 15:50

	// Set jsrender module for server-side templates.
	const jsr = require('jsrender');

	module.exports = {route, view};

	function route(fastify) {

	fastify.route({
	method: 'GET',
	url: '/',

dbauszus-glx / xyz_route.js

Last active April 17, 2019 15:53

	const env = require('../mod/env');

	const sql_filter = require('../mod/pg/sql_filter');

	const sql_fields = require('../mod/pg/sql_fields');

	fastify.route({
	method: 'GET',
	url: '/api/layer/table',
	preValidation: fastify.auth([

dbauszus-glx / authToken.js

Last active April 17, 2019 15:55

	const env = require('../mod/env');

	module.exports = fastify => (req, res, next, access = {}) => {

	// Delete query token param if string 'null' instead of null.
	if (req.query.token === 'null') {
	delete req.query.token;
	}

	// Public access without token.

dbauszus-glx / fastify_server.js

Created April 12, 2019 10:28

	const fastify = require('fastify')({
	trustProxy: true,
	logger: {
	level: process.env.LOG_LEVEL \|\| 'error',
	prettifier: require('pino-pretty'),
	prettyPrint: {
	errorProps: 'hint, detail',
	levelFirst: true,
	crlf: true
	}

dbauszus-glx / OL.selectFeatureById.js

Created June 15, 2019 15:35

Select a feature at pixel from XYZ endpoint with OpenLayers

	let currentFeature;

	map.on('click', select);

	function select(e) {

	sourceVector.clear();

	map.removeInteraction(drawInteraction);