Postgis 101

postgis_setup.sh

sudo apt-get install postgres
sudo apt-get install postgis

# Connect to postgres with user postgres
psql postgres

# List databases
\d+

# Create database
CREATE DATABASE mydb;

# Connect to databse
\connect mydb

# Inne i postgres
CREATE EXTENSION postgis

# Create table
CREATE TABLE mytable (
    id SERIAL PRIMARY KEY,
    name VARCHAR(64),
    location GEOMETRY(POINT, 4326) -- 4326 is the srid for WGS84
);

# List tables (relations)
\dt+

# Insert data
INSERT INTO mytable (name, location) values ('Trondheim', 'SRID=4326;POINT(10.3951 63.4305)');

# Select data
select *, ST_AsText(location) as location_wkt from mytable;

# Disconnect with ctrl-d

# To connect to mydb again
psql mydb