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. Confirm that pgc has been installed by running a list command:

     ls (Linux/Mac)
     dir (Windows)
    

    Output:

     bam2        data        logs        pgadmin3    pgc
     conf        hub         pg96        pgbadger    uninstall
    
  4. Run the pgc status command to show your current Postgres server status.

    In this example Postgres 9.6 and the BigSQL Manager are running on the default ports:

     ./pgc status
        pg96 running on port 5432
        bam2 running on port 8050  
    

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

     ./pgc start
    
  5. Run the pgc list command to show what Postgres version(s) and components you currently have installed and update to latest versions:

     ./pgc list
     ./pgc update
    
  6. Install PostGIS:

     ./pgc install postgis23-pg96
    
  7. Run list command again to see that PostGIS is now installed:

     ./pgc list
      Extensions     postgis23-pg96        2.3.2-1            2017-02-09   Installed      
    

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

Launch psql and run the script

Run the script using the psql terminal.

Linux / OSX:

 cd <directory of installation>/pg96
 source pg96.env
 psql -U postgres -d template1 -f share/doc/postgresql/extension/create_postgis_sample_db.sql

Windows:

 cd <directory of installation>\pg96\bin
 psql -U postgres -d template1 -f ..\share\doc\postgresql\extension\create_postgis_sample_db.sql

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

 psql (Mac / OX)
 psql -U postgres -d template1 (Windows)
 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)