zimmicz’s gists

zimmicz / data.js

Created November 14, 2015 06:29

This file has been truncated, but you can view the full file.

var data = {"type":"FeatureCollection","features":[{"type":"Feature","geometry":{"type":"Point","coordinates":[15.1620997,50.5929858]},"properties":{"id":1,"title":"Restaurace v Hotelu Karel IV","location_type":4,"description":"speciality italské, ale i české kuchyně. Restaurace\r\ns odděleným salonkem má celkovou kapacitu 70 míst. Pro osoby s tělesným postižením je zajištěn bezbariérový přístup\r\na sociální zařízení.\r\nPondělí až čtvrtek 10:30 - 22:00\r\nPátek 10:30 - 23:00\r\nSobota 11:30-23:00\r\nNeděle 11:30 - 21:00 od listopadu do konce dubna ZAVŘENO","author_name":"Standa","attr1":1,"attr2":"yes","attr3":"no"}},{"type":"Feature","geometry":{"type":"Point","coordinates":[14.4503582,50.1084557]},"properties":{"id":86,"title":"Parkoviště","location_type":13,"description":"4x pro ZTP","author_name":"Mili","attr1":1,"attr2":"no","attr3":"yes"}},{"type":"Feature","geometry":{"type":"Point","coordinates":[15.3607817,50.4353005]},"properties":{"id":123,"title":"Parkoviště","location_type":13,"description":"2 

zimmicz / 01_ogr2ogr.sh

Created September 21, 2016 16:38

OSM2PostGIS

	#!/bin/bash

	wget http://download.geofabrik.de/europe/czech-republic-latest-free.shp.zip
	unzip czech-republic-latest-free.shp.zip -d osm

	cd osm
	rename 's/gis\.//g' .
	rename 's/osm_//g' .
	rename 's/_free_1//g' .

zimmicz / 02_ogrfdw.sh

Created September 21, 2016 17:10

	#!/bin/bash

	sudo apt-get install libgdal-dev
	git clone https://github.com/pramsey/pgsql-ogr-fdw.git && cd pgsql-ogr-fdw
	make
	make install

	psql -d $PGDATABASE -X -c "CREATE EXTENSION ogr_fdw"

	psql -d $PGDATABASE -c "$(./ogr_fdw_info -s /tmp/buildings_a.shp -l buildings_a)"

zimmicz / 03_st_difference.sql

Created September 21, 2016 17:46

	DROP SCHEMA IF EXISTS test;
	CREATE SCHEMA test;
	--
	-- PostgreSQL database dump
	--

	-- Dumped from database version 9.5.4
	-- Dumped by pg_dump version 9.5.4

	SET statement_timeout = 0;

zimmicz / 03_st_disjoint.sql

Created September 21, 2016 17:53

	--
	-- PostgreSQL database dump
	--

	-- Dumped from database version 9.5.4
	-- Dumped by pg_dump version 9.5.4

	SET statement_timeout = 0;
	SET lock_timeout = 0;
	SET client_encoding = 'UTF8';

zimmicz / 04_random_points.sql

Created September 21, 2016 18:09

	SELECT
	a.id,
	b.*
	FROM (
	VALUES(
	1,
	ST_SetSRID(
	ST_GeomFromText(
	'POLYGON((0 0, -1 0, -1 -1, 0 -1, 0 0))'
	),

zimmicz / 05_polygon_from_centroid.sql

Created September 21, 2016 18:18

	CREATE OR REPLACE FUNCTION ST_PolygonFromCentroid(centroid geometry, xsize numeric, ysize numeric)
	RETURNS geometry
	AS $ST_PolygonFromCentroid$
	SELECT ST_MakeEnvelope(
	ST_X(ST_Translate($1, -$2, -$3)),
	ST_Y(ST_Translate($1, -$2, -$3)),
	ST_X(ST_Translate($1, $2, $3)),
	ST_Y(ST_Translate($1, $2, $3))
	);
	$ST_PolygonFromCentroid$

zimmicz / 06_grid.sql

Created September 21, 2016 18:28

	CREATE OR REPLACE FUNCTION cm_grid(
	blx float8, -- bottom left x coordinate
	bly float8, -- bottom left y coordinate
	trx float8, -- top right x coordinate
	try float8, -- top right y coordinate
	xsize float8, -- cell width
	ysize float8, -- cell height
	srid integer DEFAULT 5514,
	OUT col varchar,
	OUT "row" varchar,

zimmicz / 07_vlm.sql

Created September 22, 2016 05:13

	SET standard_conforming_strings = OFF;
	DROP TABLE "public"."vlm" CASCADE;
	DELETE FROM geometry_columns WHERE f_table_name = 'vlm' AND f_table_schema = 'public';
	BEGIN;
	CREATE TABLE "public"."vlm" ( OGC_FID SERIAL, CONSTRAINT "vlm_pk" PRIMARY KEY (OGC_FID) );
	SELECT AddGeometryColumn('public','vlm','wkb_geometry',-1,'POLYGON',2);
	CREATE INDEX "vlm_wkb_geometry_geom_idx" ON "public"."vlm" USING GIST ("wkb_geometry");
	ALTER TABLE "public"."vlm" ADD COLUMN "id" NUMERIC(10,0);
	COPY "public"."vlm" ("wkb_geometry", "id") FROM STDIN;
	0103000000010000000500000048357C101DA220C14D71B8B5DA2C31C17C68AF4309A220C1241529B3C82C31C1F77C5D0BDFA120C180A4EB68D42C31C19AED9A95EDA120C1E70A52CFE72C31C148357C101DA220C14D71B8B5DA2C31C1 993

zimmicz / 07_zabor.sql

Created September 22, 2016 05:14

	SET standard_conforming_strings = OFF;
	DROP TABLE "test"."zab" CASCADE;
	DELETE FROM geometry_columns WHERE f_table_name = 'zab' AND f_table_schema = 'test';
	BEGIN;
	CREATE TABLE "test"."zab" ( OGC_FID SERIAL, CONSTRAINT "zab_pk" PRIMARY KEY (OGC_FID) );
	SELECT AddGeometryColumn('test','zab','wkb_geometry',-1,'POLYGON',2);
	CREATE INDEX "zab_wkb_geometry_geom_idx" ON "test"."zab" USING GIST ("wkb_geometry");
	ALTER TABLE "test"."zab" ADD COLUMN "id" NUMERIC(10,0);
	ALTER TABLE "test"."zab" ADD COLUMN "entity" NUMERIC(10,0);
	COPY "test"."zab" ("wkb_geometry", "id", "entity") FROM STDIN;

Michal Zimmermann zimmicz