By Decai Xu
This article provides a general overview of using Terraform to achieve the goal. If you prefer using a graphical interface, you can refer to the following article:
Starter Guide | Build a Heat Map Tile App with Alibaba Cloud ECS and PostgreSQL in One Click
A Heat Map Tile is a data visualization tool that represents the spatial distribution of data by overlaying colored tiles on a map. Each tile's color intensity reflects the magnitude or concentration of a particular metric within that area. Heat Map Tiles are often used to visualize geographic data, making it easier to identify patterns, trends, and outliers at a glance.
Typical scenarios where Heat Map Tiles are used include:
Heat Map Tiles effectively communicate complex data sets by simplifying them into a visual format that is easy to interpret, making them a valuable tool in various fields where geographic data visualization is essential.
Embark on a journey to master the creation of a heat map tile application with this guide. This step-by-step tutorial is designed to provide you with an easy-to-follow roadmap, ensuring a smooth learning experience as you harness the capabilities of these robust technologies.
Illustrated below is a top-level architecture diagram that outlines what are used to construct your application on Alibaba Cloud. The key resources are Elastic Compute Service (ECS) and the powerful RDS for PostgreSQL (Ganos).
If you need a more scalable solution, consider replacing RDS for PostgreSQL (Ganos) in the diagram with PolarDB for PostgreSQL (featuring Ganos). PolarDB is a cloud-native relational database that offers full compatibility with PostgreSQL.
Experience its capabilities by joining the PolarDB Always Free Plan.
If this is your first time to use Terraform, please refer to https://github.com/alibabacloud-howto/terraform-templates to learn how to install and use it on different operating systems.
When your Terraform environment is ready, you need to prepare a resource provision script and run terraform apply to launch resources. You can refer to the following sample scripts but do remember to specify necessary information like AK/SK pairs and region.
terraform {
required_providers {
alicloud = {
source = "aliyun/alicloud"
version = "1.214.1"
}
}
}
provider "alicloud" {
access_key = "xxxxxxxxxxxxxxxxxx" # Your AK
secret_key = "xxxxxxxxxxxxxxxxxx" # Your SK
region = "cn-hongkong"
}# 1. VPC
resource "alicloud_vpc" "default" {
vpc_name = "vpc-hmt"
cidr_block = "172.16.0.0/16"
}
resource "alicloud_vswitch" "default" {
vpc_id = alicloud_vpc.default.id
cidr_block = "172.16.0.0/24"
# zone_id = data.alicloud_zones.default.zones[0].id
zone_id = "cn-hongkong-b"
vswitch_name = "vsw-hmt"
}
resource "alicloud_security_group" "group" {
name = "sg_hmt"
description = "Security group for AnalyticDB for PostgreSQL"
vpc_id = alicloud_vpc.default.id
}
resource "alicloud_security_group_rule" "allow_ssh_22" {
type = "ingress"
ip_protocol = "tcp"
nic_type = "intranet"
policy = "accept"
port_range = "22/22"
priority = 1
security_group_id = alicloud_security_group.group.id
cidr_ip = "0.0.0.0/0"
}
resource "alicloud_security_group_rule" "allow_ssh_5500" {
type = "ingress"
ip_protocol = "tcp"
nic_type = "intranet"
policy = "accept"
port_range = "5500/5500"
priority = 1
security_group_id = alicloud_security_group.group.id
cidr_ip = "0.0.0.0/0"
}
# 2. ECS
resource "alicloud_instance" "instance" {
security_groups = alicloud_security_group.group.*.id
instance_type = "ecs.c6e.large" # cn-hongkong-b
system_disk_category = "cloud_essd"
system_disk_name = "hmt_system_disk"
system_disk_size = 40
system_disk_description = "game_map_system_disk"
image_id = "centos_7_9_x64_20G_alibase_20231220.vhd"
instance_name = "hmt-server"
password = "Aliyun-test" ## Please change accordingly
instance_charge_type = "PostPaid"
vswitch_id = alicloud_vswitch.default.id
}
resource "alicloud_eip" "setup_ecs_access" {
bandwidth = "200"
internet_charge_type = "PayByTraffic"
}
resource "alicloud_eip_association" "eip_ecs" {
allocation_id = alicloud_eip.setup_ecs_access.id
instance_id = alicloud_instance.instance.id
}
# 3. RDS PostgreSQL
resource "alicloud_db_instance" "instance" {
engine = "PostgreSQL"
engine_version = "16.0"
instance_type = "pg.n2.large.1"
instance_storage = "20"
vswitch_id = alicloud_vswitch.default.id
instance_name = "hmt_database"
security_ips = [alicloud_vswitch.default.cidr_block]
}
resource "alicloud_db_database" "default" {
instance_id = alicloud_db_instance.instance.id
name = "hmt"
}
resource "alicloud_rds_account" "account" {
db_instance_id = alicloud_db_instance.instance.id
account_name = "hmt_user"
account_password = "Hmt_abc123"
account_type = "Super"
}
resource "alicloud_db_account_privilege" "privilege" {
instance_id = alicloud_db_instance.instance.id
account_name = alicloud_rds_account.account.name
privilege = "DBOwner"
db_names = alicloud_db_database.default.*.name
}
# 5. Output
output "eip_ecs" {
value = alicloud_eip.setup_ecs_access.ip_address
}
output "rds_pg_url" {
value = alicloud_db_instance.instance.connection_string
}
output "rds_pg_port" {
value = alicloud_db_instance.instance.port
}When your Terraform script execution is finished, you will see the output of connection strings for the ECS and RDS instances you just provisioned (like below).
● eip_ecs: The public EIP of the ECS for the map application host
● rds_pg_url: The connection endpoint URL of the RDS for PostgreSQL database
● rds_pg_port: The connection endpoint port of the RDS for PostgreSQL database
Click Elastic Compute Service, as shown in the following figure:
You can see one running ECS instance named hmt-server in the China (Hong Kong) region.
Copy this ECS instance's Internet IP address and log on to this ECS (CentOS system) instance remotely. For more details about remote login, please refer to login.
After logging in successfully, run the following command to install a postgresql client:
yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm -y
yum install postgresql15 -y
After that, use the following command to connect to the RDS for PostgreSQL instance. Please pay attention and replace YOUR-POSTGRESQL-ADDRESS with the connection string of your RDS for PostgreSQL instance:
psql -h YOUR-POSTGRESQL-ADDRESS -p 5432 -U hmt_user -d hmt 
Run the following command to create relative extensions of Ganos in hmt database.
CREATE EXTENSION ganos_spatialref;
CREATE EXTENSION ganos_geometry;
CREATE EXTENSION ganos_fdw;
Next, use the function ST_RegForeignTables to register the spatial data file gis_osm_buildings_a_free_1.shp as a foreign table.
SELECT ST_RegForeignTables('OSS://<ak_id>:<ak_secret>@<endpoint>/path/gis_osm_buildings_a_free_1.shp');
Once successfully registered, verify the foreign table gis_osm_buildings_a_free_1 by querying the information_schema.foreign_tables view.
SELECT foreign_table_name FROM information_schema.foreign_tables ORDER BY foreign_table_name ASC;
Insert data to table gis_osm_buildings_a_free_1_db from foreign table gis_osm_buildings_a_free_1.
CREATE TABLE table_name AS SELECT * FROM foreign_table_name;
Create spatial indexes on geometric column.
CREATE INDEX index_name ON table_name USING GIST(column_name)
Query spatial range by using function ST_Extent.
select ST_Extent(column_name) from table_name;
Execute a query to retrieve Heat Map Tiles within a specified spatial range by using function ST_AsHMT.
SELECT ST_AsHMT(column_name, --geometry type
ST_MakeEnvelope(minX, minY, maxX, maxY, 4326), -- Extent
512, -- Width
512 -- height
)
FROM table_name
WHERE column_name && ST_MakeEnvelope(minX, minY, maxX, maxY, 4326);
└── hmt_server
├── app.js
├── hmt.proto
├── index.html
└── package.json{
"name": "hmt_server",
"version": "1.0.0",
"main": "app.js",
"license": "ISC",
"dependencies": {
"chroma-js": "^2.4.2",
"express": "^4.18.2",
"lru-cache": "^10.1.0",
"pg": "^8.11.3",
"protobufjs": "^7.2.5",
"sharp": "^0.32.6"
}
}syntax = "proto2";
option optimize_for = LITE_RUNTIME;
message HMT {
required Type type = 1; // data value type
required uint32 rows = 2; // rows of matrix
required uint32 columns = 3; // columns of matrix
required uint32 srid = 4; // columns of matrix
required float xmin = 5; // xmin
required float ymin = 6; // ymin
required float xmax = 7; // xmax
required float ymax = 8; // ymax
oneof matrix {
intMatrix intValues = 10;
doubleMatrix doubleValues = 11;
}
message intMatrix {
repeated sint32 values = 12 [packed = true];
}
message doubleMatrix {
repeated double values = 13 [packed = true];
}
enum Type {
INT32 = 0;
DOUBLE = 1;
}
}const express = require('express');
const { Pool } = require('pg');
const chroma = require('chroma-js');
const sharp = require("sharp");
const protobuf = require('protobufjs');
const { LRUCache } = require('lru-cache');
// Set database connection
const CONNECTION = {
user: 'YOUR_USER',
password: 'YOUR_PWD',
host: 'YOUR_HOST',
database: 'YOUR_DB',
port: YOUR_PORT
};
// Table name
const TABLE_NAME = 'YOUR_TABLE';
// Geometric column
const GEOMETRY_COLUMN = 'YOUR_GEOM_COLUMN';
// set no data value
const NO_DATA_VALUE = 0;
// Geometric column spatial reference
const SRID = 4326
// Set Color Bands
const COLOR_MAP = [
['#536edb', 1],
['#5d96a5', 3],
['#68be70', 5],
['#91d54d', 7],
['#cddf37', 9],
['#fede28', 11],
['#fda938', 13],
['#fb7447', 15],
['#f75a40', 17],
['#f24734', 19],
['#e9352a', 21],
['#da2723', 23],
['#cb181d', 25]
];
// Create a database connection pool with a default of 10 connections
const pool = new Pool(CONNECTION);
// Configure color conversion
const [colors, domains] = COLOR_MAP.reduce(([c, d], [colors, domains]) =>
[[...c, colors], [...d, domains]], [[], []]);
const colorMap = chroma.scale(colors).domain(domains).mode('rgb')
// Load protobuf
const hmtDecoder = protobuf.loadSync('./hmt.proto').lookupType('HMT');
// Create a 1x1 transparent PNG and return it as an empty tile
const emptyPng = Buffer.from('iVBORw0KGgoAAAANSUhEUgAAAAEAAAABCAYAAAAfFcSJAAAACXBIWXMAAA7EAAAOxAGVKw4bAAAADUlEQVQImWP4//8/AwAI/AL+hc2rNAAAAABJRU5ErkJggg==', 'base64');
// For small scale tiles (z<5), set a cache that expires within 24 hours due to relatively less noticeable updates
const globalCache = new LRUCache({ max: 1000, ttl: 1000 * 3600 * 24 });
// For larger scale tiles (z>=5), set a cache that expires within 12 hours, and you can also modify it according to the actual situation
const localCache = new LRUCache({ max: 2000, ttl: 1000 * 3600 * 12 });
// Register Express Routing
express()
// Response HTML Page
.get("/", (_, res) => res.sendFile('index.html', { root: __dirname }))
// Response to HeatMap Tile services
.get('/hmt/:z/:x/:y', async ({ params: { z, x, y } }, res) => {
const cache = z < 5 ? globalCache : localCache;
const key = `${z},${x},${y}`
if (!cache.has(key)) {
// Set parallelism and call ST_AsHMT function, requesting 256x256 HeatMap tiles in this area
const parallel = z <= 5 ? 10 : 5;
const sql = `
set max_parallel_workers = ${parallel};
set max_parallel_workers_per_gather = ${parallel};
WITH _PARAMS(_BORDER) as (VALUES(ST_Transform(ST_TileEnvelope(${key}),${SRID})))
SELECT ST_AsHMT(${GEOMETRY_COLUMN},_BORDER,256,256) tile
FROM ${TABLE_NAME},_PARAMS
WHERE _BORDER && ${GEOMETRY_COLUMN};`
// Skip the set statement and obtain result of the ST_AsHMT function
const { rows: [{ tile }] } = (await pool.query(sql))[2];
// If there is no data in the area, return empty tile directly
if (!tile) cache.set(key, emptyPng);
else {
// Analyze Protobuf Results
const { type, doubleValues, intValues } = hmtDecoder.decode(tile);
const { values } = type == 1 ? doubleValues : intValues;
// Convert numerical values to corresponding colors and eliminate values without data
const pixels = values.reduce((_pixels, value) => {
_pixels.push(...colorMap(value).rgb());
_pixels.push(value <= NO_DATA_VALUE ? 0 : 255);
return _pixels;
}, [])
// Render as PNG tile
const rawConfig = { raw: { width: 256, height: 256, channels: 4 } };
const renderedPng = await sharp(Uint8Array.from(pixels), rawConfig)
.png().toBuffer();
cache.set(key, renderedPng);
}
}
const tile = cache.get(key)
res.set("Content-Type", "image/png").send(tile);
})
// Listen to port 5500
.listen(5500, () => console.log('HMT server started.'));Use Mapbox as the frontend map SDK. To apply for a token, please visit this website.
For the variable 'CENTER,' set the longitude and latitude of the central point within the desired spatial range.
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>HMT Viewer</title>
<meta name="viewport" content="initial-scale=1,maximum-scale=1,user-scalable=no">
<link href="https://api.mapbox.com/mapbox-gl-js/v2.14.1/mapbox-gl.css" rel="stylesheet">
<script src="https://api.mapbox.com/mapbox-gl-js/v2.14.1/mapbox-gl.js"></script>
</head>
<body>
<div id="map" style="position: absolute;left:0; top: 0; bottom: 0; width: 100%;"></div>
<script>
let CENTER = [YOUR_LONGITUDE, YOUR_LATITUDE]
mapboxgl.accessToken = YOUR_MAPBOX_TOKEN;
const map = new mapboxgl.Map({
container: 'map',
style: "mapbox://styles/mapbox/navigation-night-v1",
center: CENTER,
zoom: 5
})
map.on("load", () => {
map.addSource('hmt_source', {
type: 'raster',
minzoom: 3,
tiles: [`${window.location.href}hmt/{z}/{x}/{y}`],
tileSize: 256,
});
map.addLayer({
id: 'hmt',
type: 'raster',
source: 'hmt_source',
});
});
</script>
</body>
</html># Locate to hmt_server path
cd ./hmt_server
# Install dependency libraries
npm i
# Running HMT service
node .
# Access http://eip:5500/ to check rendering
Hope you've enjoyed this tutorial and found it helpful. Happy mapping, and look forward to hearing about your experience with creating your heat map tile applications!
[Infographic] Highlights | Database New Feature in December 2023
About Database Kernel | Transaction System of PolarDB-X Storage Engine – Part2
Alibaba Cloud Community - January 26, 2024
ApsaraDB - May 15, 2024
Data Geek - June 19, 2024
ApsaraDB - October 8, 2024
ApsaraDB - October 22, 2024
Data Geek - June 13, 2024
PolarDB for PostgreSQL
Alibaba Cloud PolarDB for PostgreSQL is an in-house relational database service 100% compatible with PostgreSQL and highly compatible with the Oracle syntax.
Learn More
AnalyticDB for PostgreSQL
An online MPP warehousing service based on the Greenplum Database open source program
Learn More
Tair
Tair is a Redis-compatible in-memory database service that provides a variety of data structures and enterprise-level capabilities.
Learn More
Time Series Database (TSDB)
TSDB is a stable, reliable, and cost-effective online high-performance time series database service.
Learn MoreMore Posts by ApsaraDB
