How to Build a Heat Map Tile App with Alibaba Cloud ECS & PostgreSQL/Ganos: A Step-by-Step Guide

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

Background

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:

Population Density: To display areas with high or low population density, helping urban planners and decision-makers to understand where services or infrastructure might be needed.
Traffic Analysis: To show the concentration of traffic at different times of the day or days of the week, assisting in congestion management and road planning.
Real Estate and Housing: To visualize property values or rental prices across different neighborhoods, providing insights for potential buyers, renters, and real estate investors.
Retail and Business: To identify hotspots where potential customers are concentrated, aiding businesses in selecting locations for new stores or targeted marketing campaigns.
Environmental Studies: To depict the distribution of pollution levels or temperature variations, which can be crucial for ecological research and public health.
Public Health: To track the spread of diseases or health-related issues such as obesity rates, helping health organizations in planning medical services and interventions.

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.

Overview

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.

Step 1. Use Terraform to Provision ECS and PostgreSQL Database on Alibaba Cloud

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

Step 2. Load Geometric Data in the RDS for PostgreSQL Database and Generate Heat Map Tiles

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);

Step 3. Deploy a Node.js Application to Display Heat Map Tiles

File Hierarchy

└── hmt_server
    ├── app.js
    ├── hmt.proto
    ├── index.html
    └── package.json

Backend Code

{
  "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.'));

Frontend Code

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>

Installation and Release Commands

# Locate to hmt_server path
cd ./hmt_server
# Install dependency libraries
npm i
# Running HMT service
node .
# Access http://eip:5500/ to check rendering

Display Heat Map Tiles (Rendering and Visualization)

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!

Community

How to Build a Heat Map Tile App with Alibaba Cloud ECS & PostgreSQL/Ganos: A Step-by-Step Guide

Background

Overview

Step 1. Use Terraform to Provision ECS and PostgreSQL Database on Alibaba Cloud

Step 2. Load Geometric Data in the RDS for PostgreSQL Database and Generate Heat Map Tiles

Step 3. Deploy a Node.js Application to Display Heat Map Tiles

File Hierarchy

Backend Code

Frontend Code

Installation and Release Commands

Display Heat Map Tiles (Rendering and Visualization)

Read previous post:

Read next post:

ApsaraDB

You may also like

Comments

ApsaraDB

Related Products

PolarDB for PostgreSQL

AnalyticDB for PostgreSQL

Tair

Time Series Database (TSDB)