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>
Confirm that pgc has been installed by running a list command:
ls (Linux/Mac) dir (Windows)
bam2 data logs pgadmin3 pgc conf hub pg96 pgbadger uninstall
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:
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
./pgc install postgis23-pg96
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
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)
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)