3

Seeing that I can't find the question asked here before, I have a feeling that I'm doing something wrong!

However, here is my problem: I have one PostGIS database with 525 functions, and one with none. Is there a way to import the 525 functions from one into the other? Or should I have used the one with 525 functions as a template?

Thanks

5

What you probably want to do is add the PostGIS functionality to the existing database. The one with no function probably doesn't have the PostGIS at all.

if you've got PostgreSQL 9.1+, you can run the following command on the database missing the functions:

CREATE EXTENSION postgis;

This will add all PostGIS functionality to the existing database.

If you are running a lower version of PostgreSQL, there are a few more steps to undertake to import all the functions.

I'll refer to the official documentation for those steps: http://postgis.net/docs/postgis_installation.html#create_new_db

A blatant copy/paste in case the link dies:

Many of the PostGIS functions are written in the PL/pgSQL procedural language. As such, the next step to create a PostGIS database is to enable the PL/pgSQL language in your new database. This is accomplish by the command below command. For PostgreSQL 8.4+, this is generally already installed

createlang plpgsql [yourdatabase]

Now load the PostGIS object and function definitions into your database by loading the postgis.sql definitions file (located in [prefix]/share/contrib as specified during the configuration step).

psql -d [yourdatabase] -f postgis.sql

For a complete set of EPSG coordinate system definition identifiers, you can also load the spatial_ref_sys.sql definitions file and populate the spatial_ref_sys table. This will permit you to perform ST_Transform() operations on geometries.

psql -d [yourdatabase] -f spatial_ref_sys.sql

If you wish to add comments to the PostGIS functions, the final step is to load the postgis_comments.sql into your spatial database. The comments can be viewed by simply typing \dd [function_name] from a psql terminal window.

psql -d [yourdatabase] -f postgis_comments.sql

Install raster support

psql -d [yourdatabase] -f rtpostgis.sql

Install raster support comments. This will provide quick help info for each raster function using psql or PgAdmin or any other PostgreSQL tool that can show function comments

psql -d [yourdatabase] -f raster_comments.sql

Install topology support

psql -d [yourdatabase] -f topology/topology.sql

Install topology support comments. This will provide quick help info for each topology function / type using psql or PgAdmin or any other PostgreSQL tool that can show function comments

psql -d [yourdatabase] -f topology/topology_comments.sql

If you plan to restore an old backup from prior versions in this new db, run:

psql -d [yourdatabase] -f legacy.sql

| improve this answer | |
  • Rene - thanks very much. This is just what I was looking for. – MrX May 8 '13 at 15:02
  • Hi @Rene, my database already has postgis extension. Then arcmap (arcgis) automatically generate schema sde besides my original schema public. The problem is: I cannot use ST_Union(geometry[]) on schema sde. Please help me out. Thanks! – Jackie Apr 29 '15 at 3:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.