matching_clarity_sensors.sql

CREATE TABLE current_clarity_nodes AS
SELECT unnest(ARRAY[1285716,1314366,1373846,1378636,1533718,1533720,1533721,1533915,1533917,1533920,1894630,1894631,1894632,1894634,1894636,1894637,1894638,1894639,1894640,1894641,1894642,1894643,1924313,1949202,1949203,1949206,2152632,2152633,2156118,2402491,290475,290476,290477,290478,290479,290480,290481,290482,290483,290484,290485,290487,290488,290489,290490,290491,290492,290495,290496,290498,290499,290500,290501,290502,290504,290505,290506,290508,290510,290512,290513,290515,290517,290518,290519,290520,290521,290522,290523,290524,290526,290528,290529,290530,290531,290532,290533,290534,290535,290536,290537,290538,290540,290541,290542,290543,290544,290545,290546,290549,290551,290552,290553,290554,290555,290557,290558,290559,290560,290561,290563,290564,290565,290566,290567,290569,290570,290571,290572,290573,290574,290575,290576,290578,290582,290583,290584,290585,290587,290588,290589,290590,290591,290593,290594,290595,290596,290597,290599,290600,290601,290602,290603,290604,290605,290606,290607,290608,290609,290610,290611,290614,290615,290616,290618,290620,290621,290622,290623,290624,290625,290626,290628,290629,290630,290632,290633,290634,290635,290636,290637,290638,290639,290641,290642,290643,290644,290645,290646,290648,290649,290650,290651,290652,290653,290654,290655,290656,290657,290658,290659,290660,290661,290662,290664,290665,290667,290668,290670,290671,290672,290674,290675,290677,290678,290679,290680,290681,290683,290685,290686,290687,300026,300027,300028,300030,301884,301885,308728,310353,310354,310355,310356,310357,310358,310360,351822,351823,351824,351825,351826,367083,367107,367110,367112,367113,367114,367116,367117,367118,370742,370743,370744,370750,370751,370752,815609,923364,923365,929705,938377,947124,947125,947126,947127,947128,947130,947132,947133,947134,947137,947138,947139,947140,947141,947142,947143,947144,947150,947151,947152,947153,947154,947155,947156,947157,947158,947159,947160,947161,947162,947163,947164,947165,947166,947168,947169,947170,947171,947172,947173,947174,947175,947176,947177,947178,947180,947182,947183,947184,947185,947186,947187,947188,947189,947190,947191,947192,947194,947195,947196,947197,947198,947199,947200,947201,947202,947203,947204,947205,947206,947207,947208,947210,947211,947212,947213,947214,947216,947217,947218,947219,947220,947221,947222,947223,947224,947225,947226,947227,947228,947229,947230,947231,947232,947234,947235,947236,947237,947238,947239,947240,947241,947242,947243,947244,947245,947246,947247,947248,947249,947250,947251,947252,947253,947254,947255,947256,947257,947258,947259,947260,947261,947262,947264,947265,947266,947267,947268,947270,947271,947273,947274,947275,947276,947277,947278,947279,947280,947281,947283,947284,947285,947286,947287,947288,947289,947290,947291,947292,947295,947296,947297,947298,947299,947300,947301,947302,947303,947304,947305,947306,947307,947308,947309,947310,947312,947313,947314,947315,947316,947317,947318,947319,947320,947321,947322,947323,947324,947325,947326,947327,947328,947329,947330,947332,947334,947335,947336,947338,947339,947340,947341,947342,947343,947344,947345,947346,947347,947348,947349]) as node;



    WITH clarity AS (
    SELECT sensor_nodes_id
  , source_id
  , site_name
  , geom
  , added_on
  , node IS NOT NULL as is_active
  FROM sensor_nodes
  JOIN current_clarity_nodes ON (sensor_nodes_id = node)
  WHERE source_name = 'clarity')
  SELECT c.sensor_nodes_id
  , c.source_id
  , c.site_name
  , n.source_id
  , n.site_name
  , c.geom = n.geom
  , is_active
  , ROUND(st_distance(c.geom, n.geom)::numeric, 4) as distance
  FROM clarity c
  LEFT JOIN staging_sensornodes n ON (st_distance(c.geom, n.geom)<0.0001)
  -- WHERE n.source_id IS NOT NULL OR is_active
  WHERE n.source_id IS NULL
  ORDER BY c.sensor_nodes_id DESC NULLS FIRST;




  WITH clarity AS (
    SELECT sensor_nodes_id
  , source_id
  , site_name
  , geom
  , added_on
  , node IS NOT NULL as is_active
  FROM sensor_nodes
  LEFT JOIN current_clarity_nodes ON (sensor_nodes_id = node)
  WHERE source_name = 'clarity')
  SELECT n.source_id
  , n.site_name
  , c.source_id
  , c.site_name
  --, c.geom
  --, n.geom
  --, c.added_on
  , c.geom = n.geom
  , is_active
  , ROUND(st_distance(c.geom, n.geom)::numeric, 4) as distance
  , c.sensor_nodes_id
  , c.sensor_nodes_id = LAG(c.sensor_nodes_id) OVER (ORDER BY c.sensor_nodes_id)
  FROM staging_sensornodes n
  --JOIN clarity c ON (n.site_name = c.site_name)
  --JOIN clarity c ON (n.geom = c.geom)
  JOIN clarity c ON (n.source_id = c.source_id)
  --LEFT JOIN clarity c ON (st_distance(c.geom, n.geom)<0.00001)
  --WHERE n.source_id IS NOT NULL OR is_active
  WHERE is_active
  ORDER BY sensor_nodes_id DESC;





  SELECT *
  FROM staging_sensornodes
  WHERE source_id = 'DBXRI9190';


  -- How many active clarity sensor nodes do we have?
  SELECT string_agg(DISTINCT sensor_nodes_id::text, ',')
  FROM sensor_nodes_check
  WHERE source_name = 'clarity'
  AND datetime_last > current_date
  ;