Spatial Analytics
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.
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.
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
geocoderfunction in a SQL UPDATE statement.
UPDATE tokyo_demo SET gc = geocoder( address );Note: The demo table contains 40,578 records, and it took 61 seconds for pgGeocoder to geocode the addresses with a MacBook Pro M1 machine.
From here, the gc column should now contain the geocoded data of the address.
After geocoding the addresses with pgGeocoder, the table now contains both the normalized information of the address and, most importantly, the geographic longitude and latitude data. With this, it is now possible to use GeoFuse to view the data in a thematic map.
GeoFuse is able to draw points if the table passed to it contains lon and lat fields that will hold the coordinates of the record. So for this, the geocoder resulting coordinates stored in the gc column will be used with the SQL statement below:
select address,
last_sales::int as 先月売上,
this_sales::int as 今月売上,
(gc).x as lon,
(gc).y as lat
from tokyo_demo;The resulting data that can be seen pgAdmin now can be Copy and then Pasted into GeoFuse. Do ensure though that the Copy With Header is checked when doing the Copy and Paste in the pgAdmin console option.
Once Copied, the data can be now Pasted into the GeoFuse web application page. Add a Layer Name, and then press Submit to send the data into the GeoFuse server.
By clicking the the Display Map button once the data has been submitted, a new Tab Page will be created showing a map with the 40,578 points that have been created.
The data can now be zoomed into and queried.
Looking at the point data just created though, it is still quite difficult to extract any insights from the map. GeoFuse can match Shikuchoson (市区町村名) name with a map polygon geometric object as long as the data passed has a shikuchoson_name column, then do thematics on that resulting map. This might provide a much better understanding of the data instead of just viewing the points.
To do this, the table data will be aggregated by shikuchosn normalized data created by the geocoder, by using the SQL GROUP BY functionality. The metric attributes can summed up using the SUM() function, although other statistical functions supported by the PostgreSQL database can be used. The SQL statement will be:
select (gc).todofuken||(gc).shikuchoson as shikuchoson_name,
sum(last_sales)::int as 先月売上,
sum(this_sales)::int as 今月売上
from tokyo_demo
group by (gc).todofuken,(gc).shikuchoson;
The table data can then be Copied and Pasted once more into GeoFuse, and the resulting map with thematics will be:
This demo just uses sample data, but by just looking at the created thematics map, it is easy now to conclude where sales are doing well and area where sales are not doing as well.
The concept of Drilling Down is to go from an overview into a more detailed view which will provide more information. In GeoFuse' case, it is possible go from the Shikuchoson level into an Ooaza (or Choume) level since GeoFuse can also match an Ooaza level address with a polygon geometric object as long as there is an ooaza_name column in the passed data, and then again do thematics on the resulting map. The Ooaza data of the address is also provided by the geocoder, and can be similarly used to aggregate data with the SQL GROUP BY function. In this example, we will drill down into the Suginami-Ku (杉並区) area.
The SQL statement will be:
select (gc).todofuken||(gc).shikuchoson||(gc).ooaza as ooaza_name,
sum(last_sales)::int as 先月売上,
sum(this_sales)::int as 今月売上
from tokyo_demo
where (gc).shikuchoson = '杉並区'
The table data can then be Copied and Pasted once more into GeoFuse, and the resulting map with thematics will be:
Aside from creating Thematic Maps from passed data sets, GeoFuse can also create a Pie Chart to display the data spread count of the thematic numeric categories:
Also, it is also possible to edit the category numeric ranges as well as its colors.
