Creating Spatial Tables

Creating Spatial Tables

Now that PostGIS is installed, let's create a table with geometry columns.

Create a Points Table

sql
CREATE TABLE cities (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    population INTEGER,
    geom GEOMETRY(Point, 4326)
);

Insert Some Data

sql
INSERT INTO cities (name, population, geom) VALUES
    ('London', 8982000, ST_SetSRID(ST_MakePoint(-0.1276, 51.5074), 4326)),
    ('Paris', 2161000, ST_SetSRID(ST_MakePoint(2.3522, 48.8566), 4326)),
    ('Berlin', 3645000, ST_SetSRID(ST_MakePoint(13.4050, 52.5200), 4326));

Verify

sql
SELECT name, population, ST_AsText(geom) FROM cities;

You now have a spatial table with real coordinate data!