1.背景
欢迎数据库应用开发者参与贡献场景, 在此issue回复即可, 共同建设《沉浸式数据库学习教学素材库》, 帮助开发者用好数据库, 提升开发者职业竞争力, 同时为企业降本提效.
- 系列课程的核心目标是教大家怎么用好数据库, 而不是怎么运维管理数据库、怎么开发数据库内核. 所以面向的对象是数据库的用户、应用开发者、应用架构师、数据库厂商的产品经理、售前售后专家等角色.
本文的实验可以使用永久免费的阿里云云起实验室来完成.
如果你本地有docker环境也可以把镜像拉到本地来做实验:
x86_64机器使用以下docker image:
ARM机器使用以下docker image:
2.业务场景1 介绍: 通过GIS轨迹相似伴随|时态分析|轨迹驻点识别等技术对拐卖、诱骗场景进行侦查
拐卖、诱骗场景假设:
当我们手机开启定位功能和流量时, 可能会透过监控工具或移动APP, 定时上报手机的定位信息. 因此就形成了人的轨迹数据.
在被害前, 嫌疑人可能会与受害人进行接触(例如电话、短信、聊天工具等).
在拐卖、诱骗过程中, 受害人的轨迹 与 犯罪嫌疑人的轨迹有相似性.
在在拐卖、诱骗的中转过程中, 受害人的轨迹 与 转运人员的轨迹有近距点接触.
通过GIS轨迹相似伴随|时态分析 结合 嫌疑人名单进行撞库分析, 可以更高效的锁定犯罪嫌疑人, 对拐卖、诱骗案件侦破有帮助.
思考:
根据轨迹还可以分析在哪里停留过, 停留了多长时间? 每个时间点的速度是多少, 可能使用了什么交通工具等.
结合交通工具的轨迹, 可以碰撞出车牌等.
2.1实现和对照
2.1.1传统方法 设计和实验
传统数据库对GIS支持不够完善, 例如类型不够完善, 索引不够完善, GIS类型的数据处理不够完善(例如可能不支持 轨迹相似伴随|时态分析).
2.1.2PolarDB|PG新方法1 设计和实验
2.1.2.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)
2.2实验通过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信息不完整, 嫌疑人不在特征库内, 嫌疑人通过其他方式与受害人联系(而不是近距离接触) ...
2.3对照
传统数据库对GIS支持不够完善, 例如类型不够完善, 索引不够完善, GIS类型的数据处理不够完善(例如可能不支持 轨迹相似伴随|时态分析).
PolarDB|PostgreSQL 具有非常完善的GIS功能, 从支持GIS丰富的数据类型, GIS索引, GIS排序(例如按距离远近排序)加速, 到丰富的GIS类型运算函数等.
商业版的PolarDB通过Ganos对GIS的功能进行了进一步增强, 有兴趣的同学可以继续研究ganos.
3.知识点
GIS类型
GIS索引
GIS索引支持的操作符, ops, order by operator等.
GIS数据相关运算函数、运算符等
ganos (阿里云增强gis插件)
4.思考
阅读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函数返回的单位是什么? 单位和什么有关? 有哪些坐标系? 选择坐标系的原则是什么?
什么是球坐标系? 什么是投影坐标系?
投影坐标系的边界问题如何解决? 为什么你所处的地域不能选择某些投影坐标系?
5.参考
- 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 如何轻松搞定行驶、运动轨迹合并和切分》
- 《海量用户实时定位和圈人 - 团圆社会公益系统(位置寻人\圈人)》