PolarDB:Real-time spatiotemporal computing based on GanosGanosBaseGanosGanosBaseGanosBase: electronic fence computing

About electronic fence

An electronic fence is a virtual geographic boundary, not a physical one, defined by polygons or polylines. When an application or device uses GPS, RFID, Wi-Fi, or cellular data, specific actions are triggered upon entering or exiting this boundary. These virtual perimeters can be established around various geographic locations, from small-scale areas like buildings and malls to larger regions such as cities or countries.

Scenarios

Electronic fences are applied in various real-life contexts:

• Traffic and Logistics

  In the traffic and logistics industry, electronic fences help monitor vehicle status. They can alert the system when a vehicle strays from a designated route or provide notifications and services for vehicle meetings in blind spots.

  

• No-Fly/No-Drive Zone Management

  Drones are restricted from flying within certain ranges around airports by setting up no-fly zones. If a drone enters this area, the electronic fence issues a warning to prevent aviation safety incidents.

• Emergency Events

  During severe weather conditions like typhoons or tornadoes, electronic fences activate to issue alerts when vehicles or ships enter affected areas.

• Marketing Scenarios

  Merchants can set electronic fences to automatically send marketing activities, such as coupon distribution, to customers upon their entry, enhancing marketing efficiency.

Other applications of electronic fences include IoT security, law enforcement, home automation, animal tracking, asset management, and human resources security management. Overall, real-time electronic fence technology is a powerful tool for businesses and developers to manage geospatial data effectively, improving location-based decision-making in terms of efficiency and accuracy.

• IoT security: If children or elderly individuals under surveillance leave a predefined geo-fence, the system will trigger an alarm to help prevent them from becoming lost.

• Law enforcement: Should a monitored individual exit their residence without authorization, the device will notify the appropriate authorities.

• Home automation or smart home: Upon the homeowner's smartphone entering the geo-fence range of their house, the thermostat may automatically adjust to the preset temperature, or the lights may turn on automatically.

• Animal tracking: Pet owners and farmers can create geo-fences that trigger alarms if animals move beyond established boundaries, enabling real-time tracking of their movements.

• Asset management: Network administrators can configure alerts for when company devices exit specified areas, allowing them to monitor their location and secure them against unauthorized use by individuals such as thieves.

• Human resources security management: In the event employees try to access restricted geo-fence areas, their smart cards will issue alerts to the security department.

In summary, real-time electronic fence technology offers enterprises and developers a robust tool to better manage and utilize geospatial data, enhancing the efficiency and precision of location-based decision-making.

Best Practices

GanosBase combines real-time electronic fences with Realtime Compute for Apache Flink, enhancing the Flink engine with spatial computing functions and memory space indexes for efficient, real-time electronic fence computations. Data sources can include those supported by Flink, such as Kafka. The electronic fence table is pre-stored in PolarDB and refreshed periodically, while the results of electronic fence operations are written back to PolarDB.

Preparations

To utilize the Realtime Compute for Apache Flink product, log on to the Realtime Compute console and purchase a Realtime Compute for Apache Flink instance. For detailed instructions, see Activate Realtime Compute for Apache Flink.

Register spatial computing functions

Upload the GanosBase spatial computing functions to the Realtime Compute for Apache Flink workspace. Realtime Compute for Apache Flink will then automatically register the relevant functions and display them in the function list on the left. To obtain the spatial computing functions, please contact us. For detailed instructions on uploading to the Realtime Compute for Apache Flink workspace, see Manage user-defined functions (UDF).

Electronic fence table

The electronic fence table is a pre-defined set of electronic fences, such as no-parking and no-drive zones, with data stored as geometric objects in PolarDB.

Generate electronic fence table

Generate 10,000 random polygon electronic fences in PolarDB. The method involves creating 10,000 random points and generating a buffer with a radius of 0.01 centered on these points.

-- Generate random floating-point numbers
CREATE OR REPLACE FUNCTION test_random_float(low float, high float)
    RETURNS float AS $$
BEGIN
    RETURN random() * (high-low) + low;
END;
$$ LANGUAGE 'plpgsql' STRICT;

-- Generate random points
CREATE OR REPLACE FUNCTION test_random_geogpoint(lowx float, highx float,
                                            lowy float, highy float)
    RETURNS geometry AS $$
BEGIN
    RETURN st_setsrid(st_point(test_random_float(lowx, highx), test_random_float(lowy, highy)),4326);
END;
$$ LANGUAGE 'plpgsql' STRICT;

-- Electronic fence table
CREATE TABLE IF NOT EXISTS geofencings (
    name VARCHAR(100) PRIMARY KEY,
    geofencing Geometry
);

-- Insert 10,000 random electronic fences
INSERT INTO geofencings SELECT 'g_' || s, test_random_geogpoint(129, 130, 49, 50) FROM generate_series(1, 10000) s;

-- Generate a buffer with a radius of approximately 1 km
Update geofencings set geofencing = st_buffer(geofencing, 0.01);

Register as Flink dimension table

In Flink SQL, register the electronic fence table in PolarDB as a dimension table for Realtime Compute for Apache Flink. The electronic fence information comes from the geofencings table in PolarDB. Use the CONTAINS predicate for join operations according to Flink's syntax.

CREATE TEMPORARY TABLE geofencings (
    name STRING NOT NULL,
    geofencing STRING NOT NULL,
    PRIMARY KEY (name) NOT ENFORCED
) WITH (
    'connector' = 'polardb',
    'jdbcUrl' = 'jdbc:postgresql://<yourHostname>:<yourPort>/<dbname>',
    'username' = '<yourUserName>',
    'password' = '<yourPassWord>',
    'query' = 'SELECT name, ST_AsText(geofencing) FROM geofencings;',
    'join.columnName' = 'geofencing',
    'join.predicate' = 'CONTAINS'
);

Calculation results

The results of real-time electronic fence calculations are also stored in PolarDB.

Calculation result table

Create a table in PolarDB to record the IDs of points that meet the electronic fence criteria, the fence name, and the points' coordinate information.

CREATE TABLE IF NOT EXISTS point_in_geofencing (
  id serial PRIMARY KEY, -- Primary key
  point_id VARCHAR(100), -- Point ID
  geofencing_name VARCHAR(100), -- Fence name
  point Geometry -- Point
);

Register as Flink table

In Flink SQL, register the result table in PolarDB as a result table for Flink. Then, write the query results into PolarDB using SQL.

CREATE TEMPORARY TABLE point_in_geofencing (
  point_id STRING NOT NULL,
  geofencing_name STRING NOT NULL,
  point STRING NOT NULL
) WITH (
    'connector' = 'polardb',
    'jdbcUrl' = 'jdbc:postgresql://<yourHostname>:<yourPort>/<dbname>',
    'username' = '<yourUserName>',
    'password' = '<yourPassWord>',
    'query' = 'INSERT INTO point_in_geofencing(point_id,geofencing_name,point) values(?,?,ST_GeomFromText(?));',
    'batchSize' = '100' -- Insert 100 records per batch
);

Simulate calculation source

The following Flink SQL simulates a data source table using datagen, generating 100,000 records per second, including randomly generated IDs, longitude, latitude, and timestamps.

CREATE TEMPORARY TABLE points (
  id STRING not null,
  lng DOUBLE not null, -- Longitude
  lat DOUBLE not null, -- Latitude
  proctime AS PROCTIME(), -- Timestamp
  PRIMARY KEY (id) NOT ENFORCED
) WITH (
  'connector' = 'datagen',
  'rows-per-second' = '100000',
  'fields.lng.kind' = 'random',
  'fields.lng.min' = '129',
  'fields.lng.max' = '130',
  'fields.lat.kind' = 'random',
  'fields.lat.min' = '49',
  'fields.lat.max' = '50',
  'fields.id.kind' = 'random',
  'fields.id.length' = '5'
);

Electronic fence calculation

Perform a Join operation in Flink SQL. Set the predicate for the Join to CONTAINS when defining the table, which executes the geofencings.geofencing CONTAINS ST_MakePoint(lng,lat) condition, and then writes the results into the point_in_geofencing table in PolarDB:

INSERT INTO point_in_geofencing  
SELECT points.id, geofencings.name, ST_MakePoint(lng,lat) 
FROM points 
JOIN geofencings FOR SYSTEM_TIME AS OF points.proctime 
ON geofencings.geofencing = ST_MakePoint(lng,lat);

Calculation performance

Monitoring data shows that the database write TPS is approximately 300 times per second, equating to 30,000 records per second. For a PolarDB enterprise edition cluster with 2 cores and 4 GB of memory, the CPU utilization is around 10%, indicating capacity for additional load.

Conclusion

Compared to traditional middleware or business code implementations, GanosBase offers a standardized spatiotemporal processing framework for large-scale mobile objects at the real-time computing layer, leading to significant improvements in computing efficiency and cost-effectiveness. Moving forward, GanosBase will continue to enhance real-time computing analysis capabilities for mobile objects, fostering a comprehensive shift of spatial information applications to online platforms.

Trial experience

You can visit the or PolarDB free trial pages to select the "cloud-native database PolarDB PostgreSQL edition" trial and explore the real-time spatiotemporal computing capabilities of GanosBase.