对野生动物的GPS信息，进行数据库查询

目录

前言

一、从movebank.org上，下载加拉帕戈斯群岛信天翁数据

二、使用步骤

1.将下载的shp数据导入postgres中，并根据时间戳顺序将每只鸟的轨迹点连成线段。

2.每只鸟的移动距离

3.找到飞的最快的那只鸟

 4、划分每只鸟的活动范围

总结

---

前言

movebank.org上提供了各种野生动物的GPS追踪信息，我们下载加拉帕戈斯群岛信天翁（Galapagos Albatrosses）的数据来进行数据库查询，得到以下问题的答案:

1、建立每只鸟的移动轨迹

2、计算每只鸟的移动距离

3、找到飞的最快的那只鸟

4、划分每只鸟的活动范围

一、从movebank.org上，下载加拉帕戈斯群岛信天翁数据

                                                                                    

                                             每只鸟有独立的id                    活动轨迹大约每三分钟记录一次，存储                                                                                                为1个点，按时间戳顺序排列

二、使用步骤

1.将下载的shp数据导入postgres中，并根据时间戳顺序将每只鸟的轨迹点连成线段。

注：轨迹点表为 points，tag_ident为tag_id，确定好空间坐标系，如4326

例：生成一只鸟的轨迹路线代码（默认起始点为gid=1 的点）：

WITH RECURSIVE next_spot(geom, idlist) AS (
    (SELECT
      geom,
      ARRAY[gid] AS idlist
    FROM points
    WHERE gid = 1)
	 UNION ALL
    (SELECT
      s.geom,
      array_append(n.idlist, s.gid) AS idlist
    FROM points s, next_spot n
    WHERE strpos(s.tag_ident, '138') != 0
    AND NOT n.idlist @> ARRAY[s.gid]
	  ORDER BY timestamp ASC
    LIMIT 1
    )
)

要一次生成全部鸟的轨迹路线的话，要写建立递归函数：

CREATE OR REPLACE function fly_Gala(integer, text) returns geometry AS
$$
WITH RECURSIVE next_spot(geom, idlist) AS (
    (SELECT
      geom AS geom,
      ARRAY[gid] AS idlist
    FROM points
    WHERE gid = $1)
    UNION ALL
    (SELECT
      s.geom AS geom,
      array_append(n.idlist, s.gid) AS idlist
    FROM points s, next_spot n
    WHERE strpos(s.tag_ident, $2) != 0
    AND NOT n.idlist @> ARRAY[s.gid]
    ORDER BY timestamp ASC
    LIMIT 1)
)
SELECT ST_MakeLine(geom) AS geom
FROM next_spot;
$$
language 'sql';

接下来就是用递归函数，来创建每个鸟的路线的表了，但需要注意的是要确定每只鸟的起始点，既每只鸟时间戳下的最早时间的点。 

CREATE TABLE lines AS

WITH tag_ident AS (
  SELECT DISTINCT unnest(string_to_array(tag_ident,',')) AS leibie
  FROM points ORDER BY leibie ),
  
  spots AS (
  SELECT s.gid, s.geom, r.leibie,s.timestamp
  FROM tag_ident r
  JOIN points s
  ON (strpos(s.tag_ident, r.leibie) <> 0)
),

bytimestamp AS (
  SELECT s.*
  FROM spots s 
  ORDER BY leibie, timestamp ASC
),

first_stops AS (
  SELECT DISTINCT ON (leibie) bytimestamp.*
  FROM bytimestamp
)

SELECT 
  leibie,
  fly_gala(gid, leibie) AS geom
FROM first_stops;

在QGIS中可以看到，生成的图形，也可以导入arcmap中看每一只鸟的轨迹路线：

 

2.每只鸟的移动距离

select st_length(geom)from lines;

3.找到飞的最快的那只鸟

根据飞行距离的总长度和所花费的时间来计算速度，以此为根据来找到飞的最快的那只鸟。要把时间戳类型数据转换为以秒为单位的小数类型的数据，且每只鸟的飞行距离的总长度也要转换为以米为单位的小数类型数据。

with lengths as(
select st_length(geom)as len,leibie
from lines),

times as(select (max(s.timestamp)::timestamp-min(s.timestamp)::timestamp)as time,tag_ident
from points s
group by s.tag_ident),

secs as(select extract(epoch from time)as sec ,tag_ident
from times),
	  
speeds as(select tag_ident,round((len*100000)::numeric/sec::numeric,4) as speed
from lengths,secs
where lengths.leibie=secs.tag_ident
),

Mspeeds as(select max(speed)as Mspeed
from speeds)
		 
select tag_ident,Mspeed
from speeds,Mspeeds
where speeds.speed=Mspeeds.Mspeed;

查询到的最快的鸟（tag_ident）和速度（m/s）如图：

 4、划分每只鸟的活动范围

 用ST_ConvexHull函数来计算每只鸟的活动范围。

select  leibie, ST_ConvexHull(geom) from lines;

---

总结

以上都是我根据老师出的题目，想出来的一些方法，供大家参考一下，有什么错误的地方，理解理解。