数据库实验手册系列:18 通过GIS轨迹相似伴随|时态分析|轨迹驻点识别等技术对拐卖、诱骗场景进行侦查
digoal约 30 分钟文章开始前推荐2个学习环境:
1、欢迎使用镜像快速体验PostgreSQL/DuckDB强大功能:《最好的PostgreSQL学习镜像》
2、欢迎使用云起实验室: 《免费体验PolarDB开源数据库》
3、PolarDB开源数据库内核、应用等学习图谱: https://www.aliyun.com/database/openpolardb/activity
标签
PostgreSQL , PolarDB , 数据库 , 教学
背景
欢迎数据库应用开发者参与贡献场景, 在此issue回复即可, 共同建设《沉浸式数据库学习教学素材库》, 帮助开发者用好数据库, 提升开发者职业竞争力, 同时为企业降本提效.
系列课程的核心目标是教大家怎么用好数据库, 而不是怎么运维管理数据库、怎么开发数据库内核. 所以面向的对象是数据库的用户、应用开发者、应用架构师、数据库厂商的产品经理、售前售后专家等角色.
本文的实验可以使用永久免费的阿里云云起实验室来完成.
如果你本地有docker环境也可以把镜像拉到本地来做实验:
x86_64机器使用以下docker image:
《amd64 image》
ARM机器使用以下docker image:
《arm64 image》
业务场景1 介绍: 通过GIS轨迹相似伴随|时态分析|轨迹驻点识别等技术对拐卖、诱骗场景进行侦查
拐卖、诱骗场景假设:
当我们手机开启定位功能和流量时, 可能会透过监控工具或移动APP, 定时上报手机的定位信息. 因此就形成了人的轨迹数据.
在被害前, 嫌疑人可能会与受害人进行接触(例如电话、短信、聊天工具等).
在拐卖、诱骗过程中, 受害人的轨迹 与 犯罪嫌疑人的轨迹有相似性.
在在拐卖、诱骗的中转过程中, 受害人的轨迹 与 转运人员的轨迹有近距点接触.
通过GIS轨迹相似伴随|时态分析 结合 嫌疑人名单进行撞库分析, 可以更高效的锁定犯罪嫌疑人, 对拐卖、诱骗案件侦破有帮助.
思考:
根据轨迹还可以分析在哪里停留过, 停留了多长时间? 每个时间点的速度是多少, 可能使用了什么交通工具等.
结合交通工具的轨迹, 可以碰撞出车牌等.
实现和对照
传统方法 设计和实验
传统数据库对GIS支持不够完善, 例如类型不够完善, 索引不够完善, GIS类型的数据处理不够完善(例如可能不支持 轨迹相似伴随|时态分析).
PolarDB|PG新方法1 设计和实验
准备测试环境和数据
创建postgis插件, 用于记录用户轨迹和轨迹计算.
create extension if not exists postgis;
1、用户表
create unlogged table users ( -- 为测试方便使用unlogged table
id int8 primary key, -- 用户ID
sfz text unique, -- 身份证号, 随机生成
info text, -- 其他信息, 随即填充
ts timestamp -- 时间戳
);
生成随机身份证号的函数
create or replace function gen_id(
a date,
b date
)
returns text as $$
select lpad((random()*99)::int::text, 2, '0') ||
lpad((random()*99)::int::text, 2, '0') ||
lpad((random()*99)::int::text, 2, '0') ||
to_char(a + (random()*(b-a))::int, 'yyyymmdd') ||
lpad((random()*99)::int::text, 2, '0') ||
random()::int ||
(case when random()*10 >9 then 'X' else (random()*9)::int::text end ) ;
$$ language sql strict;
生成10万测试用户数据.
insert into users select generate_series(1,100000), gen_id('1900-01-01', '2023-10-01'), md5(random()::text), clock_timestamp();
2、设计一张用户轨迹表, 记录每个用户在任意时刻的位置信息
create unlogged table user_pos ( -- 为测试方便使用unlogged table
id serial8 primary key, -- 主键
uid int8, -- 用户ID
pos geometry, -- 位置信息
ts timestamp, -- 时间
unique(uid,ts) -- 为了测试方便, 同一个轨迹每个point 的M值(位置序号)都不一样. 一样的序号将导致轨迹无效(开源版本PostGIS限制.)
);
参考各省份经纬度数据: https://blog.csdn.net/esa72ya/article/details/114642127
时间转换方法:
postgres=# select extract(epoch from '2023-10-01'::date);
extract
------------
1696118400
(1 row)
postgres=# select extract(epoch from '2023-10-08'::date);
extract
------------
1696723200
(1 row)
postgres=# select to_timestamp(1696118400);
to_timestamp
------------------------
2023-10-01 00:00:00+00
(1 row)
postgres=# select to_timestamp(1696723200);
to_timestamp
------------------------
2023-10-08 00:00:00+00
(1 row)
生成10万用户ID: 1-100000在7天 (2023-10-01, 2023-10-08) 的轨迹, 每个用户平均每天100个位置, 得到7000万条位置数据. 经度范围 118-122, 纬度范围 26-31
-- 经纬度取到小数点后4位
-- 位置序号使用时间戳
vi t1.sql \set uid random(1,100000)
\set lon random(1180000,1220000)
\set lat random(260000,310000)
\set ts random(1696118400,1696723200)
insert into user_pos (uid,pos,ts) values (:uid, ST_SetSRID(ST_MakePointM(:lon/10000.0, :lat/10000.0, :ts),4326), to_timestamp(:ts)) on conflict do nothing;
pgbench -M prepared -n -r -P 1 -f ./t1.sql -c 7 -j 7 -t 10000000
transaction type: ./t1.sql
scaling factor: 1
query mode: prepared
number of clients: 7
number of threads: 7
number of transactions per client: 10000000
number of transactions actually processed: 70000000/70000000
latency average = 0.114 ms
latency stddev = 1.000 ms
initial connection time = 20.032 ms
tps = 60838.762552 (without initial connection time)
statement latencies in milliseconds:
0.000 \set uid random(1,100000)
0.000 \set lon random(1180000,1220000)
0.000 \set lat random(260000,310000)
0.000 \set ts random(1696118400,1696723200)
0.114 insert into user_pos (uid,pos,ts) values (:uid, ST_SetSRID(ST_MakePointM(:lon/10000.0, :lat/10000.0, :ts),4326), to_timestamp(:
生成100个用户ID: 1-100 在 (2023-10-01, 2023-10-08) 密集的每个用户1440条轨迹/天, 即14.4*7万条密集轨迹. 经度范围121.0-121.5, 纬度范围 30-30.5
-- 经纬度取到小数点后4位
-- 位置序号使用时间戳
vi t2.sql \set uid random(1,100)
\set lon random(1210000,1215000)
\set lat random(300000,305000)
\set ts random(1696118400,1696723200)
insert into user_pos (uid,pos,ts) values (:uid, ST_SetSRID(ST_MakePointM(:lon/10000.0, :lat/10000.0, :ts),4326), to_timestamp(:ts)) on conflict do nothing;
pgbench -M prepared -n -r -P 1 -f ./t2.sql -c 7 -j 7 -t 144000
transaction type: ./t2.sql
scaling factor: 1
query mode: prepared
number of clients: 7
number of threads: 7
number of transactions per client: 144000
number of transactions actually processed: 1008000/1008000
latency average = 0.063 ms
latency stddev = 0.098 ms
initial connection time = 17.631 ms
tps = 110956.369050 (without initial connection time)
statement latencies in milliseconds:
0.000 \set uid random(1,100)
0.000 \set lon random(1210000,1215000)
0.000 \set lat random(300000,305000)
0.000 \set ts random(1696118400,1696723200)
0.063 insert into user_pos (uid,pos,ts) values (:uid, ST_SetSRID(ST_MakePointM(:lon/10000.0, :lat/10000.0, :ts),4326), to_timestamp(:
3、嫌疑人表, 存储上过嫌疑人名单的旧犯信息
create unlogged table users_class1 ( -- 为测试方便使用unlogged table
id int8 primary key, -- 用户ID
sfz text unique, -- 身份证号, 随机生成
info text, -- 其他信息, 随即填充
ts timestamp -- 时间戳
);
假设有1000位上过嫌疑人名单的旧犯, ID: 51-1050 . 生成数据:
insert into users_class1 select * from users where id>=51 and id<=1050;
数据情况如下:
postgres=# select count(*) from user_pos;
count
----------
70958079
(1 row) postgres=# \dt+
List of relations
Schema | Name | Type | Owner | Persistence | Access method | Size | Description
--------+-----------------+-------+----------+-------------+---------------+---------+-------------
public | spatial_ref_sys | table | postgres | permanent | heap | 6936 kB |
public | user_pos | table | postgres | unlogged | heap | 6301 MB |
public | users | table | postgres | unlogged | heap | 9928 kB |
public | users_class1 | table | postgres | unlogged | heap | 152 kB |
(4 rows)
postgres=# \di+
List of relations
Schema | Name | Type | Owner | Table | Persistence | Access method | Size | Description
--------+----------------------+-------+----------+-----------------+-------------+---------------+---------+-------------
public | spatial_ref_sys_pkey | index | postgres | spatial_ref_sys | permanent | btree | 208 kB |
public | user_pos_pkey | index | postgres | user_pos | unlogged | btree | 1520 MB |
public | user_pos_uid_ts_key | index | postgres | user_pos | unlogged | btree | 2690 MB |
public | users_class1_pkey | index | postgres | users_class1 | unlogged | btree | 48 kB |
public | users_class1_sfz_key | index | postgres | users_class1 | unlogged | btree | 72 kB |
public | users_pkey | index | postgres | users | unlogged | btree | 2216 kB |
public | users_sfz_key | index | postgres | users | unlogged | btree | 4848 kB |
(7 rows)
实验通过GIS轨迹相似伴随|时态分析对拐卖、诱骗场景进行侦查
假设受害人ID为1 (根据上面的测试数据选择, 有某一天的密集轨迹, 不在嫌疑人ID范围.).
假设受害人 2023-10-08 00:00:00 失联.
1 在拐卖、诱骗过程中, 押运受害人的嫌疑人, 和受害人必定有轨迹伴随特征
将失联前最后1天的数据点组合成轨迹. 通过轨迹计算出与受害人有过接触的人的ID. 以及:
在哪些时间点或时间段接触过? 即伴随时间段 (开源版本PostGIS只能得到1个点, 有兴趣的同学请继续研究Ganos插件. )
在哪些位置接触过? 即伴随路线和相应长度 (开源版本PostGIS只能得到1个点(真要干得把轨迹拆成小段, 计算每一段的, 非常麻烦), 不能得到多个接触点, 有兴趣的同学请继续研究Ganos插件. )
接触时的距离是多少? 即伴随距离 (开源版本PostGIS只能得到1个点的接触距离, 不能得到多个接触点, 有兴趣的同学请继续研究Ganos插件. )
在伴随过程中, 在什么时间、什么位置驻留过? 分别驻留了多长时间? (开源版本PostGIS无法计算, 有兴趣的同学请继续研究Ganos插件. )
PostGIS 轨迹计算函数如下:
ST_MakeLine, 将点聚合为轨迹
boolean ST_IsValidTrajectory(geometry line), Tests if a geometry encodes a valid trajectory. A valid trajectory is represented as a LINESTRING with measures (M values). The measure values must increase from each vertex to the next. 是否为有效轨迹
float8 ST_ClosestPointOfApproach(geometry track1, geometry track2), Returns the smallest measure at which points interpolated along the given trajectories are at the smallest distance. 两条轨迹最近时的M值
geometry ST_LocateAlong(geometry geom_with_measure, float8 measure, float8 offset = 0), Returns the location(s) along a measured geometry that have the given measure values. The result is a Point or MultiPoint. 输入M值, 返回该M值对应的位置
float8 ST_DistanceCPA(geometry track1, geometry track2), Returns the minimum distance two moving objects have ever been each other. 两条轨迹最近时的距离, 单位取决于轨迹使用的坐标系
boolean ST_CPAWithin(geometry track1, geometry track2, float8 dist), Tests whether two moving objects have ever been closer than the specified distance. 两条轨迹的最近距离是否小于给定值, 值的单位取决于轨迹使用的坐标系
float ST_DistanceSphere(geometry geomlonlatA, geometry geomlonlatB, float8 radius=6371008), Returns minimum distance in meters between two lon/lat points. Uses a spherical earth and radius derived from the spheroid defined by the SRID. Faster than ST_DistanceSpheroid, but less accurate. 将地球当成正球, 计算两个点的球面距离, 单位米.
float ST_DistanceSpheroid(geometry geomlonlatA, geometry geomlonlatB, spheroid measurement_spheroid=WGS84), Returns minimum distance in meters between two lon/lat geometries given a particular spheroid. 根据所处地域选择srid, 可以更精确的计算两个点的球面距离, 默认采用WGS84, 单位米.
st_astext, Return the Well-Known Text (WKT) representation of the geometry/geography without SRID metadata. wkT形式geo类型显示.
ST_Transform, Return a new geometry with coordinates transformed to a different spatial reference system. 转换geo坐标系.
st_setsrid, Set the SRID on a geometry. 设置srid
PostGIS SQL 例子如下:
with
-- a 受害人(ID=1) 10.7号轨迹
a as (select uid, ST_MakeLine(pos order by ts) as tra from user_pos where uid=1 and ts >= '2023-10-07' and ts < '2023-10-08' group by 1),
-- b 除受害人以外, 所有人10.7号轨迹
b as (select uid, ST_MakeLine(pos order by ts) as tra from user_pos where uid <> 1 and ts >= '2023-10-07' and ts < '2023-10-08' group by 1)
select
a.uid,
b.uid,
-- 两条轨迹相隔最近时的M值, 这里M代表时间戳
ST_ClosestPointOfApproach(a.tra, b.tra) m,
-- 将M转换为时间戳
to_timestamp(ST_ClosestPointOfApproach(a.tra, b.tra)) ts,
-- 两条轨迹最近时轨迹a的位置
st_astext(ST_LocateAlong(a.tra, ST_ClosestPointOfApproach(a.tra, b.tra))) a,
-- 两条轨迹最近时轨迹b的位置
st_astext(ST_LocateAlong(b.tra, ST_ClosestPointOfApproach(a.tra, b.tra))) b,
-- 使用球坐标计算两条轨迹最近时的距离, 单位米
ST_DistanceSpheroid(ST_LocateAlong(a.tra, ST_ClosestPointOfApproach(a.tra, b.tra)), ST_LocateAlong(b.tra, ST_ClosestPointOfApproach(a.tra, b.tra))) tra_meters,
-- 不管轨迹M值, 以纯线段的方式计算2条轨迹的距离, 单位米
ST_DistanceSpheroid(a.tra, b.tra) line_meters,
-- 由于轨迹使用4326坐标系, 不转换坐标系, 使用ST_DistanceCPA计算两条轨迹最近时的距离得到的是degree单位的值
ST_DistanceCPA(a.tra, b.tra) dis_by_srid_4326,
-- 将几何对象转换为适用于米的投影坐标参考系统
-- 转换为BD-09（百度坐标系）的EPSG代码是EPSG：3857, GCJ-02和BD-09是非标准的坐标系，其转换结果可能存在一定的误差。
ST_DistanceCPA(ST_Transform(a.tra,3857), ST_Transform(b.tra,3857)) dis_by_srid_3857,
-- 接触点距离是否在5米以内?
ST_DistanceSpheroid(ST_LocateAlong(a.tra, ST_ClosestPointOfApproach(a.tra, b.tra)), ST_LocateAlong(b.tra, ST_ClosestPointOfApproach(a.tra, b.tra))) < 5 dist_within_5_meters
from a,b limit 10;
-[ RECORD 1 ]--------+--------------------------------------------------------------------------
uid | 1
uid | 2
m | 1696697311.3036995
ts | 2023-10-07 16:48:31.3037+00
a | MULTIPOINT M ((121.31658534763336 30.187193591499327 1696697311.3036995))
b | MULTIPOINT M ((121.31654815735817 30.18717094370127 1696697311.3036995))
tra_meters | 4.373915142071747
line_meters | 0.016491753952581085
dis_by_srid_4326 | 4.354353360589861e-05
dis_by_srid_3857 | 0.36991291409089133
dist_within_5_meters | t
-[ RECORD 2 ]--------+--------------------------------------------------------------------------
uid | 1
uid | 3
m | 1696703047.2971663
ts | 2023-10-07 18:24:07.297166+00
a | MULTIPOINT M ((121.1313048864607 30.35598715900421 1696703047.2971663))
b | MULTIPOINT M ((121.13124493157522 30.3559373743466 1696703047.2971663))
tra_meters | 7.980249939657012
line_meters | 0.014040196551025192
dis_by_srid_4326 | 7.79300994034703e-05
dis_by_srid_3857 | 2.3202176959047467
dist_within_5_meters | f
-[ RECORD 3 ]--------+--------------------------------------------------------------------------
uid | 1
uid | 4
m | 1696714926.6986308
ts | 2023-10-07 21:42:06.698631+00
a | MULTIPOINT M ((121.22373551545142 30.26627129611969 1696714926.6986308))
b | MULTIPOINT M ((121.22394794253111 30.266598629581928 1696714926.6986308))
tra_meters | 41.64862299149548
line_meters | 0.005156185117280435
dis_by_srid_4326 | 0.0003902210394268254
dis_by_srid_3857 | 47.17957374737505
dist_within_5_meters | f
-[ RECORD 4 ]--------+--------------------------------------------------------------------------
uid | 1
uid | 5
m | 1696705262.5495796
ts | 2023-10-07 19:01:02.54958+00
a | MULTIPOINT M ((121.37177815465725 30.27131101107496 1696705262.5495796))
b | MULTIPOINT M ((121.37172153333307 30.271289392630607 1696705262.5495796))
tra_meters | 5.952059806803198
line_meters | 0.02082467099524442
dis_by_srid_4326 | 6.0608015048630094e-05
dis_by_srid_3857 | 10.781228219837937
dist_within_5_meters | f
-[ RECORD 5 ]--------+--------------------------------------------------------------------------
uid | 1
uid | 6
m | 1696638810.0620923
ts | 2023-10-07 00:33:30.062092+00
a | MULTIPOINT M ((121.12317522716522 30.266258507394788 1696638810.0620923))
b | MULTIPOINT M ((121.12303405156064 30.26638752446608 1696638810.0620923))
tra_meters | 19.7259144659703
line_meters | 0.005841662369415527
dis_by_srid_4326 | 0.00019124839491914186
dis_by_srid_3857 | 4.96812051205451
dist_within_5_meters | f
-[ RECORD 6 ]--------+--------------------------------------------------------------------------
uid | 1
uid | 7
m | 1696647555.7661612
ts | 2023-10-07 02:59:15.766161+00
a | MULTIPOINT M ((121.04340661763372 30.29411974696469 1696647555.7661612))
b | MULTIPOINT M ((121.0434242972109 30.294122692171733 1696647555.7661612))
tra_meters | 1.731839173153422
line_meters | 0.003999721077257994
dis_by_srid_4326 | 1.7923216457506776e-05
dis_by_srid_3857 | 13.363477510814603
dist_within_5_meters | t
-[ RECORD 7 ]--------+--------------------------------------------------------------------------
uid | 1
uid | 8
m | 1696667418.9117382
ts | 2023-10-07 08:30:18.911738+00
a | MULTIPOINT M ((121.4588447823869 30.16601897884593 1696667418.9117382))
b | MULTIPOINT M ((121.4590019390485 30.165965665920574 1696667418.9117382))
tra_meters | 16.250915960631275
line_meters | 0.018480064946785075
dis_by_srid_4326 | 0.00016595325936695634
dis_by_srid_3857 | 11.054858168555002
dist_within_5_meters | f
-[ RECORD 8 ]--------+--------------------------------------------------------------------------
uid | 1
uid | 9
m | 1696722730.1260679
ts | 2023-10-07 23:52:10.126068+00
a | MULTIPOINT M ((121.32588992220283 30.295322107986475 1696722730.1260679))
b | MULTIPOINT M ((121.32568354031474 30.295544315750558 1696722730.1260679))
tra_meters | 31.638203036955655
line_meters | 0.014506525287765921
dis_by_srid_4326 | 0.00030326518781932817
dis_by_srid_3857 | 40.15213564699135
dist_within_5_meters | f
-[ RECORD 9 ]--------+--------------------------------------------------------------------------
uid | 1
uid | 10
m | 1696705787.834826
ts | 2023-10-07 19:09:47.834826+00
a | MULTIPOINT M ((121.3880368950087 30.27887733767146 1696705787.834826))
b | MULTIPOINT M ((121.3881332828405 30.278952208693173 1696705787.834826))
tra_meters | 12.44574769306065
line_meters | 0.04376000475557697
dis_by_srid_4326 | 0.00012205033392033374
dis_by_srid_3857 | 15.018266874472049
dist_within_5_meters | f
-[ RECORD 10 ]-------+--------------------------------------------------------------------------
uid | 1
uid | 11
m | 1696666719.784847
ts | 2023-10-07 08:18:39.784847+00
a | MULTIPOINT M ((121.01649247573907 30.253476133300673 1696666719.784847))
b | MULTIPOINT M ((121.01654042665702 30.25344092913426 1696666719.784847))
tra_meters | 6.043744124177614
line_meters | 0.014478272570600022
dis_by_srid_4326 | 5.948633343239396e-05
dis_by_srid_3857 | 11.82060975306182
dist_within_5_meters | f
得到了10月7日与受害人有过近距离接触(1米以内)的信息:
谁? b.uid
什么时间? ts
什么地点? apos, bpos
接触点距离多近? tra_meters
PS: 如果使用ganos可以得到更多的信息, 当我们限定小于N米距离表示接触, 返回的是数组, 而不仅仅是一个点的信息.
数组提供了更多的信息, 例如伴随路径,时间等. 有兴趣的同学请继续研究Ganos插件.
with
-- a 受害人(ID=1) 10.7号轨迹
a as (select uid, ST_MakeLine(pos order by ts) as tra from user_pos where uid=1 and ts >= '2023-10-07' and ts < '2023-10-08' group by 1),
-- b 除受害人以外, 所有人10.7号轨迹
b as (select uid, ST_MakeLine(pos order by ts) as tra from user_pos where uid <> 1 and ts >= '2023-10-07' and ts < '2023-10-08' group by 1)
select
a.uid,
b.uid,
-- 两条轨迹相隔最近时的M值, 这里M代表时间戳
ST_ClosestPointOfApproach(a.tra, b.tra) m,
-- 将M转换为时间戳
to_timestamp(ST_ClosestPointOfApproach(a.tra, b.tra)) ts,
-- 两条轨迹最近时轨迹a的位置
st_astext(ST_LocateAlong(a.tra, ST_ClosestPointOfApproach(a.tra, b.tra))) apos,
-- 两条轨迹最近时轨迹b的位置
st_astext(ST_LocateAlong(b.tra, ST_ClosestPointOfApproach(a.tra, b.tra))) bpos,
-- 使用球坐标计算两条轨迹最近时的距离, 单位米
ST_DistanceSpheroid(ST_LocateAlong(a.tra, ST_ClosestPointOfApproach(a.tra, b.tra)), ST_LocateAlong(b.tra, ST_ClosestPointOfApproach(a.tra, b.tra))) tra_meters,
-- 不管轨迹M值, 以纯线段的方式计算2条轨迹的距离, 单位米
ST_DistanceSpheroid(a.tra, b.tra) line_meters,
-- 由于轨迹使用4326坐标系, 不转换坐标系, 使用ST_DistanceCPA计算两条轨迹最近时的距离得到的是degree单位的值
ST_DistanceCPA(a.tra, b.tra) dis_by_srid_4326,
-- 将几何对象转换为适用于米的投影坐标参考系统
-- 转换为BD-09（百度坐标系）的EPSG代码是EPSG：3857, GCJ-02和BD-09是非标准的坐标系，其转换结果可能存在一定的误差。
ST_DistanceCPA(ST_Transform(a.tra,3857), ST_Transform(b.tra,3857)) dis_by_srid_3857,
-- 接触点距离是否在1米以内?
ST_DistanceSpheroid(ST_LocateAlong(a.tra, ST_ClosestPointOfApproach(a.tra, b.tra)), ST_LocateAlong(b.tra, ST_ClosestPointOfApproach(a.tra, b.tra))) < 1 dist_within_1_meters
from a,b
where
-- 接触点距离在1米以内
ST_DistanceSpheroid(ST_LocateAlong(a.tra, ST_ClosestPointOfApproach(a.tra, b.tra)), ST_LocateAlong(b.tra, ST_ClosestPointOfApproach(a.tra, b.tra))) < 1
;
uid | uid | m | ts | apos | bpos
| tra_meters | line_meters | dis_by_srid_4326 | dis_by_srid_3857 | dist_within_1_meters
-----+-------+--------------------+-------------------------------+---------------------------------------------------------------------------+-----------------------------------------------------------
----------------+----------------------+----------------------+------------------------+---------------------+----------------------
1 | 16 | 1696711287.0637639 | 2023-10-07 20:41:27.063764+00 | MULTIPOINT M ((121.179514921611 30.202250277204946 1696711287.0637639)) | MULTIPOINT M ((121.17951589281729 30.202247724543867 16967
11287.0637639)) | 0.29802977629678595 | 0.017088185140647746 | 2.7311756121174723e-06 | 9.851859586106789 | t
1 | 23 | 1696673079.4389594 | 2023-10-07 10:04:39.438959+00 | MULTIPOINT M ((121.19259837793997 30.102287001200715 1696673079.4389594)) | MULTIPOINT M ((121.19259184264052 30.1022845365676 1696673
079.4389594)) | 0.6866180882664799 | 0 | 6.984594030331777e-06 | 0.331713166532339 | t
1 | 24 | 1696694100.1699324 | 2023-10-07 15:55:00.169932+00 | MULTIPOINT M ((121.31577521460606 30.12400778947977 1696694100.1699324)) | MULTIPOINT M ((121.31577443009843 30.124007099270315 16966
94100.1699324)) | 0.10756191927499771 | 0.00909950209249224 | 1.0449105905812406e-06 | 0.5964301291912145 | t
1 | 52 | 1696687781.956004 | 2023-10-07 14:09:41.956004+00 | MULTIPOINT M ((121.32612384229635 30.38429201588597 1696687781.956004)) | MULTIPOINT M ((121.32612636452372 30.38429238351746 169668
7781.956004)) | 0.24581924311992584 | 0.045892882767090054 | 2.5488788815666763e-06 | 0.641131518765304 | t
1 | 66 | 1696710449.2383232 | 2023-10-07 20:27:29.238323+00 | MULTIPOINT M ((121.19927072257184 30.224691424142552 1696710449.2383232)) | MULTIPOINT M ((121.1992771364212 30.22468591556549 1696710
449.2383232)) | 0.8684180012921737 | 0.002397637444492437 | 8.454696012053516e-06 | 0.3008637252343871 | t
1 | 86 | 1696718794.016806 | 2023-10-07 22:46:34.016806+00 | MULTIPOINT M ((121.14680299750056 30.3187883438485 1696718794.016806)) | MULTIPOINT M ((121.14679871755352 30.318787521913755 16967
18794.016806)) | 0.4215941825998081 | 0.011436598849449354 | 4.358155945315723e-06 | 1.9039684461333242 | t
1 | 274 | 1696663952.4315116 | 2023-10-07 07:32:32.431512+00 | MULTIPOINT M ((121.5712085725069 28.75975077080727 1696663952.4315116)) | MULTIPOINT M ((121.57121841057288 28.75975119167405 169666
3952.4315116)) | 0.9619502356195184 | 0.7167056969789865 | 9.844668142077385e-06 | 87.66814056462134 | t
1 | 952 | 1696704013.8419132 | 2023-10-07 18:40:13.841913+00 | MULTIPOINT M ((121.02689413772583 29.755560576553346 1696704013.8419132)) | MULTIPOINT M ((121.02689975697152 29.755556990628868 16967
04013.8419132)) | 0.6733482055635507 | 2.346209489731119 | 6.665941503573243e-06 | 488.70467529515736 | t
1 | 1327 | 1696700397.318303 | 2023-10-07 17:39:57.318303+00 | MULTIPOINT M ((119.1528412981224 29.633691846618653 1696700397.318303)) | MULTIPOINT M ((119.15284157641521 29.633690893539352 16967
00397.318303)) | 0.10902846597912982 | 1.2128953826930036 | 9.928781387071022e-07 | 1309.0723316207764 | t
1 | 2695 | 1696647326.8443363 | 2023-10-07 02:55:26.844336+00 | MULTIPOINT M ((121.92014894145804 27.304754500296355 1696647326.8443363)) | MULTIPOINT M ((121.92015071659038 27.304754978416668 16966
47326.8443363)) | 0.18352642902869487 | 0.9494438906882574 | 1.8383937871086966e-06 | 7.784088226800597 | t
1 | 3364 | 1696641956.2189903 | 2023-10-07 01:25:56.21899+00 | MULTIPOINT M ((121.41641340264665 30.021485305571048 1696641956.2189903)) | MULTIPOINT M ((121.41641216383304 30.02148641679296 169664
1956.2189903)) | 0.17162380732232013 | 2.3485449598985473 | 1.6641733275123874e-06 | 550.393623662213 | t
1 | 3789 | 1696663613.1192298 | 2023-10-07 07:26:53.11923+00 | MULTIPOINT M ((121.40490159148867 30.200234662606675 1696663613.1192298)) | MULTIPOINT M ((121.40489893844436 30.20023209944578 169666
3613.1192298)) | 0.38209898283152377 | 3.872682948316315 | 3.688961601972608e-06 | 46.000064987674314 | t
1 | 4068 | 1696682013.1714096 | 2023-10-07 12:33:33.17141+00 | MULTIPOINT M ((119.93563328628541 28.850422898067126 1696682013.1714096)) | MULTIPOINT M ((119.93562673342112 28.850428047415246 16966
82013.1714096)) | 0.8570762232397039 | 1.8629634196314546 | 8.334010286706677e-06 | 735.0353589180091 | t
1 | 4099 | 1696669913.0897362 | 2023-10-07 09:11:53.089736+00 | MULTIPOINT M ((120.29306461217404 29.688800785332756 1696669913.0897362)) | MULTIPOINT M ((120.29306242512594 29.688803564647078 16966
69913.0897362)) | 0.37379045863241284 | 0.6226158277989603 | 3.5366321082731683e-06 | 1.136634126525745 | t
1 | 4528 | 1696663512.3624809 | 2023-10-07 07:25:12.362481+00 | MULTIPOINT M ((121.18744072338018 30.055323946348103 1696663512.3624809)) | MULTIPOINT M ((121.18744373676509 30.055320590695686 16966
63512.3624809)) | 0.47203288535668314 | 0.0796265326726013 | 4.510087007300477e-06 | 63.61717791178923 | t
1 | 5023 | 1696660785.0599978 | 2023-10-07 06:39:45.059998+00 | MULTIPOINT M ((120.31567452233169 30.33319997797012 1696660785.0599978)) | MULTIPOINT M ((120.31567394096454 30.333205461349326 16966
60785.0599978)) | 0.6104422435811728 | 6.606695991314753 | 5.51411236933417e-06 | 18.08601410579314 | t
1 | 7206 | 1696668790.211112 | 2023-10-07 08:53:10.211112+00 | MULTIPOINT M ((120.34997429162875 28.534545109640586 1696668790.211112)) | MULTIPOINT M ((120.34996892932537 28.534548239535944 16966
68790.211112)) | 0.6290966371161706 | 0.12981873625900192 | 6.208903406239915e-06 | 112.07658805087544 | t
1 | 8632 | 1696650655.8955953 | 2023-10-07 03:50:55.895595+00 | MULTIPOINT M ((121.30070905862821 30.16942004348692 1696650655.8955953)) | MULTIPOINT M ((121.30070950977675 30.16941753082198 169665
0655.8955953)) | 0.2819116116153599 | 0.3913363732213239 | 2.55284548387773e-06 | 632.2620269064657 | t
1 | 9696 | 1696642867.062975 | 2023-10-07 01:41:07.062975+00 | MULTIPOINT M ((119.40510418914995 29.055729341015063 1696642867.062975)) | MULTIPOINT M ((119.40510654029482 29.055725577834817 16966
42867.062975)) | 0.47581336309293903 | 3.893366703063385 | 4.437269238523562e-06 | 5.288463000900437 | t
1 | 11896 | 1696721585.6198063 | 2023-10-07 23:33:05.619806+00 | MULTIPOINT M ((121.07395726798376 29.878972859509787 1696721585.6198063)) | MULTIPOINT M ((121.07395331916851 29.87898024074505 169672
1585.6198063)) | 0.9027680653917985 | 0.6255647656136949 | 8.371127345071201e-06 | 64.6081576523878 | t
1 | 13063 | 1696649954.7468536 | 2023-10-07 03:39:14.746854+00 | MULTIPOINT M ((121.20000680392235 30.105817676956953 1696649954.7468536)) | MULTIPOINT M ((121.2000091227254 30.10581209936282 1696649
954.7468536)) | 0.6574532265314424 | 1.9329580861436884 | 6.040397579310141e-06 | 531.6967470710825 | t
1 | 13587 | 1696711803.128927 | 2023-10-07 20:50:03.128927+00 | MULTIPOINT M ((119.07367600150013 29.85209721119952 1696711803.128927)) | MULTIPOINT M ((119.07367828417577 29.852101037245045 16967
11803.128927)) | 0.478045538186566 | 2.090900002113868 | 4.455247558710363e-06 | 305.4389715213877 | t
1 | 14041 | 1696658132.7981834 | 2023-10-07 05:55:32.798184+00 | MULTIPOINT M ((121.11449072455301 30.032905229462518 1696658132.7981834)) | MULTIPOINT M ((121.11448320610805 30.03290622711798 169665
8132.7981834)) | 0.7335717485850729 | 0.3177401891905778 | 7.584347753932764e-06 | 0.6588516977908566 | t
1 | 14622 | 1696674662.383303 | 2023-10-07 10:31:02.383303+00 | MULTIPOINT M ((121.07033762680425 29.952296948501136 1696674662.383303)) | MULTIPOINT M ((121.07033441427465 29.95229793209586 169667
4662.383303)) | 0.328722362310889 | 3.086186293730716 | 3.3597306224091628e-06 | 44.01831471657201 | t
1 | 15953 | 1696663955.8399303 | 2023-10-07 07:32:35.83993+00 | MULTIPOINT M ((121.6488664509217 27.19355037475692 1696663955.8399303)) | MULTIPOINT M ((121.64886419408023 27.193550105075076 16966
63955.8399303)) | 0.22560556491183936 | 2.491284217827418 | 2.2728894996621646e-06 | 74.97603981523513 | t
1 | 16797 | 1696718896.481428 | 2023-10-07 22:48:16.481428+00 | MULTIPOINT M ((121.17288679807335 30.311123260317743 1696718896.481428)) | MULTIPOINT M ((121.17288692095798 30.311125603308906 16967
18896.481428)) | 0.2600074038834392 | 1.210765255876662 | 2.3462112896858327e-06 | 70.09228534239557 | t
1 | 18097 | 1696659096.7449162 | 2023-10-07 06:11:36.744916+00 | MULTIPOINT M ((120.53668731952148 29.7592912382516 1696659096.7449162)) | MULTIPOINT M ((120.53668838062474 29.759289984552908 16966
59096.7449162)) | 0.17275798154757635 | 1.9498709588371512 | 1.642464899633402e-06 | 36.233765093536924 | t
1 | 18882 | 1696639805.7521462 | 2023-10-07 00:50:05.752146+00 | MULTIPOINT M ((119.6124845246315 28.64298624162674 1696639805.7521462)) | MULTIPOINT M ((119.61248300951765 28.642988010927912 16966
39805.7521462)) | 0.24575593461307804 | 5.802797653265205 | 2.322357829352477e-06 | 143.61260299577694 | t
1 | 19457 | 1696656978.8593462 | 2023-10-07 05:36:18.859346+00 | MULTIPOINT M ((121.10964313701689 30.402930613937972 1696656978.8593462)) | MULTIPOINT M ((121.10963806079896 30.40293190715813 169665
6978.8593462)) | 0.5084262779846881 | 3.807638967543638 | 5.238359074282207e-06 | 42.12943050167923 | t
1 | 19678 | 1696650788.7621696 | 2023-10-07 03:53:08.76217+00 | MULTIPOINT M ((120.84112391397 30.047119638786313 1696650788.7621696)) | MULTIPOINT M ((120.84112381650385 30.047125125868476 16966
50788.7621696)) | 0.6083334446277675 | 8.728381673141044 | 5.4879466491573395e-06 | 60.861701682359524 | t
1 | 21128 | 1696708386.1945717 | 2023-10-07 19:53:06.194572+00 | MULTIPOINT M ((120.9928735683901 29.87562772511755 1696708386.1945717)) | MULTIPOINT M ((120.99286666991779 29.875633057211466 16967
08386.1945717)) | 0.8907831312494247 | 1.0357927351704053 | 8.71894615022157e-06 | 73.75950712682116 | t
1 | 21551 | 1696698455.0804508 | 2023-10-07 17:07:35.080451+00 | MULTIPOINT M ((119.88332710919182 28.654636776875787 1696698455.0804508)) | MULTIPOINT M ((119.88333363973983 28.65463173629246 169669
8455.0804508)) | 0.8483423712009329 | 2.9175268316100946 | 8.249572975777566e-06 | 4.501173204575792 | t
1 | 23654 | 1696699004.4357011 | 2023-10-07 17:16:44.435701+00 | MULTIPOINT M ((120.32586305058003 28.224965540361406 1696699004.4357011)) | MULTIPOINT M ((120.32585392021389 28.224969466135896 16966
99004.4357011)) | 0.9962285275669108 | 0.661942929711958 | 9.938375337237118e-06 | 279.42689827781703 | t
1 | 23675 | 1696669082.721245 | 2023-10-07 08:58:02.721245+00 | MULTIPOINT M ((121.33878123431359 30.225555129353545 1696669082.721245)) | MULTIPOINT M ((121.33878206818201 30.225553685528325 1696669082.721245)) | 0.1790592710016045 | 0.4590342533974994 | 1.6673235008993733e-06 | 5.607515229442826 | t
1 | 23748 | 1696701785.699044 | 2023-10-07 18:03:05.699044+00 | MULTIPOINT M ((121.1557328856501 30.311444374110813 1696701785.699044)) | MULTIPOINT M ((121.15573356432371 30.3114430549314 1696701785.699044)) | 0.16014871340427175 | 6.178921128890516 | 1.4835174577443446e-06 | 112.76922786042378 | t
1 | 25113 | 1696638867.9027693 | 2023-10-07 00:34:27.902769+00 | MULTIPOINT M ((120.43506771830249 29.41975734770522 1696638867.9027693)) | MULTIPOINT M ((120.43507174377926 29.419752871159407 1696638867.9027693)) | 0.631513163631407 | 9.127728575333236 | 6.020292719404112e-06 | 466.17290199731866 | t
1 | 26233 | 1696655643.9096806 | 2023-10-07 05:14:03.909681+00 | MULTIPOINT M ((121.44488671377087 30.262630868972778 1696655643.9096806)) | MULTIPOINT M ((121.44488465822698 30.262627062319194 1696655643.9096806)) | 0.4660541982066187 | 4.130090572881211 | 4.326184475629882e-06 | 1003.0412364550547 | t
1 | 27030 | 1696658261.5684383 | 2023-10-07 05:57:41.568438+00 | MULTIPOINT M ((120.38403054710031 28.780095603967563 1696658261.5684383)) | MULTIPOINT M ((120.38402775609217 28.78009681124826 1696658261.5684383)) | 0.3036026084604519 | 2.6057327750869943 | 3.04092088004529e-06 | 141.69170818971094 | t
1 | 27269 | 1696682133.4078817 | 2023-10-07 12:35:33.407882+00 | MULTIPOINT M ((119.95768023199354 28.334730961009434 1696682133.4078817)) | MULTIPOINT M ((119.95767862709869 28.33473194042768 1696682133.4078817)) | 0.19117291339615988 | 3.6411401012882836 | 1.8801221191774505e-06 | 1050.9958786735879 | t
1 | 28796 | 1696661559.0163007 | 2023-10-07 06:52:39.016301+00 | MULTIPOINT M ((120.58258460621535 29.724324164491893 1696661559.0163007)) | MULTIPOINT M ((120.5825795265706 29.724330529968938 1696661559.0163007)) | 0.8598883846787986 | 0.5121254649445207 | 8.143815087684714e-06 | 546.5198695672347 | t
1 | 28971 | 1696654296.5058289 | 2023-10-07 04:51:36.505829+00 | MULTIPOINT M ((121.29741412963867 30.164514163208008 1696654296.5058289)) | MULTIPOINT M ((121.29740996772121 30.16451210648733 1696654296.5058289)) | 0.46120185672994046 | 0.4833689872871863 | 4.6423551933974315e-06 | 141.55215964495375 | t
1 | 29769 | 1696686838.9500232 | 2023-10-07 13:53:58.950023+00 | MULTIPOINT M ((120.76697959213256 26.86544181008339 1696686838.9500232)) | MULTIPOINT M ((120.76697445922146 26.865441725833126 1696686838.9500232)) | 0.5101569300567247 | 6.56638108267371 | 5.131834578451469e-06 | 0.21293124586624265 | t
1 | 30443 | 1696671045.6869292 | 2023-10-07 09:30:45.686929+00 | MULTIPOINT M ((120.16365948050155 28.830947510591702 1696671045.6869292)) | MULTIPOINT M ((120.16365664744086 28.830950067308 1696671045.6869292)) | 0.395915407400825 | 0.34814817988748564 | 3.816151256001616e-06 | 1208.327302147006 | t
1 | 30643 | 1696699507.442408 | 2023-10-07 17:25:07.442408+00 | MULTIPOINT M ((121.25106467376509 30.180538404181128 1696699507.442408)) | MULTIPOINT M ((121.25106838743443 30.18053807917341 1696699507.442408)) | 0.35947784704684865 | 6.6953149552606765 | 3.7278637082682142e-06 | 35.11601924793931 | t
1 | 30711 | 1696671505.5776684 | 2023-10-07 09:38:25.577668+00 | MULTIPOINT M ((120.3675394213593 29.650030113601687 1696671505.5776684)) | MULTIPOINT M ((120.3675435494672 29.650023473137658 1696671505.5776684)) | 0.8375916363119341 | 11.244166875028942 | 7.819017536187592e-06 | 4.526707663295202 | t
1 | 30852 | 1696696661.781601 | 2023-10-07 16:37:41.781601+00 | MULTIPOINT M ((120.45459506716047 30.323928447505406 1696696661.781601)) | MULTIPOINT M ((120.45459330981092 30.323919897590955 1696696661.781601)) | 0.9627756175720703 | 2.3370886332069976 | 8.72864900615556e-06 | 260.433408187248 | t
1 | 32511 | 1696650096.5774534 | 2023-10-07 03:41:36.577453+00 | MULTIPOINT M ((120.69861677012867 28.660078020420073 1696650096.5774534)) | MULTIPOINT M ((120.69861386657008 28.66007871264182 1696650096.5774534)) | 0.2940256577130997 | 2.4657692233379183 | 2.9849327128774415e-06 | 234.06686686028428 | t
1 | 35374 | 1696668814.1683435 | 2023-10-07 08:53:34.168344+00 | MULTIPOINT M ((120.29634285128405 28.462983256409547 1696668814.1683435)) | MULTIPOINT M ((120.29634494217493 28.462979387799802 1696668814.1683435)) | 0.4751394131261665 | 3.2769761902178565 | 4.3974953519594e-06 | 585.8570695250128 | t
1 | 37280 | 1696670746.3334923 | 2023-10-07 09:25:46.333492+00 | MULTIPOINT M ((121.01733036949851 30.405979702094108 1696670746.3334923)) | MULTIPOINT M ((121.01732855189476 30.405979859119768 1696670746.3334923)) | 0.1755209588559273 | 0.6916806178030206 | 1.824373986854119e-06 | 5.820030977567113 | t
.....
1 | 92786 | 1696647693.9392626 | 2023-10-07 03:01:33.939263+00 | MULTIPOINT M ((121.23145032615521 30.07733795758556 1696647693.9392626)) | MULTIPOINT M ((121.23145316364328 30.077339001048284 1696647693.9392626)) | 0.2970157963216016 | 1.9955025384066218 | 3.0232677754001735e-06 | 1.5522382351805757 | t
1 | 92894 | 1696653823.8322117 | 2023-10-07 04:43:43.832212+00 | MULTIPOINT M ((120.50905785198812 29.66615634982832 1696653823.8322117)) | MULTIPOINT M ((120.5090646832996 29.666150256484613 1696653823.8322117)) | 0.9452780998209026 | 2.6750479790209414 | 9.153996579077552e-06 | 268.6419916165105 | t
1 | 94012 | 1696668793.713486 | 2023-10-07 08:53:13.713486+00 | MULTIPOINT M ((120.2243147944096 28.32114641244218 1696668793.713486)) | MULTIPOINT M ((120.22431645728058 28.321145614393213 1696668793.713486)) | 0.18551525351534806 | 0.22265847037325914 | 1.8444457025479694e-06 | 73.19416846294136 | t
1 | 94490 | 1696661443.6483343 | 2023-10-07 06:50:43.648334+00 | MULTIPOINT M ((119.98443743732514 28.278346144459974 1696661443.6483343)) | MULTIPOINT M ((119.98443171006116 28.278350401619917 1696661443.6483343)) | 0.7336970624152985 | 0.5480052239579389 | 7.1361718794268315e-06 | 1073.5657919499138 | t
1 | 95748 | 1696642913.567644 | 2023-10-07 01:41:53.567644+00 | MULTIPOINT M ((120.93007260280609 29.93665248905182 1696642913.567644)) | MULTIPOINT M ((120.93006480171415 29.936656468375393 1696642913.567644)) | 0.87284310410174 | 0.6987031737878059 | 8.756967239998214e-06 | 238.4298906455575 | t
1 | 96224 | 1696685404.5064683 | 2023-10-07 13:30:04.506468+00 | MULTIPOINT M ((121.34163029847866 30.330739655340032 1696685404.5064683)) | MULTIPOINT M ((121.34163436575774 30.330739893798075 1696685404.5064683)) | 0.3920210862680823 | 0.23594007861706245 | 4.0742632857507065e-06 | 2.284967046119032 | t
1 | 96675 | 1696649382.526684 | 2023-10-07 03:29:42.526684+00 | MULTIPOINT M ((121.17474468027332 29.41541323321169 1696649382.526684)) | MULTIPOINT M ((121.1747448241157 29.415413240849436 1696649382.526684)) | 0.01398508650050752 | 0.13768925799949278 | 1.4394098215803466e-07 | 120.80068698463104 | t
1 | 98639 | 1696660048.1699793 | 2023-10-07 06:27:28.169979+00 | MULTIPOINT M ((121.0546212370197 29.819232673811914 1696660048.1699793)) | MULTIPOINT M ((121.05462436430687 29.819231131689655 1696660048.1699793)) | 0.3472725872027416 | 3.1531459413645355 | 3.4868350545280815e-06 | 265.99258450437304 | t
(107 rows)
得到了107条数据, 在以上接触过受害人的人中, 进行盘查. 缩小范围.
2 如果信息还不够, 可以将时间拉长, 例如查看2天, 3天, 7天内接触过受害人的人, 进行盘查. 缩小范围.
3 嫌疑人特征库
已经上过嫌疑人名单的旧犯(users_class1表), 以及:
案件盘查后的可疑人。(例如近期与受害人有过接触的可疑对象, 有过节的可疑对象, 身边最近有异样的人等 ...)
4 数据对撞1
取1,2,3步得到的UID的交集, 锁定最可疑对象.
如果没有交集, 可能有很多原因, 例如嫌疑人或受害人的GIS信息不完整, 嫌疑人不在特征库内, 嫌疑人通过其他方式与受害人联系(而不是近距离接触) ...
对照
传统数据库对GIS支持不够完善, 例如类型不够完善, 索引不够完善, GIS类型的数据处理不够完善(例如可能不支持 轨迹相似伴随|时态分析).
PolarDB|PostgreSQL 具有非常完善的GIS功能, 从支持GIS丰富的数据类型, GIS索引, GIS排序(例如按距离远近排序)加速, 到丰富的GIS类型运算函数等.
https://postgis.net/docs/manual-3.4/
商业版的PolarDB通过Ganos对GIS的功能进行了进一步增强, 有兴趣的同学可以继续研究ganos.
https://help.aliyun.com/zh/polardb/polardb-for-postgresql/spatio-temporal-database/
知识点
GIS类型
GIS索引
GIS索引支持的操作符, ops, order by operator等.
GIS数据相关运算函数、运算符等
ganos (阿里云增强gis插件)
思考
阅读PostGIS手册, GIS还能用在哪些应用场景?
营销、刑侦、...
对于GIS类型, gist,sp-gist,brin 索引的选择原则是什么?
索引空间搜索为什么会有内部recheck, 和bound boxy有什么关系, 如何解决IO和cpu放大性能问题?
《一起学PolarDB - 第20期 - 为什么GIS时空查询即耗CPU又费IO?》
《PostgreSQL 推荐系统优化总计 - 空间、时间、标量等混合多模查询场景, 大量已读过滤导致CPU IO剧增(类挖矿概率下降优化)》
《推荐系统, 已阅读过滤, 大量CPU和IO浪费的优化思路2 - partial index - hash 分片， 降低过滤量》
《Recheck Cond filter IO\CPU放大 原理与优化CASE - 含 超级大表 不包含(反选) SQL优化》
《PostgreSQL multipolygon 空间索引查询过滤精简优化 - IO，CPU放大优化》
《PostgreSQL 空间st_contains，st_within空间包含搜索优化 - 降IO和降CPU(bound box) (多边形GiST优化)》
相似轨迹是否可以用向量距离计算得到?
pgvector
ganos GIS增强版有哪些特性? 应该和场景如何结合, 提高侦查效率?
相似轨迹, 伴随时长计算, 停留位置(轨迹驻点识别), 速度曲线计算等.
为什么要用轨迹分析, 为什么不直接使用点进行分析?
点是静态的, 无法计算速度, 而且点的时间可能不一致. 无法简单点进行交叉运算. 轨迹虽然也是点组成, 但是可以运算(模拟)时间、速度、驻留时间等.
计算距离的GIS函数返回的单位是什么? 单位和什么有关? 有哪些坐标系? 选择坐标系的原则是什么?
什么是球坐标系? 什么是投影坐标系?
投影坐标系的边界问题如何解决? 为什么你所处的地域不能选择某些投影坐标系?
参考
https://postgis.net/docs/manual-3.4/
https://postgis.net/docs/manual-3.4/ST_ClosestPointOfApproach.html
https://help.aliyun.com/zh/polardb/polardb-for-postgresql/spatio-temporal-database/
《PostgreSQL 应用开发解决方案最佳实践系列课程 - 6. 时空、时态、时序、日志等轨迹系统》
《PostgreSQL + PostGIS 时态分析》
《Greenplum 轨迹相似(伴随分析)》
《沉浸式学习PostgreSQL|PolarDB 14: 共享单车、徒步、旅游、网约车轨迹查询》
《PolarDB 开源版 轨迹应用实践 - 出行、配送、快递等业务的调度; 传染溯源; 刑侦》
《使用 PolarDB 开源版 部署 PostGIS 支撑时空轨迹|地理信息|路由等业务》
《重新发现PostgreSQL之美 - 11 时空轨迹系统 新冠&刑侦&预测》
《重新发现PostgreSQL之美 - 8 轨迹业务IO杀手克星index include(覆盖索引)》
《PostgreSQL 应用开发解决方案最佳实践系列课程 - 6. 时空、时态、时序、日志等轨迹系统》
《使用Postgres，MobilityDB和Citus大规模(百亿级)实时分析GPS轨迹》
《PostgreSQL index include - 类聚簇表与应用(append only, IoT时空轨迹, 离散多行扫描与返回)》
《PostgreSQL IoT，车联网 - 实时轨迹、行程实践 2 - (含index only scan类聚簇表效果)》
《PostgreSQL IoT，车联网 - 实时轨迹、行程实践 1》
《PostgreSQL pipelinedb 流计算插件 - IoT应用 - 实时轨迹聚合》
《Greenplum 轨迹相似(伴随分析)》
《PostgreSQL 实时位置跟踪+轨迹分析系统实践 - 单机顶千亿轨迹/天》
《GIS术语 - POI、AOI、LOI、路径、轨迹》
《菜鸟末端轨迹 - 电子围栏(解密支撑每天251亿个包裹的数据库) - 阿里云RDS PostgreSQL最佳实践》
《车联网案例，轨迹清洗 - 阿里云RDS PostgreSQL最佳实践 - 窗口函数》
《PostgreSQL 物流轨迹系统数据库需求分析与设计 - 包裹侠实时跟踪与召回》
《PostgreSQL 聚集存储 与 BRIN索引 - 高并发行为、轨迹类大吞吐数据查询场景解说》
《PostgreSQL 如何轻松搞定行驶、运动轨迹合并和切分》
《海量用户实时定位和圈人 - 团圆社会公益系统(位置寻人\圈人)》
欢迎关注我的github (https://github.com/digoal/blog) , 学习数据库不迷路.
近期正在写公开课材料, 未来将通过视频号推出, 欢迎关注视频号:
文章中的参考文档请点击阅读原文获得.