Spatial Analytics

Spatial Analytics using pgGeocoder with GeoFuse

Combining pgGeocoder with GeoFuse, it is possible to use the tabular output of the geocoder and create thematic maps for Spatial Analytics and provide more insights to assist in decision making.

This wiki uses pgAdmin4 to query and display PostgreSQL's data.

Tabular Data

It is quite common to view tabular data with addresses and metric attributes such the one below. This example data can be a table of convenience stores with their addresses and last month's sale along with this month's sales. The problem here is that it is very difficult to determine which areas are doing well and which areas where the sales are declining by just going through the table, and there is a need to view the data spatially in order to do any form of analytics.

pgGeocoder

To view this data spatially, geometric information has to be added. And this is done by geocoding the addresses and get its longitude and latitude so that it can be viewed in a map. Using pgGeocoder, the geocoding process can be done in place, since calling the geocoder function is very much like calling any other PostgreSQL function. In order for pgGeocoder to geocode the addresses, the following has to be done:

• Add a special column type that will contain the geocoder results.

ALTER TABLE tokyo_demo ADD COLUMN gc geores;

• Populate the created column by calling pgGeocoder's geocoder function in a SQL UPDATE statement.

UPDATE tokyo_demo SET gc = geocoder( address );