Day One 位置历史记录可视化

经过长时间的使用 DayOne 之后，积累了很多位置记录。如果能将这些数据导入到数据库，并实现 GeoJSON 的API 定制可视化将是一件很酷的事情。好在 DayOne 支持将历史位置备份进行导出。

DayOne 的历史位置备份导出的是一份 JSON 文件。结构如下。

[
    {
        "horizontalAccuracy":0,
        "arrivalDate":"",
        "location":{
            "region":{
                "center":{
                    "longitude":0,
                    "latitude":0
                },
                "identifier":"",
                "radius":0
            },
            "localityName":"",
            "country":"",
            "timeZoneName":"",
            "administrativeArea":"",
            "longitude":0,
            "placeName":"",
            "latitude":0
        },
        "uuid":"",
        "timestamp":""
    }
]

可以看到，这个文件就是一个数组。每一条记录都有指向了一个 location 项。这个 location 项记录的是当前记录所对应的地理位置的中心点坐标和半径，且是唯一的。多个地理位置记录会同时指向一个 location 。因此在设计数据库 schema 的时候，可以采取下列设计。

CREATE TABLE activity (
    arrival_date VARCHAR(25) NOT NULL,
    departure_date VARCHAR(25),
    horizontal_accuracy DOUBLE NOT NULL,
    uuid VARCHAR(50) NOT NULL PRIMARY KEY,
    timestamp VARCHAR(25) NOT NULL,
    locality VARCHAR(25) NOT NULL,
    country VARCHAR(25) NOT NULL,
    timezone VARCHAR(50) NOT NULL,
    place VARCHAR(25) NOT NULL,
    administrative_area VARCHAR(50) NOT NULL,
    longitude DOUBLE NOT NULL,
    latitude DOUBLE NOT NULL
) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8;

CREATE TABLE location (
    longitude DOUBLE NOT NULL,
    latitude DOUBLE NOT NULL,
    identifier VARCHAR(100) NOT NULL PRIMARY KEY,
    radius DOUBLE NOT NULL
) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8;

CREATE TABLE activity_feed (
  id INT AUTO_INCREMENT PRIMARY KEY,
  activity VARCHAR(50),
  location VARCHAR(100),
  FOREIGN KEY (activity) REFERENCES activity(uuid),
  FOREIGN KEY (location) REFERENCES location(identifier)
) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8;

最后，通过写一个 nodejs 的脚本将记录插入数据库即可。

import { uniqBy } from 'lodash';
import { createPool } from 'mysql2';
import { Observable, of } from 'rxjs';
import { concatAll, map, switchMap } from 'rxjs/operators';

import type { ResultSetHeader } from 'mysql2';

import feeds from './data/feed.json';

type Location = {
  region: {
    center: {
      longitude: number;
      latitude: number;
    };
    identifier: string;
    radius: number;
  };
  localityName: string;
  country: string;
  timeZoneName: string;
  administrativeArea: string;
  longitude: number;
  placeName: string;
  latitude: number;
};

type Feed = {
  horizontalAccuracy: number;
  arrivalDate: string;
  departureDate?: string;
  uuid: string;
  location: Location;
  timestamp: string;
};

type Feeds = Feed[];

type Query = (string | number | undefined)[];
type Queries = Query[];

const pool = createPool({
  host: 'your_database_host',
  user: 'your_database_user',
  database: 'your_database_name',
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0,
});

const query = (sql: string, data: Queries): Observable<number> => {
  return new Observable((subscriber) => {
    pool.query(sql, [data], (err, result) => {
      if (err) {
        subscriber.error(err);
      } else {
        subscriber.next((result as ResultSetHeader).affectedRows);
      }

      subscriber.complete();
    });
  });
};

const location$ = of(feeds).pipe(
  map<Feeds, Queries>((feeds) =>
    uniqBy(feeds, (feed) => feed.location.region.identifier).map((feed) => {
      const {
        location: {
          region: {
            center: { longitude, latitude },
            identifier,
            radius,
          },
        },
      } = feed;

      return [longitude, latitude, identifier, radius];
    })
  ),
  switchMap<Queries, Observable<number>>((queries) =>
    query(
      'INSERT INTO location (longitude, latitude, identifier, radius) VALUES ?',
      queries
    )
  )
);

const activity$ = of(feeds).pipe(
  map<Feeds, Queries>((feeds) =>
    feeds.map((feed) => {
      const {
        arrivalDate,
        departureDate,
        timestamp,
        horizontalAccuracy,
        uuid,
        location: {
          localityName,
          country,
          timeZoneName,
          administrativeArea,
          longitude,
          placeName,
          latitude,
        },
      } = feed;

      return [
        arrivalDate,
        departureDate,
        horizontalAccuracy,
        uuid,
        timestamp,
        localityName,
        country,
        timeZoneName,
        placeName,
        administrativeArea,
        longitude,
        latitude,
      ];
    })
  ),
  switchMap<Queries, Observable<number>>((queries) =>
    query(
      'INSERT INTO activity (arrival_date, departure_date, horizontal_accuracy, uuid, timestamp, locality,' +
        ' country, timezone, place, administrative_area, longitude, latitude) VALUES ?',
      queries
    )
  )
);

const feeds$ = of(feeds).pipe(
  map<Feeds, Queries>((feeds) =>
    feeds.map((feed) => {
      const {
        uuid,
        location: {
          region: { identifier },
        },
      } = feed;

      return [uuid, identifier];
    })
  ),
  switchMap<Queries, Observable<number>>((queries) =>
    query('INSERT INTO activity_feed (activity, location) VALUES ?', queries)
  )
);

of(location$, activity$, feeds$)
  .pipe(concatAll())
  .subscribe({
    error: console.error,
    complete: () => {
      pool.end();
    },
  });

有了数据之后，就可以编写接口和应用实现数据可视化的功能了。

作者： Cheng