zimmicz

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

#!/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

#!/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

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

--
-- 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

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

zimmicz

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

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

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

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;