Get started with PostGIS in PostGreSQL

postgres-postgis-cheatsheet.md

Get started with PostGis in PostGreSQL

Note: Coordinates are in (longitude latitude) format

Enable PostGIS extension for the current database

CREATE EXTENSION postgis;

Upgrade PostGIS version

ALTER EXTENSION postgis
UPDATE TO "VERSION_NUMBER"

Check if PostGIS extension is enabled for the current database

SELECT PostGIS_Version();

Create table

CREATE TABLE cities
(
  id serial PRIMARY KEY,
  name VARCHAR(50) UNIQUE NOT NULL,
  location geometry, //point
  boundary geometry //polygon
);

Insert a record

INSERT INTO cities("name", "location", "boundary")
VALUES("Wa", ST_GeomFromEWKT('SRID=4326;POINT(-2.5441288 10.0611456)'), ST_GeomFromEWKT('SRID=4326;POLYGON((-2.523873 10.094610, -2.544815 10.051342, -2.489197 10.026663, -2.474091 10.055061, -2.484047 10.084470, -2.523873 10.094610))'));

Fetch all cities that contain a specified point

SELECT * FROM cities WHERE ST_Contains("boundary", 'SRID=4326;POINT(lon lat)');

Fetch all cities that falls within a specific boundary

SELECT * FROM cities
WHERE ST_Within(boundary, ST_GeomFromText('POLYGON((-2.523873 10.094610, -2.544815 10.051342, -2.489197 10.026663, -2.474091 10.055061, -2.484047 10.084470, -2.523873 10.094610))', 4326));

Make a circle given a radius in meters and a point

SELECT ST_Buffer(ST_MakePoint(lon, lat)::geography, 4000);

Make a geometry point from VARCHAR(string) columns of lat lon

SELECT id, name,latitude, longitude,
ST_SetSRID(ST_MakePoint(longitude::float, latitude::float), 4326) AS center
FROM places;

Add geometry column

SELECT AddGeometryColumn('table', 'column', 4326, 'POINT', 2); 

Populate geo point column values from two lat lon float columns

UPDATE <table> SET <geom column> = ST_SetSRID(ST_MakePoint(<lon column>, <lat column>), 4326);

Convert a geometry to a Well-Known Text representation

SELECT ST_AsEWKT('0101000020E610000012D90759164CD0BF3F19E3C3EC351640');

Constructs a Geometry

Method 1: Using Text

SELECT ST_GeomFromText('POINT(lon lat)', 4326);

Method 2: Using Extended Well Known Text

SELECT ST_GeomFromEWKT('SRID=4326;POINT(lon lat)');

Creates a rectangular Polygon from the minimum and maximum values for X and Y (Bounding Box)

SELECT ST_AsText( ST_MakeEnvelope(Xmin, Ymin, Xmax, Ymax, 4326) );

Xmin = South West Lat, Ymin = South West Lon, Xmax = North East Lat, YMax = North East Lon

Get JSON representation of a geometry

SELECT ST_AsGeoJSON('0101000020E610000012D90759164CD0BF3F19E3C3EC351640');

Select points and order them by how close they are to a particular point

SELECT * FROM towns
ORDER BY geom_column <-> (SETSRID(ST_MakePoint(lon, lat)), 4326) LIMIT 10