Data preparation

Data source (point data using the imported data 120038310, juejin.cn/post/690310…)

 CREATE TABLE default.points
(
    `id` UInt32,
    `Lon` Float32,
    `Lat` Float32
)
ENGINE = MergeTree()
ORDER BY (intHash64(id), Lon, Lat)
SAMPLE BY intHash64(id)
SETTINGS index_granularity = 8192
insert into points SELECT id,Lon,Lat FROM pnts
Copy the code

Sampling function and Hash function

SAMPLE clause features

This SAMPLE clause allows approximate SELECT query processing.

When data sampling is enabled, queries are not executed on all data, but only on a specific piece of data (the sample). For example, if you need to calculate statistics for all accesses, simply perform a query on 1/10 of the score of all accesses and multiply the result by 10.

Approximate query processing can be useful in the following situations:

When you have strict time requirements (e.g. <100ms), but you can’t meet their costs with additional hardware resources. When your raw data is inaccurate, so approximations do not significantly degrade quality. The goal of a business requirement is to approximate the results (to be cost-effective, or to sell the exact results to power users).

The Hash function intHash64 can be used to irreversibly perturb elements. After testing more than 100 million points sampling performance is satisfactory, can provide a satisfactory display for the page. Note: The sample field must be of type int and must be in primary key or sort. Sample SQL,0.01 as an example, can be modified according to the actual situation

SELECT id,Lon,Lat FROM points SAMPLE 0.01
Copy the code

Add server and web page

Using Express as a server, requesting data and presenting it in a web page using Maptalks, testing locally

References:

Clickhouse. Tech/docs/useful/SQL… Clickhouse. Tech/docs/useful/SQL…