a1mzone/spatial_functions.md

## spatial_functions.md

      
    Raw
  

              spatial_functions.md
            
          
    GeoMesa Spark

MakePolygon

ST_MakePolygon(ST_ExteriorRing(ST_Collect(

st_makepoint(28.1833495,-26.0544965),

st_makepoint(28.1837932,-26.0604524),

st_makepoint(28.1833495,-26.0544965))))
PolygonFromText

st_contains(ST_PolygonFromText('POLYGON((
28.1833495 -26.0544965,
28.2527973 -26.0118373,
28.1833495 -26.0544965
))'), geom)
Create Events by Label

val sqlQuery = """
WITH linestr (
-- order asc and calculate delta time
SELECT
deviceId,
geom,
accuracy,
timestamp,
bigint(to_timestamp(lead(timestamp, 1) OVER (PARTITION BY deviceId ORDER BY timestamp ASC))) - bigint(to_timestamp(timestamp)) duration
FROM points
--WHERE deviceId = 'D9A82640-8D3D-46CF-BD36-760D3B762959'
ORDER BY timestamp ASC
),
line_calc (
-- filter out duplicate points, calculate distance and speed
SELECT
deviceId,
geom,
st_distanceSphere(geom, lead(geom, 1) OVER (PARTITION BY deviceId ORDER BY timestamp ASC)) distance,
st_distanceSphere(geom, lead(geom, 1) OVER (PARTITION BY deviceId ORDER BY timestamp ASC))/duration speed,
accuracy,
timestamp,
duration
FROM linestr
WHERE duration > 0
),
line_label (
-- label linestr type
SELECT
*,
CASE
WHEN distance < accuracy OR distance < 8 THEN 'Stationary'
WHEN distance > 8 AND speed < 50 THEN 'Moving'
WHEN speed > 50 THEN 'Invalid'
END status
FROM line_calc
),
determine_event (
-- new event if linestr type changed
SELECT
*,
CASE
WHEN status = lag(status, 1) OVER (PARTITION BY deviceId ORDER BY timestamp ASC) THEN 0
ELSE 1
END is_new_event
FROM line_label
),
line_group (
-- index points into groups
SELECT
*,
SUM(is_new_event) OVER (PARTITION BY deviceId ORDER BY timestamp ASC) group
FROM determine_event
),
event_linestr (
-- aggregate the event
SELECT
deviceId,
collect_list(geom) linestr,
SUM(distance) distance,
AVG(speed) speed,
COUNT(geom)+1 points,
AVG(accuracy) accuracy,
MIN(timestamp) timestamp,
SUM(duration) duration,
status,
group
FROM line_group
GROUP BY deviceId, status, group
ORDER BY group
)
SELECT
-- add the end point to the linestring
deviceId,
st_makeLine(concat(linestr, array(lead(linestr[0], 1) OVER (PARTITION BY deviceId ORDER BY timestamp ASC)))) linestr,
points,
distance,
speed,
accuracy,
timestamp,
duration,
status,
group
FROM event_linestr
"""
PostGIS

st_contains (admin level (wkb geometry)

SELECT

*

FROM events ez, za_admin_0 za

WHERE st_contains(za.wkb_geometry, st_setsrid(ez.geom, 4326));