zimmicz

const express = require("express")
const app = express()
const { Pool } = require("pg")
const SphericalMercator = require("sphericalmercator")
const pool = new Pool({
    host: "localhost",
    port: 15432,
    user: "postgres",
    database: "postgres"
})

zimmicz

--
-- PostgreSQL database dump
--

-- Dumped from database version 9.5.4
-- Dumped by pg_dump version 9.5.4
DROP SCHEMA IF EXISTS test;
CREATE SCHEMA test;
SET statement_timeout = 0;
SET lock_timeout = 0;

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;

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

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

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

SELECT
    a.id,
    b.*
FROM (
    VALUES(
        1,
        ST_SetSRID(
            ST_GeomFromText(
                'POLYGON((0 0, -1 0, -1 -1, 0 -1, 0 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

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

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