PostGIS Quick Start Guide

    This document assumes that you are working with PostGIS 2.0+ and PostgreSQL 9.1+. Note that this guide is based on the official PostGIS documentation.


    What is PostGIS?

    PostGIS is PostgreSQL extension that provides spacial database support to PostgreSQL. This means that it optimizes PostgreSQL to store and query data related to objects in space (like points, lines and polygons).

     

    How can I get PostgreSQL with PostGIS support?

    Download and install BitNami LAPPStack 5.4+ for Linux or BitNami MAPPStack 5.4.+ for Mac OS X. In a few clicks you will have PostgreSQL  9.1+  with PostGIS 2.0+ (check changelog for the exact version of components) and all the dependencies installed in your system. The stack is self-contained and will not interfere with any other software that you may have in your system (it will not interfere with your existing PostgreSQL installation).

    If you already have an existing PostgreSQL from a BitNami installation and you are running Linux or Mac OS X check this.

    If you are running Windows you can install BitNami WAPPStack and then follow these steps.

    Alternatively you can install it from source or the system packages according to the official documentation . Be careful when upgrading components and dependencies and make sure that you will not break any existing software you have.

    How can I load the environment that I have installed with BitNami?

    As we mentioned BitNami is self-contained. It doesn't modify your current enviroment. To use BitNami after the installation you need to load the BitNami environment before executing commands (otherwise binaries will be taken from your system path, and not the ones packaged and configured by BitNami)

     

    $ cd <installdir>
    $ ./use_lappstack
    

     

    Check that the environment is loaded correctly. The output of the command below should point to your BitNami installation.

    $ which psql
    


    In BitNami virtual appliances and AMIs the below are the default parameters:

    The installation directory is /opt/bitnami.
    The default username is bitnami.
    The default database root user is postgres.
    The default password for the database root user is bitnami.

     

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

    Once your environment is set we will double check the version of PostGIS and it dependencies. Execute the following line and you will get an output similar to the below.

     

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

     

         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?

    If you want to check if a specific database is loading the PostGIS extension and which version of PostGIS is using you can execute the following:

     

    $ psql yourdatabase -c "SELECT PostGIS_Full_Version()"
    


    You should get something similar to the below.

     

    postgis_full_version
    -----------------------------------------------------------------------
    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"
    LIBJSON="UNKNOWN" TOPOLOGY RASTER
    

     

    How can I create a database template with PostGIS support?

    Execute the following commands for creating a template for your spatially-enabled database. In the 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
    
    # Enabling 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;"
    


    To check the version of PostGIS and its dependecies execute the following:
     

    $ psql template_postgis -c "SELECT PostGIS_Full_Version()"
    
    postgis_full_version
    --------------------------------------------------------------------------------------------------------------------------------
    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"
    LIBJSON="UNKNOWN" TOPOLOGY RASTER
    


    Although the official documentation mentions that loading legacy.sql should be done only "if really necessary", we have found that it is indeed required for installing Tiger Geocoder and running the OGC tests suite. Also it is necessary if you are restoring data from an old database. Later you can run uninstall_legacy.sql for removing the the deprecated functions.

    Also notice that with PostgreSQL 9.1+ we use the "create extension" command, this differs from previous versions of PostgreSQL.

    Now you can create a new database with PostGIS support with:

    $ createdb -T template_postgis geocoder
    

    or once your are in the psql console:

    CREATE DATABASE geocoder TEMPLATE template_postgis;
    

    How can I check that everything works?

    For checking that everything works we will run the OGC Test suite scripts. This step is not necessary but as these scripts uses the PostGIS functions we will run them to check that everything works and that the Simple Features for SQL specificaton form  the Open Geospatial Consortium (OGC) are implemented.

    Go to the extras/ogc_test_suite directory <installdir>/postgresql/share/contrib/postgis-2.0/extras/ogc_test_suite
     

    $ 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
    

     

    How can I install Tiger Geocoder?

    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.

    Go to the tiger_geocoder folder <installdir>/postgresql/share/contrib/postgis-2.0/extras/tiger_geocoder/tiger_2010/

    Edit the create_geocode.sh script and set the PGPASSWORD and THEDB parameters. In this example our database will be 'geocoder' so you will not need to modify the THEDB parameter. In the PGPASSWORD parameter set the password of your database (the one you enter during the installation of BitNami). In this file also uncomment the line below if it is not already uncommented:

     

    ${PSQL_CMD} -d "${THEDB}" -c "ALTER DATABASE ${THEDB} SET search_path=public, tiger;"
    


    Now edit the tiger_loader.sql file and set the PGPASSWORD and PGDATABASE parameters with the same values that you set in the create_geocoder.sh file. Notice that you need to modify the lines for unix systems (the ones with "export PGPASSWORD" and "export
    PGDATABASE
    ").

    If you are not using a BitNami installation you may need to edit other parameters in those files (PGPORT, PGHOST, PGUSER, PSQL_CMD, PGBIN). Also in the create_geocoder.sh file comment the line which refers to fuzzystrmatch.sql and uncomment the line with "CREATE EXTENSION fuzzystrmatch" (remember that this guide is for PostgreSQL 9.1+).

    Once we have configured these files, we can install Tiger Loader in our database. We first need to create our example database with postgis support though. You will be prompted for your database password serveral times.

    $ createdb -T template_postgis geocoder
    $ ./create_geocode.sh
    

     

    This will create the 'tiger' schema in your database and it will be part of your database search_path. To verify it you can run the following:

    $ psql geocoder -c "SHOW search_path";
    
    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;"
    
               pretty_address
    ---------------------------------------
    202 E Fremont St, Las Vegas, NV 89101
    (1 row)
    


    How can I load Tiger data in my database?

    Once you have installed Tiger Geocoder in your database you can load data from the census website and load it in your database.

    We will use the Loader_Generate_Script function for creating the script that will download data from the census website, extract it and load it in our example database geocoder. In the example we will load the data from the state of Rhode Island (RI).

    The following command will create the RIdata.sh.

    $ psql geocoder -c "SELECT loader_generate_script(ARRAY['RI'], 'sh');" -A -o RIdata.sh
    $ chmod +x RIdata.sh
    


    Edit this file and set the PGPASSWORD parameter with the right database password. You can also remove the first and the last line (they are not really bash actions, just output of the SQL command we executed previously). Also you will notice that it uses the /gisdata
    for storing the data downloaded from the census website. You can change that location in the script, however in the example we will create that directory.

    Notice that wget and unzip commands should be installed in your system.

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

     

    Instead of bitnami use the username of the user running these commands. Now you will have the Rhode Island data loaded in your geocoder database.

    Tag page (Edit tags)
    • No tags
    Pages that link here
    Page statistics
    26546 view(s), 6 edit(s) and 13861 character(s)

    Comments

    You must login to post a comment.

    Attach file

    Attachments