How do I implement electronic geofencing by using Lindorm GanosBase - Lindorm

You can use Lindorm GanosBase together with the Lindorm streaming engine to build an electronic geofencing solution.

Background information

In vehicle management scenarios, electronic geofencing can be used to monitor the driving range of a vehicle and determine whether the vehicle deviates from the specified route. This way, you can better schedule and manage vehicles.

In electronic geofencing, some geofences are specified in advance. The geofence data does not frequently change and can be stored in a table in LindormTable. The position data (coordinates) of vehicles is uploaded in real time. Therefore, you can store vehicle position data to Apache Kafka and use the Lindorm streaming engine to subscribe to the data in Apache Kafka. This way, you can calculate the relationship between the positions of multiple vehicles and the specified geofences in real time.

The electronic geofencing solution describes in this topic supports in-memory indexes and parallel computing, which significantly improves computing efficiency.

Prerequisites

Lindorm GanosBase is activated. For more information, see Activate Lindorm GanosBase (free of charge).
The Lindorm streaming engine is activated for the instance. For more information, see Activate the streaming engine.
An ECS instance is created to read data and create computing tasks. The ECS instance must be deployed in the same VPC as your Lindorm instance. JDK 1.8 and later versions must be installed on the ECS instance. For more information, see Create a subscription instance on the Quick Launch tab.
The IP address of the ECS instance is added to the whitelist of the Lindorm instance. For more information, see Configure whitelists.

Procedure

Use Lindorm-cli to create a geofence table named regions and a result table named fresult. For more information, see Use Lindorm-cli to connect to and use LindormTable.

Create the geofence table.

CREATE TABLE regions(rID INT, rName VARCHAR, fence GEOMETRY, PRIMARY KEY(rID));

Insert geofence data.

INSERT INTO regions(rID, rName, fence) VALUES 
(1, 'SoHo', ST_GeomFromText('POLYGON((-74.00279525078275 40.72833625216264,-74.00547745979765 40.721929158663244,-74.00125029839018 40.71893680218994,-73.9957785919998 40.72521409075776,-73.9972377137039 40.72557184584898,-74.00279525078275 40.72833625216264))')),
(2, 'Chinatown', ST_GeomFromText('POLYGON((-73.99712367114876 40.71281582267133,-73.9901070123658 40.71336881907936,-73.99023575839851 40.71452359088633,-73.98976368961189 40.71554823078944,-73.99551434573982 40.717337246783735,-73.99480624255989 40.718491949759304,-73.99652285632942 40.719109951574,-73.99776740131233 40.7168005470334,-73.99903340396736 40.71727219249899,-74.00193018970344 40.71938642421256,-74.00409741458748 40.71688186545551,-74.00051398334358 40.71517415773184,-74.0004281526551 40.714377212470005,-73.99849696216438 40.713450141693166,-73.99748845157478 40.71405192594819,-73.99712367114876 40.71281582267133))')),
(3, 'Tribeca', ST_GeomFromText('POLYGON((-74.01091641815208 40.72583120006787,-74.01338405044578 40.71436586362705,-74.01370591552757 40.713617702123415,-74.00862044723533 40.711308107057235,-74.00194711120628 40.7194238654018,-74.01091641815208 40.72583120006787))'));

Create the result table.

CREATE TABLE fresult(uID VARCHAR, rName VARCHAR, rID INT, PRIMARY KEY (uID));

Write real-time data to the Lindorm streaming engine.
In this topic, an open source Apache Kafka script tool is used to connect to the Lindorm streaming engine as an example.
1. Run the following commands on the ECS instance to download and decompress the Apache Kafka script tool:
```
wget https://hbaseuepublic.oss-cn-beijing.aliyuncs.com/kafka_2.12-2.7.1.tgz
tar -zxf kafka_2.12-2.7.1.tgz
cd kafka_2.12-2.7.1
```
2. Create an Apache Kafka topic.
```
./bin/kafka-topics.sh --bootstrap-server <Lindorm streaming engine endpoint for Kafka> --topic log_topic --create
```
  You can obtain the Lindorm streaming engine endpoint for Kafka by performing the following operations: Log on to the Lindorm console. In the left-side navigation pane, click Database Connection. Then, click the Stream Engine tab and obtain the endpoint.
3. Write the real-time position data to the created Apache Kafka topic.
```
./bin/kafka-console-producer.sh --bootstrap-server <Lindorm streaming engine endpoint for Kafka> --topic log_topic
{"uID": "A", "x":"-74.00035", "y": "40.72432"}
{"uID": "B", "x":"-74.00239", "y": "40.71692"}
{"uID": "C", "x":"-74.00201", "y": "40.72563"}
{"uID": "D", "x":"-74.00158", "y": "40.72412"}
{"uID": "E", "x":"-73.99836", "y": "40.71588"}
{"uID": "F", "x":"-74.01015", "y": "40.71422"}
{"uID": "G", "x":"-73.99183", "y": "40.71451"}
{"uID": "H", "x":"-73.99595", "y": "40.71773"}
```
  You can run the following command to check whether the data is written to the Lindorm streaming engine: ./bin/kafka-console-consumer.sh --bootstrap-server <Lindorm streaming engine endpoint for Kafka> --topic logVehicle --from-beginning.
Submit a computing task in the Lindorm streaming engine.
1. On the ECS instance, run the following commands to download and decompress the Lindorm streaming engine client:
```
wget https://hbaseuepublic.oss-cn-beijing.aliyuncs.com/lindorm-sqlline-2.0.2.tar.gz
tar zxvf lindorm-sqlline-2.0.2.tar.gz
```
2. Go to the lindorm-sqlline-0.1.5/bin path, and then run the following command to connect to the Lindorm streaming engine:
```
./lindorm-sqlline -url <Lindorm streaming engine endpoint for SQL>
```
  You can obtain the Lindorm streaming engine endpoint for SQL by performing the following operations: Log on to the Lindorm console. In the left-side navigation pane, click Database Connection. Then, click the Stream Engine tab and obtain the endpoint.
3. Submit a computing task.
  Submit a computing task in the Lindorm streaming engine. The task reads real-time position data from the Apache Kafka topic, uses the ST_Contains function to calculate the geofence in which the real-time position is located based on the geofence data in the regions table, and then writes the results to the fresult table.
```
CREATE FJOB fenceFilter (
  LOAD MODULE ganos;
  -- Enable parallelism
  SET 'parallelism.default'='12';
  -- Create stream table
  CREATE TABLE carData(`uID` STRING, `x` DOUBLE, `y` DOUBLE, `proctime` AS PROCTIME()
    ) WITH (
    'connector'='kafka',
    'topic'='log_topic',
    'scan.startup.mode'='earliest-offset',
    'properties.bootstrap.servers'='<Lindorm streaming engine endpoint for SQL>',
    'format'='json'
  );
  -- Create area table
  CREATE TABLE regions (
    `rID` INT,
    `rName` STRING,
    `fence` GEOMETRY,
    PRIMARY KEY (`rID`) NOT ENFORCED
    ) WITH (
    'connector'='lindorm',
    'seedServer'='<ld-bp1ri5k784d1d****-proxy-lindorm.lindorm.rds.aliyuncs.com:30020>',
    'userName'='root',
    'password'='<your_passwd>',
    'tableName'='regions',
    'namespace'='default'
  );
  -- Create result table
  CREATE TABLE fresult (
    `uID` STRING,
    `rName` STRING,
    `rID` INT,
    PRIMARY KEY (`uID`) NOT ENFORCED
  ) WITH (
    'connector'='lindorm',
    'seedServer'='<ld-bp1ri5k784d1d****-proxy-lindorm.lindorm.rds.aliyuncs.com:30020>',
    'userName'='root',
    'password'='<your_passwd>',
    'tableName'='fresult',
    'namespace'='default'
  );
  -- Find the area that car located
  INSERT INTO fresult 
    SELECT A.uID, B.rName, B.rID
    FROM carData AS A 
    JOIN regions /*+ OPTIONS('geomHint'='fence:st_contains','geomIndex'='true','cacheTTLMs'='1800000') */ 
    FOR SYSTEM_TIME AS OF A.proctime AS B 
    ON B.fence=ST_MakePoint(A.x,A.y);
);
```
  Parameters:
  - properties.bootstrap.servers: The Lindorm streaming engine endpoint for Kafka.
  - seedServer: The LindormTable endpoint for SQL. You can obtain the endpoint by performing the following operations: Log on to the Lindorm console. In the left-side navigation pane, click Database Connection. Then, click the Wide Table Engine tab and obtain the endpoint. The port in the endpoint must be 30020. In addition, the jdbc:lindorm:table:url=http:// string must be removed from the endpoint obtained on the console. Example: 'seedServer'='ld-bp1ri5k784d1d****-proxy-lindorm.lindorm.rds.aliyuncs.com:30020'.
  - userName: The username used to connect to LindormTable. Default value: root.
  - password: The password used to connect to LindormTable. You can obtain the password by performing the following operations: Log on to the Lindorm console. In the left-side navigation pane, click Database Connection. Then, click the Wide Table Engine tab and obtain the endpoint.

Query data in the fresult table to obtain the results.

You can run the following command in LindormTable to query data in the fresult table:

SELECT * FROM fresult;

The following result is expected to be returned:

+-----+-----------+-----+
| uID |   rName   | rID |
+-----+-----------+-----+
| A   | SoHo      | 1   |
| B   | Chinatown | 2   |
| C   | SoHo      | 1   |
| D   | SoHo      | 1   |
| E   | Chinatown | 2   |
| F   | Tribeca   | 3   |
| G   | Chinatown | 2   |
| H   | Chinatown | 2   |
+-----+-----------+-----+