Convert a Normal Postgres Database to PostGis Database

First, make sure PostGIS is installed on the system, then create a PostGIS extension for the database using:

CREATE EXTENSION postgis;

Next, spatially enable each table with a geometry column and populate the column with the long/lat data points:

ALTER TABLE mytable ADD COLUMN geom geometry(Point,4326);
UPDATE mytable SET geom = ST_SetSRID(ST_MakePoint(long, lat), 4326);

Also consider using the geography type:

ALTER TABLE mytable ADD COLUMN geog geography(Point,4326);
UPDATE mytable SET geog = ST_MakePoint(long, lat)::geography;

I know this is older... but I'd add something to address a previous issue:

@Cerin, make sure you apt-get install postgresql-x.x-postgis-2.1, not just apt-get install postgis. I had that issue before as well

Related