PostGIS

GEOS
GDAL - Geospatial Data Abstraction Library
SFCGAL - C++ wrapper around CGAL
CGAL - Computational Geometry Algorithms Library
JTS - Java Topology Suite
Geotools - Open Source Java GIS Toolkit

Installation (Docker)

bash

docker run --rm -it --name pals-pg \
  -e POSTGRES_PASSWORD=postgres \
  -p 15432:5432 \
  postgis/postgis:14-3.4-alpine

SQL Snippets

Enable Extensions

sql

CREATE EXTENSION postgis;
CREATE EXTENSION postgis_topology;
-- Optional
CREATE EXTENSION postgis_sfcgal;
CREATE EXTENSION fuzzystrmatch;
CREATE EXTENSION postgis_tiger_geocoder;
CREATE EXTENSION address_standardizer;

Basic Geometry Operations

sql

-- Create table with Geometry column
CREATE TABLE gtest (
    id serial primary key,
    name varchar(20),
    geom geometry(LINESTRING)
);

-- Insert Data
INSERT INTO gtest (ID, NAME, GEOM)
VALUES (1, 'First Geometry', ST_GeomFromText('LINESTRING(2 3,4 5,6 5,7 8)'));

-- Spatial Query: Contains
SELECT id, the_geom
FROM thetable
WHERE ST_Contains(the_geom,'POLYGON((0 0, 0 10, 10 10, 10 0, 0 0))');

-- Spatial Query: Distance (Within Radius)
SELECT * FROM geotable
WHERE ST_DWithin(geocolumn, 'POINT(1000 1000)', 100.0);

-- Nearest Neighbor (KNN) using <-> (center distance)
SELECT name, gid
FROM geonames
ORDER BY geom <-> st_setsrid(st_makepoint(-90,40),4326)
LIMIT 10;

Coordinate Systems & Projections (SRID)

SRID 4326 (WGS 84): Standard GPS coordinates (Longitude, Latitude).
SRID 3857 (Web Mercator): Used by Google Maps, OpenStreetMap.

sql

-- Transform/Reproject
SELECT ST_Transform(the_geom, 4269) FROM geotable;

-- Set SRID when creating point
SELECT ST_SetSRID(ST_MakePoint(longitude, latitude), 4326);