Bitnami PostGIS for Huawei Cloud

PostGIS adds support for geographic objects to the PostgreSQL object-relational database. It optimizes PostgreSQL to store and query data related to objects in space (like points, lines and polygons).

How can I check if my PostgreSQL installation has PostGIS support?

Execute the following command and check the output to find out if PostGIS is installed and which version is in use:

$ psql -c "SELECT name, default_version,installed_version FROM pg_available_extensions WHERE name LIKE 'postgis%' ;"

Here is an example of the output:

     name       | default_version | installed_version
postgis          | 2.0.0           |
postgis_topology | 2.0.0           |
(2 rows)

How can I check which version of PostGIS is my database loading?

To check which version of PostGIS is being loaded by the database, execute the following command:

$ psql yourdatabase -c "SELECT PostGIS_Full_Version()"

Here is an example of the output:

POSTGIS="2.0.0 r9605" GEOS="3.3.3-CAPI-1.7.4" PROJ="Rel. 4.8.0, 6
March 2012" GDAL="GDAL 1.9.0, released 2011/12/29" LIBXML="2.7.8"

How can I create a database template with PostGIS support?

Follow these steps:

  • Execute the following commands for creating a template for your spatially-enabled database. In this example, our template will be named template_postgis.

     $ createdb template_postgis
     # Allows non-superusers the ability to create from this template
     $ psql -d postgres -c "UPDATE pg_database SET datistemplate='true' WHERE datname='template_postgis';"
     $ psql template_postgis -c "create extension postgis"
     $ psql template_postgis -c "create extension postgis_topology"
     $ psql template_postgis -f <installdir>/postgresql/share/contrib/postgis-2.0/legacy.sql
     # Enable users to alter spatial tables.
     $ psql -d template_postgis -c "GRANT ALL ON geometry_columns TO PUBLIC;"
     $ psql -d template_postgis -c "GRANT ALL ON geography_columns TO PUBLIC;"
     $ psql -d template_postgis -c "GRANT ALL ON spatial_ref_sys TO PUBLIC;"

Although the official documentation recommends loading legacy.sql only "if really necessary", we have found that it is indeed required for installing Tiger Geocoder and running the OGC tests suite. It is also necessary to restore data from an old database. Run uninstall_legacy.sql later to remove the deprecated functions.

  • Create a new database with PostGIS support with:

     $ createdb -T template_postgis geocoder

    If you prefer to use the psql console:

     CREATE DATABASE geocoder TEMPLATE template_postgis;

How can I get PostgreSQL with PostGIS support?

Depending on the Bitnami stack you have installed, PostgreSQL and PostGIS might already be included. Refer to this page for more information and installation instructions.

How can I install Tiger Geocoder?

NOTE: This guide is for PostgreSQL 9.1+.

Tiger Geocoder is a plpgsql-based fully-functional geocoder that can process an arbitrary address string and, using normalized TIGER census data, produce a point geometry and rating reflecting the location of the given address and likeliness of the location.

To install it, follow these steps:

  • Navigate to the /opt/bitnami/postgresql/share/contrib/postgis-2.0/extras/tiger_geocoder/tiger_2010/ directory.

  • Edit the script and set the PGPASSWORD and THEDB parameters. In this example, the database name will be geocoder (the default value) so it is not necessary to modify the THEDB parameter. Set the PGPASSWORD parameter to your database password.

  • In the same file, uncomment the line below if it is not already uncommented:

     ${PSQL_CMD} -d "${THEDB}" -c "ALTER DATABASE ${THEDB} SET search_path=public, tiger;"
  • Edit the tiger_loader.sql file and set the PGPASSWORD and PGDATABASE parameters with the same values as in the file. You may need to modify the lines containing export PGPASSWORD and export PGDATABASE.

  • Edit other parameters in the files, such as PGPORT, PGHOST, PGUSER, PSQL_CMD and PGBIN, as needed.

  • Edit the file and comment out the line which refers to fuzzystrmatch.sql and uncomment the line containing CREATE EXTENSION fuzzystrmatch.

  • Create an example database with PostGIS support:

     $ createdb -T template_postgis geocoder
  • Create the necessary schema:

     $ ./

    This will create the tiger schema in your database and make it part of your database search_path. To verify this, run the following command:

     $ psql geocoder -c "SHOW search_path";
     public, tiger
     (1 row)
     $ psql geocoder -c "SELECT pprint_addy(normalize_address('202 East Fremont Street, Las Vegas, Nevada 89101')) As pretty_address;"
     202 E Fremont St, Las Vegas, NV 89101
     (1 row)

How can I load Tiger data in my database?

After installing Tiger Geocoder, load data from the census website into your database. The easiest way to do this is to use the loader_generate_script() function and create a script that will download data from the census website, extract it and load it into the example database. This example will load the data for the state of Rhode Island (RI).

  • Use the following command to create the script

     $ psql geocoder -c "SELECT loader_generate_script(ARRAY['RI'], 'sh');" -A -o
     $ chmod +x
  • Edit this file and set the PGPASSWORD parameter to reflect the correct database password.

  • Remove the first and last lines of the script. These are not Bash commands but output of the SQL command executed previously). Notice that the script uses the /gisdata directory to store the data downloaded from the census website. You can change this location if you wish.

  • Run these commands to obtain and load the data. Note that the wget and unzip commands should be installed for this to work.

     $ sudo mkdir /gisdata
     $ sudo chown -R bitnami /gisdata
     $ ./
     $ psql geocoder -c "SELECT install_missing_indexes();"

    If necessary, replace the account username bitnami with the correct username for the user running these commands.

How can I check that everything works?

To checking that everything works, run the OGC Test suite, as these scripts use the PostGIS functions. Using this test suite also helps verify that the Simple Features for SQL specification from the Open Geospatial Consortium (OGC) are implemented.

Browse to the /opt/bitnami/postgresql/data/pg_hba.conf/postgresql/share/contrib/postgis-2.0/extras/ogc_test_suite directory and run these commands:

$ createdb -T template_postgis ogc
$ psql -a -f 1_schema.sql ogc >& 1_output.txt
$ psql -a -f 2_queries.sql ogc >& 2_output.txt
$ psql -a -f 3_cleanup.sql ogc >& 3_output.txt