Search code examples
databasepostgresqlpostgis

Convert a Normal Postgres Database to PostGis Database

I have a normal postgres database with lots of geo coded data. This is just present two columns as latitude and longitude.

I want to convert this database to PostGIs database. Can anyone suggest me a way to convert the database i have? I don't want to create a new postgis tempalte based database and then move the whole data one by one.

Solution

  • 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;