[postgis-users] steps in creating a new database

[Edward Mac Gillavry]

Cristian,

In PostGres, you can set up a new database scheme using a particular template. For spatial databases, you can create a template, so you don't have to add the postgis extensions or spatial reference systems each time you create a new spatial database. So you first create a new template for spatial databases that we call "template_postgis".

createdb template_postgis
$ createlang plpgsql template_postgis (plpgsql provides the functionality to use the spatial extensions)
$ psql -d template_postgis -f postgis/postgis.sql
$ psql -d template_postgis -f spatial_ref_sys.sql (contains the parameters of the various spatial reference systems, describing the projections and coordinate systems)

Next you grant the appropriate actions to the user "postgres".

$ psql -d template_postgis
# grant select, update, insert, delete on table geometry_columns to postgres;
# grant select on table spatial_ref_sys to postgres;
#\q

Since you do not want each database to be owned by postgres (just as you wouldn't want every directory in your file system to be owned by root), let's create a new user "gisuser":

$ createuser gisuser -P

Finally, you create your spatial database "gisdb" that is owned by the new user "gisuser". For this, you simply use the template that you created earlier "template_postgis". So, you create your template once and re-use every time you create a new spatial database, gisdb1, gisdb2, gisdb3 or a more explanatory database name. 

$ createdb -T template_postgis -E UTF8 -O gisuser gisdb

Hope this helps.

Edward



Date: Fri, 9 Apr 2010 12:10:47 +0200
From: vasileestan at gmail.com
To: postgis-users at postgis.refractions.net
Subject: [postgis-users] steps in creating a new database

Hi all,


I try to create a new postgis database on Ubuntu, and I'm quite confused about the steps in creating a database.


Creating database:
# createdb -T [template] [name_of_the_database]


- what are the use [-T] and [template] in this line 
- I can create my own template? for example: # createdb -T [general_data] [world]




	
	
	
	

Adding plpsql language to the database:


	
	
	
	

# createlang plpgsql [database]


	
	
	
	

# psql -d [database] -f
/usr/share/postgresql/8.4/contrib/postgis.sql

# psql -d [database] -f
/usr/share/postgresql/8.4/contrib/spatial_ref_sys.sql

# psql -d [database] -c"select
postgis_lib_version();"





- is plpgsql a condition in creating a geodatabase
- what's happening if I don't add this line # psql -d [database] -f
/usr/share/postgresql/8.4/contrib/spatial_ref_sys.sql

- This are the complete steps in creating a spatial database? 
- Where i can find more information regarding this subject?


Thank you, 
Cristian


 		 	   		  
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20100409/0012982b/attachment.html>