background

Usually, we may have a large amount of point data to display but do not want to preprocess, so the online browsing data request time is controlled at about 3s.

To prepare

Software environment :PostGIS, data is 3578998 points of data.

The environment

Oct-core CPU (Intel(R)Core(TM) i7-10750H CPU @2.60GHz 2.59 GHz),RAM 16.0 GB, SSD disk

The principle of

The principle of aggregation refers to my previous article ten million data display – vector slice point aggregation. It is more important to modify the PG database configuration. Refer to ali’s configuration items. During the adjustment process, you should back up the configuration file first.

SELECT ST_AsMVT(vt,'points',256,'geo') tile FROM (SELECT ST_SetSRID(ST_Point(a.geo),ST_Y(a.geo))), 426) geo from (SELECT ST_AsMVTGeom(w.geom,Box2D(TileBBox(10,176,409,4326)) AS geo from public.capnt w Where TileBBox(10,176,409,4326)&&geom) a group by ST_X(a.geo),ST! [](https://p1-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/644f045b96e34fe6b4d456c6eb8e8d13~tplv-k3u1fbpfcp-zoom-1.image)_Y(a. geo) ) AS vtCopy the code

In actual combat

Write a simple query service, when the level is 8-11 can use aggregation to reduce the amount of data, when the level is greater than 11 can directly check

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)), 426) geo from (SELECT ST_AsMVTGeom(w.geom,Box2D(TileBBox("+bbox+", 426)),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: ", ELAPSED) mux.get (tileBase, createTile) log.fatal (HTTP.ListenAndServe(":8081", mux))}Copy the code

References:

Developer.aliyun.com/article/700…