百万点数据矢量切片实时聚合展示
背景
平时我们可能有比较大量的点数据要展示又不想预处理，在线浏览数据请求时间控制在3s左右。
准备
软件环境:PostGIS,数据是3578998条点数据。
环境
八核处理器(Intel(R)Core(TM) i7-10750H CPU @2.60GHz 2.59GHZ),RAM 16.0 GB,硬盘SSD
原理
聚合原理参考我之前的文章千万数据展示-矢量切片点聚合。
比较重要的是要进行pg数据库配置修改,参考阿里的配置项,调整过程中要先备份好配置文件，可以修改完一两个配置就重启数据库，这样就避免全部修改完不能启动的尴尬局面。
--示例1
SELECT ST_AsMVT(vt,'points',256,'geo') tile
FROM (select ST_SetSRID( ST_Point( ST_X(a.geo),ST_Y(a.geo)), 4326) geo from (
SELECT ST_AsMVTGeom(w.geom,Box2D(TileBBox(10,176,409,4326)),256,0,true) AS geo
FROM public.capnt w where TileBBox(10,176,409,4326)&&geom) a
group by ST_X(a.geo),ST_Y(a.geo) ) AS vt
实战
写个简单的查询服务，当级别在8-11级别可以使用聚合减少数据量，当级别大于11级直接查出就可以
package main
import
(
_ "github.com/lib/pq"
"database/sql"
"github.com/julienschmidt/httprouter"
"time"
"log"
"errors"
"fmt"
"net/http"
"strconv"
"bytes"
"compress/gzip"
)
func FloatToString(input_num float64) string {
return strconv.FormatFloat(input_num, 'f', 6, 64)
}
func gZipData(data []byte) (compressedData []byte,
err error) {
var b bytes.Buffer
gz := gzip.NewWriter(&b)
_, err = gz.Write(data)
if err != nil {
return
}
if err = gz.Flush(); err != nil {
return
}
if err = gz.Close(); err != nil {
return
}
compressedData =b.Bytes()
return
}
func check(e error) {
if e != nil {
log.Fatal(e)
}
}
type Tile struct {
X, Y, Z int
}
// ZMax is the maximum Z coordinate for a tile as well as quadkey level
const ZMax = 23
func Tile2Quadkey(t Tile) string {
//bytes.Buffer was bottleneck
z := t.Z
var qk [ZMax]byte
for i := z; i > 0; i-- {
q := 0
m := 1 << uint(i-1)
if (t.X & m) != 0 {
q++
}
if (t.Y & m) != 0 {
q += 2
}
var d byte
switch q {
case 0:
d = '0'
case 1:
d = '1'
case 2:
d = '2'
case 3:
d = '3'
default:
panic("Invalid tile.Quadkey()")
}
qk[z-i] = d
}
return string(qk[:z])
}
func Quad2Tile(qk string) (tile Tile, err error) {
tile.Z = len(qk)
for i := tile.Z; i > 0; i-- {
m := 1 << uint(i-1)
c := len(qk) - i
q := qk[c]
switch q {
case '0':
case '1':
tile.X |= m
case '2':
tile.Y |= m
case '3':
tile.X |= m
tile.Y |= m
default:
err = errors.New("Invalid Quadkey " + qk)
tile = Tile{} // zero tile
return
}
}
return
}
func createTile(w http.ResponseWriter, r *http.Request, p httprouter.Params){
//start := time.Now()
header := w.Header()
if origin := r.Header.Get("Origin"); origin != "" {
header.Set("Access-Control-Allow-Methods", r.Header.Get("Allow"))
header.Set("Access-Control-Allow-Origin", "*")
}
header.Set("Content-Type", "application/x-protobuf")
header.Add("Content-Encoding", "gzip")
header.Add("Accept-Encoding", "gzip")
xs:=p.ByName("x")
ys:=p.ByName("y")
zs:=p.ByName("z")
z,errz := strconv.Atoi(zs)
check(errz)
if (z>min){
var sql string
bbox:=zs+","+xs+","+ys
if (z>min&&z<max){
sql="SELECT ST_AsMVT(vt,'points',256,'geo') tile FROM (select ST_SetSRID( ST_Point( ST_X(a.geo),ST_Y(a.geo)), 4326) geo from ( SELECT ST_AsMVTGeom(w.geom,Box2D(TileBBox("+bbox+",4326)),256,0,true) AS geo FROM "+table+" w where TileBBox("+bbox+",4326)&&geom) a group by ST_X(a.geo),ST_Y(a.geo) ) AS vt"
}else{
sql="SELECT ST_AsMVT(tile,'points',4096,'geom') tile FROM (SELECT ST_AsMVTGeom(w.geom,Box2D(TileBBox("+bbox+",4326)),4096, 0, true) AS geom FROM "+table+" w where TileBBox("+bbox+",4326)&&w.geom ) AS tile "
}
//fmt.Println(sql)
var tile []byte
rows:= db.QueryRow(sql)
err := rows.Scan(&tile)
if err != nil {
log.Fatal(err)
http.Error(w, "Invalid tile url", 400)
return
}
size := cap(tile)
if size== 0 {
http.Error(w, "Invalid tile url", 400)
return
}
compressedData, Err := gZipData(tile)
if Err != nil {
log.Fatal(Err)
http.Error(w, "Invalid tile url", 400)
return
}else{
w.Write(compressedData)
}
}else{
http.Error(w, "Invalid tile url", 400)
return
}
}
var db *sql.DB
var err error
var table string
var min int=7
var max int=12
var tileBase string
func main(){
start := time.Now()
table="public.\"point_grid\""
mux := httprouter.New()
tileBase = "/tiles/:z/:x/:y"
connStr := "dbname=postgis_30_sample user=postgres password=123456 host=localhost port=5432 sslmode=disable"
db, err = sql.Open("postgres", connStr)
if err != nil {
panic(err)
}
defer db.Close()
err = db.Ping()
if err != nil {
panic(err)
}
db.SetMaxOpenConns(8)
db.SetMaxIdleConns(8)
elapsed := time.Since(start)
fmt.Println("min max ", min,max)
fmt.Println("启动耗时: ", elapsed)
mux.GET(tileBase, createTile)
log.Fatal(http.ListenAndServe(":8081", mux))
}
