postgres/postgis on AWS cheatsheet

postgis-cheatsheet.md

• Create DB at https://ap-southeast-2.console.aws.amazon.com/rds/home?region=ap-southeast-2#databases:
• Ensure ports are open (they seem to not be by default)
• brew cask install pgAdmin4
• Connect to database
• Run following:

select current_user;
CREATE EXTENSION postgis
CREATE EXTENSION fuzzystrmatch
CREATE EXTENSION postgis_tiger_geocoder;
CREATE EXTENSION postgis_topology;

• Add data in QGIS
• Test SQL:

CREATE TABLE geometries (name varchar, geom geometry);

INSERT INTO geometries VALUES
  ('Point', 'POINT(0 0)'),
  ('Linestring', 'LINESTRING(0 0, 1 1, 2 1, 2 2)'),
  ('Polygon', 'POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'),
  ('PolygonWithHole', 'POLYGON((0 0, 10 0, 10 10, 0 10, 0 0),(1 1, 1 2, 2 2, 2 1, 1 1))'),
  ('Collection', 'GEOMETRYCOLLECTION(POINT(2 0),POLYGON((0 0, 1 0, 1 1, 0 1, 0 0)))');

SELECT name, ST_AsText(geom) FROM geometries;