cypher lookup tables????

[dlyberis]

i am trying to build an iot project using neo4j just for training. I have a few iot devices that measure the concentration of air pollution gases and store these values on neo4j. I have build a cypher query and i received the following results as i wished to. 

at the next step i want to check each value and  calculate the air health status depenting on lookup table as you can see in the following image.

Is it possible in cypher to create a lookup table and search in which range the current value belongs and get an index of the air health? Any suggestions? Thanks in advanced

[glilienfield · Accepted solution]

Not really.  You could implement something. One approach would be to define 'AirHealth' nodes, where each contains the min/max ranges of AQI and the corresponding Air_Health value.  Given a 'value' of AQI, you could query them to find the corresponding 'Air_Health' with the following query:

with 100 as value
match(n:AirHealth)
where n.aqi_low <= value < n.aqi_high
return n.air_health

Another approach is just to use a CASE statement in your query that needs to map a given $value to air_health, such as:

RETURN CASE
    WHEN 0   <= $value < 50  THEN "Good"
    WHEN 50  <= $value < 100 THEN "Fair"
    WHEN 100 <= $value < 130 THEN "Moderate"
    WHEN 130 <= $value < 240 THEN "Poor"
    WHEN 240 <= $value < 380 THEN "Very Poor"
    WHEN 380 <= $value < 800 THEN "Extremely Poor"
    ELSE "Death Likely"
END

The issue with the CASE statement is that you will need to repeat the logic if you have multiple queries that will perform this mapping. An approach to avoid this is to use the APOC library to create a custom function that you can call from any query to map an AQI value to 'air_health.' The following creates the custom function:

CALL apoc.custom.declareFunction(
  'airQualityStatus(value::NUMBER) :: STRING',
  '
    RETURN CASE
    WHEN 0   <= $value < 50  THEN "Good"
    WHEN 50  <= $value < 100 THEN "Fair"
    WHEN 100 <= $value < 130 THEN "Moderate"
    WHEN 130 <= $value < 240 THEN "Poor"
    WHEN 240 <= $value < 380 THEN "Very Poor"
    WHEN 380 <= $value < 800 THEN "Extremely Poor"
    ELSE "Death Likely"
  END
  '
);

 You can then call it using the following in any query:

return custom.airQualityStatus(235) 

You will need to install the APOC library to use this solution.  

[dlyberis]

Thank you very much glilienfield for your response.

I have tried the custom.airQualityStatus with diffirent numbers and i am getting errors as you can see on the attachment. 

 But if i placed one or more zeros (0) infront of the value  the results are proper. This doesnt work with all values. After that i realized that the value hasnt been classified to the corresponding air quality status.

I think that this custom functions requires octo integer literals. Is it possible to change it? Is there any configurtion for this?

Thanks in advanced

[glilienfield]

Very interesting.  As you can see in my example, I used an integer. Maybe is weird behavior is caused by declaring the type of the input as a NUMBER. you can change it to one of the specific types of numbers. Here are the valid types:  FLOAT, DOUBLE, INT, INTEGER, NUMBER, LONG. I chose NUMBER because I thought it would be the most general. Try INT if your input values are integers or FLOAT if they contain decimals. See if that helps. 

you will change this segment in the declaration of the function ‘

value::NUMBER

[dlyberis]

After a lot of searching and experimenting i found that the problem that caused this was the installed version of APOC. I installed the latest one and everything works. Thank you again glilienfield for your help.