Skip to content

Instantly share code, notes, and snippets.

View pramsey's full-sized avatar

Paul Ramsey pramsey

View GitHub Profile
@pramsey
pramsey / curve-postgis.md
Last active February 21, 2024 00:25
PostGIS Curve Decomposition Functions

Current State of CompoundCurve

First question, is a CompoundCurve a unitary geometry, like a LineString, or is it a collection of geometries. Internally in PostGIS it is structured identically to a collection, and that makes some handling easier, because we just delegate calculations to the collection functions.

WITH f(geom) AS (
SELECT 
  'COMPOUNDCURVE(
    LINESTRING(2 2, 2.5 2.5),
    CIRCULARSTRING(2.5 2.5, 4.5 2.5, 3.5 3.5),
@pramsey
pramsey / index.html
Last active October 7, 2022 17:08
Moving Objects
<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>Moving Objects</title>
<!-- CSS/JS for OpenLayers map -->
<script src="https://cdn.jsdelivr.net/npm/ol@v7.1.0/dist/ol.js"></script>
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/ol@v7.1.0/ol.css" type="text/css" />
// Try as I might, I cannot get the location of the dots to update.
// The first time a payload is received, I get a point, and thereafter,
// the original point will not move (if I send in the same id) nor will a
// a second point be added (if I send in new ids).
// I just want to manipulate the data, and see the map view of the data
// change.
var objectServer = "ws://localhost:7700/objects";
var objectSource = new ol.source.Vector({
wrapX: false
@pramsey
pramsey / index.html
Last active September 29, 2022 23:44
Geonames Heat Map
<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>Geonames Heat Map</title>
<link rel="stylesheet" href="https://code.jquery.com/ui/1.12.1/themes/base/jquery-ui.css">
<link rel="stylesheet" href="https://jqueryui.com/resources/demos/style.css">
<link rel="stylesheet" href="https://unpkg.com/leaflet@1.5.1/dist/leaflet.css"
@pramsey
pramsey / pgsql-extension-upgrades.md
Last active February 10, 2022 22:17
PostgreSQL Extension Upgrades

The current PostgreSQL handling of extension upgrads is "very clever". It allows extension authors to define a set of extension files of the the form extensionName--versionA--versionB.sql and the extension mechanism will figure out what extension files need to be applied in order to service an update command like:

ALTER EXTENSION extensionName UPDATE TO 'versionB';

The clever bit is that if your extension is currently at versionA and you want to update to versionC, the extension framework will figure out that it can get there by first applying extensionName--versionA--versionB.sql and then applying extensionName--versionB--versionC.sql. So, very magical.

However, for large projects the overhead of managing the version-level bulk install script and also tracking all changes in order to generate the incremental scripts, and keeping them all in sync was rather high. The PostGIS project already had mechanisms to build up the install SQL file, and built that file so it could be applied directly

@pramsey
pramsey / postgis_pgaudit.sql
Created January 31, 2022 23:21
PostGIS + PgAudit?
DO $$
DECLARE
pgalog text;
BEGIN
SHOW pgaudit.log INTO pgalog;
IF lower(pgalog) != 'none'
THEN
RAISE DEBUG 'pgaudit.log is currently ''%''', pgalog;
EXECUTE Format('SET pgaudit.log_save TO %s', pgalog);
SET pgaudit.log = 'none';
@pramsey
pramsey / vrt2pgsql.py
Created July 9, 2021 22:56
OpenTopography SRTM VRT Parser
import sys
import xml.etree.ElementTree as ET
import argparse
######################################################################
parser = argparse.ArgumentParser()
parser.add_argument('-f', '--file',
required=True,
dest='file',
<html>
<head>
<style>
html {
font-family: sans-serif;
}
.geomA {
marker-end: url(#vertexA); marker-mid: url(#vertexA); fill:#bbbbff; fill-opacity:0.5; stroke:#0000ff; stroke-width:1; stroke-opacity:1; stroke-miterlimit:4; stroke-linejoin:miter; stroke-linecap:square;
}
/*
cc projtimer.c -I/usr/local/include -L/usr/local/lib -lproj -o projtimer
./projtimer epsg:4326 epsg:4326
./projtimer epsg:4326 epsg:26910
*/
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sys/time.h>
@pramsey
pramsey / centos-7-postgis-upgrade.md
Created December 16, 2018 00:25
Problems with PGDG Centos Upgrade Procedure when using PostGIS

Centos 7 Upgrade

PgSQL 10 / PostGIS 2.4 => PgSQL 11 / PostGIS 2.5

Setup

Starting from a bare Centos 7 box:

# root

sudo bash