Sample SQL

These examples assume the published views are available in the LISTING schema.

Site and audit coverage

SELECT
  SiteGuid,
  SiteName,
  COUNT(DISTINCT AuditGuid) AS audit_count,
  MIN(AuditDateLocal) AS first_audit_date,
  MAX(AuditDateLocal) AS last_audit_date
FROM LISTING.AUDIT_METADATA
GROUP BY 1, 2
ORDER BY audit_count DESC, last_audit_date DESC;

Movement by hour and mode

SELECT
  SiteName,
  AuditDateLocal,
  EventLocalHour,
  COALESCE(ModeOfMovement, 'Unknown') AS mode_of_movement,
  SUM(Number) AS total_people
FROM LISTING.MOVEMENT_COUNTS
GROUP BY 1, 2, 3, 4
ORDER BY SiteName, AuditDateLocal, EventLocalHour, mode_of_movement;

Stay hotspots

SELECT
  SiteName,
  AuditDateLocal,
  ROUND(Latitude, 4) AS lat_bin,
  ROUND(Longitude, 4) AS lon_bin,
  COUNT(*) AS stay_records,
  SUM(COALESCE(Number, 1)) AS people_observed,
  AVG(StayDurationMinutes) AS avg_stay_minutes
FROM LISTING.DWELL_OBSERVATIONS
WHERE Latitude IS NOT NULL
  AND Longitude IS NOT NULL
GROUP BY 1, 2, 3, 4
HAVING COUNT(*) >= 3
ORDER BY people_observed DESC, avg_stay_minutes DESC;

Trace intensity and approximate path length

SELECT
  SiteName,
  AuditDateLocal,
  COUNT(*) AS trace_count,
  AVG(LinePointCount) AS avg_line_points,
  AVG(ST_LENGTH(TRY_TO_GEOGRAPHY(GeometryWkt))) AS avg_trace_length_m,
  SUM(ST_LENGTH(TRY_TO_GEOGRAPHY(GeometryWkt))) AS total_trace_length_m
FROM LISTING.TRACE_PATHS
WHERE GeometryType = 'LINESTRING'
GROUP BY 1, 2
ORDER BY total_trace_length_m DESC, trace_count DESC;

Consumer benchmark query

WITH count_summary AS (
  SELECT
    SiteGuid,
    SUM(Number) AS total_counted_people
  FROM LISTING.MOVEMENT_COUNTS
  GROUP BY 1
),
stay_summary AS (
  SELECT
    SiteGuid,
    AVG(StayDurationMinutes) AS avg_stay_minutes,
    SUM(COALESCE(Number, 1)) AS total_stay_people
  FROM LISTING.DWELL_OBSERVATIONS
  GROUP BY 1
),
trace_summary AS (
  SELECT
    SiteGuid,
    COUNT(*) AS trace_count,
    SUM(ST_LENGTH(TRY_TO_GEOGRAPHY(GeometryWkt))) AS total_trace_length_m
  FROM LISTING.TRACE_PATHS
  GROUP BY 1
)
SELECT
  s.SiteName,
  COALESCE(c.total_counted_people, 0) AS total_counted_people,
  COALESCE(st.total_stay_people, 0) AS total_stay_people,
  st.avg_stay_minutes,
  COALESCE(t.trace_count, 0) AS trace_count,
  t.total_trace_length_m
FROM LISTING.SITE_METADATA s
LEFT JOIN count_summary c
  ON s.SiteGuid = c.SiteGuid
LEFT JOIN stay_summary st
  ON s.SiteGuid = st.SiteGuid
LEFT JOIN trace_summary t
  ON s.SiteGuid = t.SiteGuid
ORDER BY total_counted_people DESC, trace_count DESC;

Qualitative theme scan

WITH text_rows AS (
  SELECT
    SiteName,
    AuditDateLocal,
    LOWER(REGEXP_REPLACE(Comments, '[^a-zA-Z ]', ' ')) AS text_value
  FROM LISTING.INTERCEPT_RESPONSES
  WHERE Comments IS NOT NULL

  UNION ALL

  SELECT
    SiteName,
    AuditDateLocal,
    LOWER(REGEXP_REPLACE(Entry, '[^a-zA-Z ]', ' ')) AS text_value
  FROM LISTING.OBSERVATIONS
  WHERE Entry IS NOT NULL
),
tokens AS (
  SELECT
    SiteName,
    AuditDateLocal,
    TRIM(value::string) AS token
  FROM text_rows,
  LATERAL SPLIT_TO_TABLE(text_value, ' ')
  WHERE value IS NOT NULL
)
SELECT
  SiteName,
  AuditDateLocal,
  token,
  COUNT(*) AS mention_count
FROM tokens
WHERE LENGTH(token) >= 5
  AND token NOT IN (
    'there', 'their', 'about', 'which', 'would', 'could', 'should', 'place', 'people'
  )
GROUP BY 1, 2, 3
HAVING COUNT(*) >= 2
ORDER BY mention_count DESC, SiteName, AuditDateLocal, token;

Spatial note

For most geospatial workflows, TRY_TO_GEOGRAPHY(GeometryWkt) is the cleanest starting point.