We 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.”
Option 1 (GUI): Go to our BigSQL installers page and download the distribution for your operating system.
Option 2 (CLI): Refer to tutorial Install BigSQL PGC.
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
Open a terminal and navigate to the installation directory.
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>
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:
Run the pgc update command to get the latest versions:
./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
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
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)
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:
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)