postgis_plus_bigsqlWe are excited to announce that BigSQL distributions of PostGIS now come bundled with several popular community spatial extensions:

This is the only community distribution to offer PostGIS with these extensions across ALL platforms (Mac, Linux, and Windows)! So, for all you GeoGeeks using Linux / Mac – spatially enabling your PostgreSQL database with PostGIS functionality just got a lot easier.

In addition, we’ve included a sample script to help guide you through creating your first PostGIS database.

In the upcoming weeks, we will offer guidance for both GUI and command line work flows (see Are you a Yoda or a Diego?) and blogging about what you can do with spatially enabled data.

So, let’s get started…

What is PostGIS?

PostGIS is powerful spatial database extender for PostgreSQL. It adds support for geographic objects allowing location queries to be run in SQL. For a full list of supported features look here: http://postgis.net/features

How to install the BigSQL distribution

If you have installed the BigSQL distribution, skip to the next section, “Getting started with BigSQL PGCLI.”

Getting started with BigSQL PGCLI

PGCLI (Pretty Good Command Line Interface) is a package and update manager, similar to yum or apt-get, specifically for PostgreSQL and related community projects. PGC enables you to easily find and install packages, configure multiple versions of PostgreSQL, update existing applications, and control services by starting and stopping from the command line.

You can run pgc commands with the following call:

 cd <directory of installation>
 ./pgc (Linux and Mac)
 pgc (Windows)

For the purpose of this tutorial, we will be using the Linux/Mac syntax: ./pgc

To learn more about PGC, go to the BigSQL PGCLI tutorial.

Update components to latest versions and install PostGIS

  1. Open a terminal and navigate to the installation directory.

  2. On windows it could be C:\Postgres, on Linux and Mac it could be any directory based on the installation method. You will be able to locate “pgc” bash script OR “pgc.bat” file in this directory.

     cd <directory of installation>
    
  3. Run the pgc status command to show your current Postgres server status.

    In this example Postgres 9.6 is running on the default port:

     ./pgc status
        pg96 running on port 5432
    

    If your Postgres instance is not running, start it with the following pgc command:

     ./pgc start
    
  4. Run the pgc update command to get the latest versions:

     ./pgc update
    
  5. Install PostGIS:

     ./pgc install postgis23-pg96
    

Create a PostGIS sample database

We have provided you with a sql script that will create a Postgis enabled database and includes the following extensions:

Before you begin, navigate to directory that contains the sql script you will run to create a PostGIS database and check to make sure the file create_postgis_sample_db.sql downloaded with your install. You can also take a look at what is in the script using your favorite editor tool:

 cd <directory of installation>/pg96/share/doc/postgresql/extension 
 ls

 Optional:
 vi create_postgis_sample_db.sql

The GDAL_DATA Directory Path

In order for all your PostGIS extensions to properly work, you will need to set the GDAL_DATA environment variable to the location of the directory gdal.

Lucky for you, with the BigQL distribution this is as easy as running the following commands via a command line tool.

Linux / OSX:

 cd <directory of installation>/pg96
 source pg96.env

Windows:

 cd <directory of installation>\pg96
 pg96-env

Both of these commands set environment variables that will live during your current session. Note that if you close your terminal or command prompt, these variables are removed from memory.

It’s also possible to set GDAL_DATA as a persistent environment variable. But that is the beyond the scope of this tutorial.

Run the script using the psql terminal.

Linux / OSX:

 psql -f share/doc/postgresql/extension/create_postgis_sample_db.sql

Windows:

 psql -f ..\share\doc\postgresql\extension\create_postgis_sample_db.sql

Check to see that the database and extensions were successfully created:

 psql (if using Windows, you may need to include -U postgres with the psql command)
 postgres=# \connect postgis_sample;
 You are now connected to database "postgis_sample" as user "postgres".

 \dx
                 List of installed extensions
                 Name             | Version |   Schema               
    ------------------------------+---------+------------
     address_standardizer_data_us | 2.3.2   | public     
     fuzzystrmatch                | 1.1     | public     
     ogr_fdw                      | 1.0     | public     
     plpgsql                      | 1.0     | pg_catalog 
     postgis                      | 2.3.2   | public     
     postgis_tiger_geocoder       | 2.3.2   | tiger      
     postgis_topology             | 2.3.2   | topology   
    (8 rows)

Exit psql:

 \q

Install pgadmin3 if it is not already installed:

 cd <directory of installation>  
 ./pgc install pgadmin3

Launch pgadmin3 (the GUI should popup when you run this command):

 ./pgc start pgadmin3    

Output should look something like this:

pgadmin_postgis_sample

Get me outta here! (CLI edition):

You can always uninstall your BigSQL Postgres distribution at any time with the following commands:

 cd <directory of installation>
 ./pgc stop
 cd uninstall
 open uninstall.app

Finally, cd to location of your PostgreSQL directory and delete the PostgreSQL directory and it’s contents:

 rm -r <directory of installation> (Linux or Mac)
 rmdir <directory of installation> /s (Windows)