Abstract: Based on TableStore, it is easy to realize the track management and geographical fence of 100 million magnitude

I. Project background

Trajectory management system is widely used in daily life, such as takeaway delivery trajectory, express logistics flow, vehicle positioning trajectory, etc. This scenario is similar to geographic location management. The core point and bottleneck are the storage performance and query ability of the database, and the time fields are arranged in a positive order to ensure the sequence of track points. On the one hand, storage services need to deal with the low-latency storage and read of massive data. On the other hand, storage services also need to provide efficient multi-dimensional data retrieval and sorting. TableStore is still competent for trajectory management scenarios, and fully capable of realizing trajectory management system. Might as well experience based on TableStore to build [100 million weight motorcycle management system] example;

Demand scenarios

For safety reasons, motorcycles are restricted from entering certain areas in an urban area. A motorcycle rental company, in order to better manage the motorcycle under the jurisdiction of the illegal problem, the motorcycle under the jurisdiction of their own positioning system, timing acquisition of motorcycle position. Motorcycle rental companies can query and count violations through the track management platform, and can also serve as a basis to remind rental users who violate regulations that excessive violations will be blacklisted. Query scenario: [November 01, 2018] No. [ID00001] motorcycle track and violation query;

Note: This sample provides [billion magnitude] trajectory data. Official console address: project example

The sample is embedded in the console of table storage. Users can log in to the console to experience the system. (If you are a new user of table storage, you need to click Open service to experience the system.

TableStore scheme

The use of table storage (TableStore) easily build a set of: 100 million weight motorcycle management system. The multi-index function provides the capability of GEO search and multi-dimensional query, and obtains the track of devices by sorting time. At the same time, users can create indexes at any time and complete automatic synchronization without worrying about the amount of data. TableStore, as a fully hosted distributed NoSql data storage service with zero operation and maintenance provided by Ali Cloud, has the functions of [massive data storage], [automatic fragmentation of hot data], [multi-dimensional retrieval of massive data] and so on, which effectively solves the challenge of large expansion of GEO data. SearchIndex not only ensures high availability of user data, but also provides multi-dimensional data search and sorting capabilities. Create a variety of indexes for a variety of scenarios to achieve a variety of pattern retrieval. Users can create and open indexes only when needed. The TableStore ensures the consistency of data synchronization, which greatly reduces the workload of users’ scheme design, service operation and maintenance, code development and so on.

Two, construction preparation

If you have a good experience of [100 million motorcycle management system] based on TableStore, and want to start the journey of building your own system, just follow the following steps to start building:

1. Enable table storage

The form storage service is opened through the console. The form storage is out-of-the-box (postpaid), and the free amount of the function test is provided to the users by the pay-per-quantity method. Form storage official website console, free limit description.

2. Create an instance

Use the console to create a table storage instance and select a Region that supports multivariate indexes. (SearchIndex is not yet commercially available, but will be available in Beijing, Shanghai, Hangzhou and Shenzhen, with the rest to follow)

After creating an example, submit work order to apply for multi-index function invitation test (open by default after commercialization, no charge for use).

  • Invitation test address: Submit work order, choose “Form storage” > “Product function and feature Consultation” > “Create work Order”, the application content is as follows:
  • Problem description: Please fill in the application for SearchIndex Test
  • Confidential Information: Please fill in [region + instance name], for example: Shanghai +myInstanceName

3. SDK download

Using the SDK with multi-index (SearchIndex), the official website, temporarily added new features to the Java, Go, node.js SDK

java-SDK

< the dependency > < groupId > com. Aliyun. Openservices < / groupId > < artifactId > tablestore < / artifactId > < version > 4.7.4 < / version > </dependency>Copy the code

go-SDK

$ go get github.com/aliyun/aliyun-tablestore-go-sdkCopy the code

4. Table design

Store retrieval system sample, only a simple use of a store table, mainly contains fields: store type, store name, store location, store average score, per capita consumption and so on. Table design is as follows: Table name: geo_track

The column name The data type The index type Fields that
_ID (primary key column) String
MD5(mId + timestamp) avoids hotspots
mId Stirng
Motorcycle number
timestamp long LONG Time point (millisecond timestamp)
pos String GEO_POINT Vehicle position: “30.132,120.082”(latitude, accuracy)
. . . .

Iii. Start building (core code)

1. Create table

Users only need to create “Motorcycle track table” under the instance that has completed the invited test: create and manage the data table through the console (users can also directly create the data table through the SDK); create other tables, such as rental user table and motorcycle information table, according to requirements: only the track table is shown here, and the table name is geo_track

Create table index

TableStore automatic full, incremental index data synchronization: users can create index through the console, manage index (can also create index through SDK)

3. Data import

Insert some test data (108 million pieces (10 thousand motorcycles 70 days 24 hours x 6 “10 minute points “) in the console sample, users can insert a small amount of test data through the console themselves);

The table name: geo_track

Motorcycle number Trace point MD5 (mId + timestamp) time Store location
id00001 f50d55bec347253c24dc9144dff3e3b7 1541103600000 30.30094, 120.01278

The table name: moto_user

Motorcycle No. (Primary key) Motorcycle color Motorcycle brand Motorcycle rental users
id00001 silver H-brand motorcycle Six Yang

4. Data reading

Data reads fall into two categories:

Primary key read (motorcycle information query)

Obtain primary key columns based on native table storage: getRow, getRange, batchGetRow, etc. Primary key read for index (automatic) backlookup, users can also provide primary key (motorcycle number) single query page, query very fast. Single primary key query does not support multi-dimensional retrieval.

Index reading (track information query)

Query: Search interface based on the new SearchIndex feature. Users are free to design multi-dimensional condition combination queries for index fields. By setting and selecting different query parameters, different query conditions and different sorting methods are constructed. Currently support: precise query, range query, prefix query, matching query, wildcard query, phrase matching query, word string query, and through Boolean and, or combination. For example [Id00001 Motorcycle on November 01, 2018, track and violation Query] Query conditions are as follows:

List<Query> mustQueries = new ArrayList<Query>(); List<String> polygonList = Arrays. AsList (// Geo-fencing, forbidden motorcycle area"30.262348, 120.092127,"."30.311668, 120.079761,"."30.332413, 120.129371,",
    ...
);
String mId = "id00001"; Long timeStart = [2018-11-01 timestamp]; Long timeEnd = [2018-11-02 timestamp]; GeoPolygonQuery geoPolygonQuery = new GeoPolygonQuery(); geoPolygonQuery.setPoints(polygonList); geoPolygonQuery.setFieldName("pos");
mustQueries.add(geoPolygonQuery);

TermQuery termQuery = new TermQuery();
termQuery.setFieldName("mId");
termQuery.setTerm(ColumnValue.fromString(request.getmId()));
mustQueries.add(termQuery);

RangeQuery rangeQuery = new RangeQuery();
rangeQuery.setFieldName("timestamp");
rangeQuery.setFrom(ColumnValue.fromDouble(timeStart, true);
rangeQuery.setTo(ColumnValue.fromDouble(timeEnd, false);
mustQueries.add(rangeQuery);

BoolQuery boolQuery = new BoolQuery();
boolQuery.setMustQueries(mustQueries);Copy the code

Author: Tan Tan