digoal
欢迎数据库应用开发者参与贡献场景, 在此issue回复即可, 共同建设《沉浸式数据库学习教学素材库》, 帮助开发者用好数据库, 提升开发者职业竞争力, 同时为企业降本提效.
本文的实验可以使用永久免费的阿里云云起实验室来完成.
如果你本地有docker环境也可以把镜像拉到本地来做实验:
x86_64机器使用以下docker image:
ARM机器使用以下docker image:
拐卖、诱骗场景假设:
当我们手机开启定位功能和流量时, 可能会透过监控工具或移动APP, 定时上报手机的定位信息. 因此就形成了人的轨迹数据.
在被害前, 嫌疑人可能会与受害人进行接触(例如电话、短信、聊天工具等).
在拐卖、诱骗过程中, 受害人的轨迹 与 犯罪嫌疑人的轨迹有相似性.
在在拐卖、诱骗的中转过程中, 受害人的轨迹 与 转运人员的轨迹有近距点接触.
通过GIS轨迹相似伴随|时态分析 结合 嫌疑人名单进行撞库分析, 可以更高效的锁定犯罪嫌疑人, 对拐卖、诱骗案件侦破有帮助.
思考:
根据轨迹还可以分析在哪里停留过, 停留了多长时间? 每个时间点的速度是多少, 可能使用了什么交通工具等.
结合交通工具的轨迹, 可以碰撞出车牌等.
传统数据库对GIS支持不够完善, 例如类型不够完善, 索引不够完善, GIS类型的数据处理不够完善(例如可能不支持 轨迹相似伴随|时态分析).
创建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)
假设受害人ID为1 (根据上面的测试数据选择, 有某一天的密集轨迹, 不在嫌疑人ID范围.).
假设受害人 2023-10-08 00:00:00
失联.
1 在拐卖、诱骗过程中, 押运受害人的嫌疑人, 和受害人必定有轨迹伴随特征
将失联前最后1天的数据点组合成轨迹. 通过轨迹计算出与受害人有过接触的人的ID. 以及:
PostGIS 轨迹计算函数如下:
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米以内)的信息:
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类型运算函数等.
商业版的PolarDB通过Ganos对GIS的功能进行了进一步增强, 有兴趣的同学可以继续研究ganos.
GIS类型
GIS索引
GIS索引支持的操作符, ops, order by operator等.
GIS数据相关运算函数、运算符等
ganos (阿里云增强gis插件)
阅读PostGIS手册, GIS还能用在哪些应用场景?
对于GIS类型, gist,sp-gist,brin 索引的选择原则是什么?
索引空间搜索为什么会有内部recheck, 和bound boxy有什么关系, 如何解决IO和cpu放大性能问题?
相似轨迹是否可以用向量距离计算得到?
ganos GIS增强版有哪些特性? 应该和场景如何结合, 提高侦查效率?
为什么要用轨迹分析, 为什么不直接使用点进行分析?
计算距离的GIS函数返回的单位是什么? 单位和什么有关? 有哪些坐标系? 选择坐标系的原则是什么?
什么是球坐标系? 什么是投影坐标系?
投影坐标系的边界问题如何解决? 为什么你所处的地域不能选择某些投影坐标系?
此处可能存在不合适展示的内容,页面不予展示。您可通过相关编辑功能自查并修改。
如您确认内容无涉及 不当用语 / 纯广告导流 / 暴力 / 低俗色情 / 侵权 / 盗版 / 虚假 / 无价值内容或违法国家有关法律法规的内容,可点击提交进行申诉,我们将尽快为您处理。