13

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.

Ibtesam Hussain Sharif
  • 179
  • 1
  • 7
  • The PostGIS docs discuss how to "spatially enable" a database. Once you've done that you just add PostGIS columns with the appropriate PostGIS functions, use `UPDATE`s to copy the basic geo data into the PostGIS columns, and drop the old columns. – Craig Ringer Feb 24 '13 at 07:09
  • 2
    If you are running PostgreSQL 9.1+, you don't need to bother with the template database. Just create a database and then on the SQL view in pgAdmin, type and run CREATE EXTENSION postgis; That will spatially enable your database. Source : http://gis.stackexchange.com/questions/26508/how-to-create-postgis-template – Ibtesam Hussain Sharif Feb 24 '13 at 07:34

2 Answers2

14

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;
Mike T
  • 41,085
  • 18
  • 152
  • 203
  • I have the postgis Ubuntu packages installed, yet running that gets me "ERROR: could not open extension control file "/usr/share/postgresql/9.1/extension/postgis.control": No such file or directory" – Cerin Jan 08 '14 at 22:56
  • @Cerin I'd notify the package manager with additional details – Mike T Jan 09 '14 at 00:34
  • Just tried this again in Ubuntu 14, and it works fine. – Cerin Jan 30 '16 at 04:41
2

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

jpmaniac87
  • 121
  • 1
  • I get the same error as @Cerin but saying it can't find "/usr/share/postgresql/9.3/extension/postgis.control" (I've a Debian with installed postgresql-9.4 and postgresql-9.4-postgis-2.1 ) – FSp Sep 17 '15 at 13:16