postgis

pig repo add pgdg -u          # Add PGDG repo and update cache

CREATE EXTENSION postgis;

CREATE EXTENSION postgis;

SELECT PostGIS_Full_Version();

-- Geography column: distances are in meters, always WGS 84
CREATE TABLE cities (
    name text PRIMARY KEY,
    location geography(Point, 4326)
);

INSERT INTO cities VALUES
    ('New York',  ST_GeogFromText('POINT(-74.006 40.7128)')),
    ('London',    ST_GeogFromText('POINT(-0.1278 51.5074)')),
    ('Tokyo',     ST_GeogFromText('POINT(139.6917 35.6895)'));

-- Distance in meters (great-circle)
SELECT a.name, b.name, ST_Distance(a.location, b.location) / 1000 AS distance_km
FROM cities a, cities b WHERE a.name < b.name;

CREATE TABLE buildings (
    id serial PRIMARY KEY,
    name text,
    geom geometry(Polygon, 4326)
);

CREATE TABLE roads (
    id serial PRIMARY KEY,
    name text,
    geom geometry(LineString, 4326)
);

CREATE TABLE sensors (
    id serial PRIMARY KEY,
    label text,
    geom geometry(Point, 4326)
);

INSERT INTO sensors (label, geom) VALUES
    ('S1', ST_GeomFromText('POINT(-73.985 40.748)', 4326)),
    ('S2', ST_GeomFromText('POINT(-73.979 40.754)', 4326));

INSERT INTO sensors (label, geom) VALUES
    ('S3', ST_SetSRID(ST_MakePoint(-73.990, 40.735), 4326));

INSERT INTO buildings (name, geom) VALUES
    ('Plaza', ST_GeomFromGeoJSON('{"type":"Polygon","coordinates":[[[-73.98,40.74],[-73.97,40.74],[-73.97,40.75],[-73.98,40.75],[-73.98,40.74]]]}'));

CREATE INDEX idx_sensors_geom ON sensors USING GIST (geom);
CREATE INDEX idx_buildings_geom ON buildings USING GIST (geom);
CREATE INDEX idx_roads_geom ON roads USING GIST (geom);

CREATE INDEX idx_sensors_geom_brin ON sensors USING BRIN (geom);

-- Point from coordinates
SELECT ST_MakePoint(-73.985, 40.748);

-- Point with SRID
SELECT ST_SetSRID(ST_MakePoint(-73.985, 40.748), 4326);

-- From WKT
SELECT ST_GeomFromText('LINESTRING(0 0, 1 1, 2 1)', 4326);

-- From GeoJSON
SELECT ST_GeomFromGeoJSON('{"type":"Point","coordinates":[-73.985,40.748]}');

-- Make a line from two points
SELECT ST_MakeLine(
    ST_MakePoint(0, 0),
    ST_MakePoint(1, 1)
);

-- Make a polygon from a closed linestring
SELECT ST_MakePolygon(
    ST_GeomFromText('LINESTRING(0 0, 1 0, 1 1, 0 1, 0 0)')
);

-- Distance between two geometries (in SRID units)
SELECT ST_Distance(
    ST_GeomFromText('POINT(0 0)', 4326),
    ST_GeomFromText('POINT(1 1)', 4326)
);

-- Distance in meters using geography
SELECT ST_Distance(
    'SRID=4326;POINT(-73.985 40.748)'::geography,
    'SRID=4326;POINT(-0.1278 51.5074)'::geography
);

-- Area (in SRID units, or square meters for geography)
SELECT ST_Area(geom) FROM buildings;

-- Length of a linestring
SELECT ST_Length(geom) FROM roads;

-- Perimeter of a polygon
SELECT ST_Perimeter(geom) FROM buildings;

-- Does A contain B?
SELECT ST_Contains(a.geom, b.geom) FROM buildings a, sensors b;

-- Does A intersect B?
SELECT ST_Intersects(a.geom, b.geom) FROM buildings a, roads b;

-- Is B within distance D of A? (index-friendly)
SELECT ST_DWithin(a.geom, b.geom, 0.01) FROM sensors a, sensors b;

-- Are A and B within distance D? (geography, meters)
SELECT ST_DWithin(a.location, b.location, 10000) FROM cities a, cities b;

-- Does A touch B? (share boundary but not interior)
SELECT ST_Touches(a.geom, b.geom) FROM buildings a, roads b;

-- Does A cross B?
SELECT ST_Crosses(a.geom, b.geom) FROM roads a, roads b;

-- Does A overlap B? (same dimension, not identical)
SELECT ST_Overlaps(a.geom, b.geom) FROM buildings a, buildings b;

-- Buffer a geometry (expand by distance)
SELECT ST_Buffer(geom, 0.001) FROM sensors;

-- Intersection of two geometries
SELECT ST_Intersection(a.geom, b.geom) FROM buildings a, buildings b
WHERE ST_Intersects(a.geom, b.geom) AND a.id < b.id;

-- Union of geometries
SELECT ST_Union(geom) FROM buildings WHERE name LIKE 'Block%';

-- Convex hull
SELECT ST_ConvexHull(ST_Collect(geom)) FROM sensors;

-- Simplify a geometry (Douglas-Peucker)
SELECT ST_Simplify(geom, 0.0001) FROM roads;

-- Centroid
SELECT ST_Centroid(geom) FROM buildings;

-- Voronoi diagram
SELECT ST_VoronoiPolygons(ST_Collect(geom)) FROM sensors;

-- Transform from WGS 84 (4326) to Web Mercator (3857)
SELECT ST_Transform(geom, 3857) FROM sensors;

-- Transform to a UTM zone for meter-based calculations
SELECT ST_Area(ST_Transform(geom, 32618)) AS area_sqm FROM buildings;

-- Set the SRID on a geometry (does NOT reproject)
SELECT ST_SetSRID(geom, 4326) FROM sensors;

-- Get the current SRID
SELECT ST_SRID(geom) FROM sensors;

-- To GeoJSON
SELECT ST_AsGeoJSON(geom) FROM sensors;

-- To WKT
SELECT ST_AsText(geom) FROM sensors;

-- To KML
SELECT ST_AsKML(geom) FROM sensors;

-- To SVG path
SELECT ST_AsSVG(geom) FROM buildings;

-- To EWKT (includes SRID)
SELECT ST_AsEWKT(geom) FROM sensors;

SELECT label, ST_Distance(
    geom::geography,
    ST_SetSRID(ST_MakePoint(-73.985, 40.748), 4326)::geography
) AS distance_m
FROM sensors
WHERE ST_DWithin(
    geom::geography,
    ST_SetSRID(ST_MakePoint(-73.985, 40.748), 4326)::geography,
    500
)
ORDER BY distance_m;

SELECT s.label, b.name
FROM sensors s
JOIN buildings b ON ST_Contains(b.geom, s.geom);

SELECT b.name, COUNT(s.id) AS sensor_count
FROM buildings b
LEFT JOIN sensors s ON ST_Contains(b.geom, s.geom)
GROUP BY b.name;

SELECT label, geom <-> ST_SetSRID(ST_MakePoint(-73.985, 40.748), 4326) AS dist
FROM sensors
ORDER BY geom <-> ST_SetSRID(ST_MakePoint(-73.985, 40.748), 4326)
LIMIT 5;

-- Collect all sensor points into a MultiPoint
SELECT ST_Collect(geom) FROM sensors;

-- Compute the minimum bounding circle
SELECT ST_MinimumBoundingCircle(ST_Collect(geom)) FROM sensors;

SELECT ST_Area(geom::geography) AS area_sqm FROM buildings;