Home > Blogs > VMware vFabric Blog


How-To: Build a Geographic Database with PostGIS and vPostgres

Mobile Location Based Services are on the rise. After several false starts back in the mid 2000s, every mobile user now depends on their phones to tell them where they are, where their friends are, and to engage with social media like Facebook and Foursquare.  A report by Juniper Research suggests this market is expected to breach over $12 billion next year, where it hardly existed a few years ago at all.

This is in part because mobile apps have become ubiquitous now. In order to remain relevant, businesses need to interact socially and have a web store to remain accessible to their wandering customers.

Building a geographically aware application from scratch sounds daunting and like a lot of initial data setup. It doesn’t have to be. Products like vFabric Postgres (vPostgres) can be used along with the PostGIS extensions to perform geographic-style queries. Then,  public data and an open source visualizer can be used to transform the query into a meaningful result for your application or end user.

Transforming vFabric Postgres into a Geographic Database

vFabric Postgres has the DNA to quickly and effectively manage large amounts of raw data (up to 255GB RAM per VM for 9.2). This robustness is inherited partially from it’s community PostgreSQL heritage, and it’s read-scalability is achieved through ESX vSphere. From an accessibility perspective, it is a relational database using plain SQL, and it includes a new GUI for database server management and a new GUI for VM management.

To transform vFabric Postgres into a geographic database, we can extend it with PostGIS, a module used for geographical data manipulation with features seldom found in many database applications on the market: daily-used navigation systems, map engines, or location of mobile devices based on coordinates.

NOTE: Installing vFabric Postgres with PostGIS needs a bit of hacking. It is necessary to compile the code from sources—not just fetch, but a bit of configuration is needed. The installation is done on a CentOS 6.6 server, but the vFabric Postgres packages are compatible with RHEL-type OS as well.

Download the RPMs for vFabric Postgres

First, you need to download the vFabric Postgres RPMs from here.

Once downloaded, install them with this simple command:

rpm -ivh VMware-vFabric-Postgres-9.2.2.0-997761.x86_64.rpm \
VMware-vFabric-Postgres-client-9.2.2.0-997761.x86_64.rpm \
VMware-vFabric-Postgres-client-devel-9.2.2.0-997761.x86_64.rpm \
VMware-vFabric-Postgres-server-devel-9.2.2.0-
997761.x86_64.rpm

VMware-vFabric-Postgres is a package containing all the server-side binaries and settings (initdb, postgres, etc). VMware-vFabric-Postgres-client contains all the client-side libraries (psql, pg_dump, etc.) required on the server. You could also control the server created from a remote machine using the community-dedicated tools for PostgreSQL 9.2. Development packages are needed for PostGIS installation.

After install, all the binaries of vFabric Postgres are installed in the folder /opt/vmware/vpostgres/current/bin.

Adding PostGIS to the Mix

Now, let’s prepare the field for PostGIS. You need to know that PostGIS is dependent on a couple of libraries used for the manipulation of geographical objects: gdal, geos and proj. In order to install them, be sure to install the package repositories called ELGIS (repository for geographical libraries) and EPEL (useful to provide many dependent libraries).

rpm -Uvh http://elgis.argeo.org/repos/6/elgis-release-6-6_0.noarch.rpm # ELGIS
rpm -Uvh http://dl.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm # EPEL

Those URLs might change depending on the environment used, so be aware of what you do.

Next, we need to install the libraries that PostGIS depends on with the following YUM command:

yum install gdal gdal-devel proj proj-devel geos geos-devel

Adding the PostGIS Extension Module

The final thing to do is to install PostGIS extension module with vFabric Postgres.

First download source code of version 2.0 from here and use the following commands to install its binaries. Also be sure to set LD_LIBRARY_PATH to /opt/vmware/vpostgres/current/lib to ensure that PostGIS is built with vFabric Postgres binaries.

tar zxvf postgis-2.0.3.tar.gz
cd postgis-2.0.3
./configure --with-pgconfig=/opt/vmware/vpostgres/current/bin/pg_config
make
sudo make install

In order to simplify the next commands of this post, be sure to set PATH as /opt/vmware/vpostgres/current/bin in ~/.bashrc.

Initiate a new Database Server with Extension Module

Finally a new server can be built with a new, dedicated database “postgis”. In our case, the server uses a given account name and not root.

initdb -D $HOME/pgdata
pg_ctl start -D $HOME/pgdata –t 10 -w
createdb postgis

Now that the database is online, it is time to finalize the PostGIS module load as an EXTENSION module.

$ psql postgis
psql.bin (9.2.2)
Type "help" for help.
postgis=# CREATE EXTENSION postgis;
CREATE EXTENSION
postgis=# CREATE EXTENSION postgis_topology;
CREATE EXTENSION
postgis=# select version();
version
--------------------------------------------------------------------------
[PostgreSQL 9.2.2 ] VMware vFabric Postgres 9.2.2.0 release build 997761
(1 row)
postgis=# select postgis_version();
postgis_version
---------------------------------------
2.0 USE_GEOS=1 USE_PROJ=1 USE_STATS=1
(1 row)

Getting Started with Example Data

Now let’s take a simple example with some data you can find on the net. Here is some survey data from the official government website of Massachusetts.

wget http://wsgw.mass.gov/data/gispub/shape/state/townssurvey_shp.exe
unzip townssurvey_shp.exe

Once data has been extracted, we upload it to the database. shp2pgsql-gui is a good choice with its simple interface. Once launched you will find the following window.

After loading the data, you can directly query your database. For example, try the following query:

postgis=# select srtext, proj4text from spatial_ref_sys where srtext ILIKE '%Massachusetts%';

The result obtained from this format is difficult to understand. Using a GIS visualizer  (Quantum GIS is a good choice) will help to give visual context to our query. The package can be installed on CentOS using the package qgis on repository ELGIS.

yum install qgis

If you aren’t familiar with Quantum GIS, it is an official open source project of the Open Source Geospatial Foundation (OSGeo). It includes a desktop, browser, server, and client with many cool features for geographic information systems.

Once opened, add a new connection to PostGIS. After a couple of seconds, you will be able to see a nice map of Massachusetts, using your homemade vFabric Postgres server.

So, in a matter of minutes, not hours, you have a working visualization, data store and method to query to start as a basis for your location based application service. What you do with it next is up to your business needs, but one hint from the industry is that you should consider accounting for advertising as well, such as location based couponing. The Juniper Report see advertising as an area of particular growth in this market.

For more information on vFabric Postgres:

About the Author: Michael Paquier is a member of PostgreSQL technical staff at VMware. He is involved for many years with community development of PostgreSQL and Postgres-XC, and has worked on multi-master database technologies. He has also interest in parallel query processing and concurrent SQL processing.

One thought on “How-To: Build a Geographic Database with PostGIS and vPostgres

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>