背景

    平时我们可能有比较大量的点数据要展示又不想预处理，在线浏览数据请求时间控制在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) agroup by ST_X(a.geo),ST![]()_Y(a.geo)  ) AS  vt                  

    写个简单的查询服务，当级别在8-11级别可以使用聚合减少数据量，当级别大于11级直接查出就可以

package mainimport (       _ "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 levelconst ZMax = 23func 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.DBvar err errorvar table stringvar min int=7var max int=12var tileBase stringfunc 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))}

image

参考资料：