DBScan 之 PostGIS ST_ClusterDBSCAN 和 Java 的 DBSCANClusterer 两种实现

avatar 2019年9月20日17:50:56 评论 102 views

在做热力图的时候,我们需要对所有的坐标进行聚合计算,比如对数据库里的所有数据的坐标进行聚合,以100米为一组,即经纬度计算100米范围内(通常我们是以经纬度,如每 0.00009度)的所有数据为一组,并统计每组的坐标数量,该组的经纬度我们可以取所有点的平均值。

最后把所有组的经纬度和每组的坐标数量返回给前端,前端就能展示了,数量多的可以显示更颜色可以更亮和图形更大,数量小的话颜色显示浅些。

现在的问题是怎么进行聚类,如何对坐标进行聚类。

目前我知道的有两种方法,一种是通过 PostgreSQL 的插件 PostGis 来计算,换言之通过数据库SQL语句来执行;另一种是通过Java代码来实现聚类,目前还在探索中,好像apache有相关的类。

下面分别来介绍:

PostGIS 的 ST_ClusterDBSCAN

先说以下通过 PostGis 来实现。主要是用到 ST_ClusterDBSCAN这个函数

下面是一个例子

  1. SELECT
  2.     cid,
  3.     COUNT ( cid ),
  4.     AVG ( latitude ) AS latitude,
  5.     AVG ( longitude ) AS longitude
  6. FROM
  7.     (
  8. SELECT
  9.   -- 聚类计算
  10.     st_clusterdbscan ( geom, 0.00009, 1 ) OVER () AS cid,
  11.     -- 将 geom 字段经纬度 
  12.     split_part( st_aslatlontext ( geom, 'DD.DDDDDD' ), ' ', 1 ) :: NUMERIC AS latitude,
  13.     split_part( st_aslatlontext ( geom, 'DD.DDDDDD' ), ' ', 2 ) :: NUMERIC AS longitude
  14. FROM
  15.     your_table_name
  16. WHERE
  17.   -- 条件
  18.     ST_Intersects (
  19.     geom,
  20.     st_geometryfromtext ( #{ boundWkt }, 4326 )) = TRUE
  21.     ) sq
  22. GROUP BY
  23.     cid

相关文档

http://postgis.net/docs/manual-dev/ST_ClusterDBSCAN.html

 

DBSCAN Java实现

目前找到了 apache 的 DBSCANClusterer类

  1. import org.apache.commons.math3.ml.clustering.Cluster;
  2. import org.apache.commons.math3.ml.clustering.DBSCANClusterer;
  3. import org.apache.commons.math3.ml.clustering.DoublePoint;
  4. import java.util.Arrays;
  5. import java.util.List;
  6. /**
  7.  * @author saysky on 2019-9-20
  8.  */
  9. public class Demo {
  10.     public static void main(String[] args) {
  11.         final DoublePoint[] points = new DoublePoint[] {
  12.                 new DoublePoint(new double[] { 83.0830324492417358.83387754182331 }),
  13.                 new DoublePoint(new double[] { 45.0544551094062623.469642649637535 }),
  14.                 new DoublePoint(new double[] { 14.9641792143229469.0264096390456 }),
  15.                 new DoublePoint(new double[] { 73.5318960433360234.896145021310076 }),
  16.                 new DoublePoint(new double[] { 73.2849817355163433.96860806993209 }),
  17.                 new DoublePoint(new double[] { 73.4582809887360833.92584423092194 }),
  18.                 new DoublePoint(new double[] { 73.965788918314535.73191006924026 }),
  19.                 new DoublePoint(new double[] { 74.007409718353336.81735596177168 }),
  20.                 new DoublePoint(new double[] { 73.4124754141084834.27314856695011 }),
  21.                 new DoublePoint(new double[] { 73.915625635301736.83206791547127 }),
  22.                 new DoublePoint(new double[] { 74.8149920580908737.15682749846019 }),
  23.                 new DoublePoint(new double[] { 74.0314488008152737.57399178552441 }),
  24.                 new DoublePoint(new double[] { 74.5187094120774438.674258946906775 }),
  25.                 new DoublePoint(new double[] { 74.5075459510553635.58903978415765 }),
  26.                 new DoublePoint(new double[] { 74.5132275274954736.030572259100154 }),
  27.                 new DoublePoint(new double[] { 59.2790099661797346.41091720294207 }),
  28.                 new DoublePoint(new double[] { 59.7374479384161546.20015558367595 }),
  29.                 new DoublePoint(new double[] { 58.8113407667260645.71150126331486 }),
  30.                 new DoublePoint(new double[] { 58.5222553943749547.416083617601544 }),
  31.                 new DoublePoint(new double[] { 58.21862664702348447.36228902172297 }),
  32.                 new DoublePoint(new double[] { 60.2713966944720646.606106348801404 }),
  33.                 new DoublePoint(new double[] { 60.89496246236376546.976924697402865 }),
  34.                 new DoublePoint(new double[] { 62.2904867387842447.66970563563518 }),
  35.                 new DoublePoint(new double[] { 61.0385760897770546.212924720020965 }),
  36.                 new DoublePoint(new double[] { 60.1691621413920145.18193661351688 }),
  37.                 new DoublePoint(new double[] { 59.9003690597601247.555364347063005 }),
  38.                 new DoublePoint(new double[] { 62.3300363414455247.83941489877179 }),
  39.                 new DoublePoint(new double[] { 57.8603553671855547.31117930193432 }),
  40.                 new DoublePoint(new double[] { 58.1371547968592548.985960494028404 }),
  41.                 new DoublePoint(new double[] { 56.13192396354861646.8508904252667 }),
  42.                 new DoublePoint(new double[] { 55.97632988705347.46384037658572 }),
  43.                 new DoublePoint(new double[] { 56.2324597523547747.940035191131756 }),
  44.                 new DoublePoint(new double[] { 58.5168704821262546.622885352699086 }),
  45.                 new DoublePoint(new double[] { 57.8541108190547745.95394361577928 }),
  46.                 new DoublePoint(new double[] { 56.44577631144784445.162093662656844 }),
  47.                 new DoublePoint(new double[] { 57.3669194965623347.50097194337286 }),
  48.                 new DoublePoint(new double[] { 58.24362638755701546.114052729681134 }),
  49.                 new DoublePoint(new double[] { 56.2722459563519844.799080066150054 }),
  50.                 new DoublePoint(new double[] { 57.60692481650039646.94291057763621 }),
  51.                 new DoublePoint(new double[] { 30.1871423004195113.877149710431695 }),
  52.                 new DoublePoint(new double[] { 30.44944881065748613.490778346545994 }),
  53.                 new DoublePoint(new double[] { 30.29501839028671413.264889000216499 }),
  54.                 new DoublePoint(new double[] { 30.16020183288492311.89278262341395 }),
  55.                 new DoublePoint(new double[] { 31.34150979178957615.282655921997502 }),
  56.                 new DoublePoint(new double[] { 31.6860163032542914.756873246748 }),
  57.                 new DoublePoint(new double[] { 29.32596374256536412.097849250072613 }),
  58.                 new DoublePoint(new double[] { 29.5482074238825613.613295356975868 }),
  59.                 new DoublePoint(new double[] { 28.7935960888862610.36352064087987 }),
  60.                 new DoublePoint(new double[] { 31.0128459709230812.788479208014905 }),
  61.                 new DoublePoint(new double[] { 27.5850921673700211.47570110601373 }),
  62.                 new DoublePoint(new double[] { 28.59379956172779210.780998203903437 }),
  63.                 new DoublePoint(new double[] { 31.35610576672479515.080316198524088 }),
  64.                 new DoublePoint(new double[] { 31.2594850363675513.674329151166603 }),
  65.                 new DoublePoint(new double[] { 32.3159007637295914.95261758659035 }),
  66.                 new DoublePoint(new double[] { 30.46041370276361715.88402809202671 }),
  67.                 new DoublePoint(new double[] { 32.5617820306215414.586076852632686 }),
  68.                 new DoublePoint(new double[] { 32.7613864853046816.239837325178087 }),
  69.                 new DoublePoint(new double[] { 30.182945333188414.709592407103628 }),
  70.                 new DoublePoint(new double[] { 29.5508817352820215.0651247180067 }),
  71.                 new DoublePoint(new double[] { 29.00415530218742814.089665298582986 }),
  72.                 new DoublePoint(new double[] { 29.33962443983182313.29096065578051 }),
  73.                 new DoublePoint(new double[] { 30.99746032757684614.551914158277214 }),
  74.                 new DoublePoint(new double[] { 30.6678412612527616.269703107886016 })
  75.         };
  76.         final DBSCANClusterer<DoublePoint> transformer =
  77.                 new DBSCANClusterer<DoublePoint>(1, 0);
  78.         final List<Cluster<DoublePoint>> clusters = transformer.cluster(Arrays.asList(points));
  79.         System.out.println(clusters);
  80.     }
  81. }

第一个参数是表示最小半径,单位是度数,经纬度的度数,最小0,最大360。如当为1的时候,注意如果相邻的相邻的相邻都满足相差1个经纬度半径,也算在一个组的;

第二个参数时该组内至少有几个邻居(1表示该组至少要有2个点,0表示至少1个点)

 

文档如下

http://commons.apache.org/proper/commons-math/apidocs/org/apache/commons/math4/ml/clustering/DBSCANClusterer.html

  • 微信
  • 交流学习,有偿服务
  • weinxin
  • 博客/Java交流群
  • 资源分享,问题解决,技术交流。群号:590480292
  • weinxin
avatar

发表评论

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: