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-18.104.22.168-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
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
/opt/vmware/vpostgres/current/lib to ensure that PostGIS is built with vFabric Postgres binaries.
tar zxvf postgis-2.0.3.tar.gz
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
Now that the database is online, it is time to finalize the PostGIS module load as an EXTENSION module.
$ psql postgis
Type "help" for help.
postgis=# CREATE EXTENSION postgis;
postgis=# CREATE EXTENSION postgis_topology;
postgis=# select version();
[PostgreSQL 9.2.2 ] VMware vFabric Postgres 22.214.171.124 release build 997761
postgis=# select postgis_version();
2.0 USE_GEOS=1 USE_PROJ=1 USE_STATS=1
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.
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:
- Learn about master-slave clusters with vFabric Postgres on vSphere
- Find out how vFabric Postgres fits within the vFabric Reference Architecture
- Get insight on how vFabric Postgres provides dynamic (elastic and automated) memory management to optimize VM usage
- Learn about performance and scale on vPostgres 9.2
- Get more resources, download a trial, or purchase online
|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.|