logo
down
shadow

Optimise SQL Query- sensor readings


Optimise SQL Query- sensor readings

By : user2949650
Date : November 17 2020, 01:00 AM
wish helps you This query gets the max and min readings from each sensor in the most recent 24 hours. Notice the absence of any DISTINCT instructions; the GROUP BY does that for you.
code :
          SELECT sensorid, MAX(value) AS maxvalue, MIN(value) AS minvalue
            FROM readings
           WHERE date >= NOW() - INTERVAL 24 HOUR
           GROUP BY sensorid
SELECT MAX(id) id, sensorid  FROM sensors GROUP BY sensorid
SELECT sensorlist.id, sensorlist.alias, a.maxvalue, a.minvalue, b.value AS lastvalue
  FROM (
          SELECT sensorid, MAX(value) AS maxvalue, MIN(value) AS minvalue
            FROM readings
           WHERE date >= NOW() - INTERVAL 24 HOUR
           GROUP BY sensorid
       ) AS a
  JOIN (
          SELECT value, sensorid
            FROM sensors
            JOIN (
                   SELECT MAX(id) id FROM sensors GROUP BY sensorid
                 ) AS m ON sensors.id = m.id
       ) AS b ON a.sensorid = b.sensorid
  JOIN sensorlist ON sensorlist.id = a.sensorid
SELECT sensorlist.id, sensorlist.alias, a.maxvalue, a.minvalue, b.value AS lastvalue
  FROM (
          SELECT sensorid, MAX(value) AS maxvalue, MIN(value) AS minvalue,
                 MAX(id) AS maxid
            FROM readings
           WHERE date >= NOW() - INTERVAL 24 HOUR
           GROUP BY sensorid
       ) AS a
  JOIN readings AS b on b.id = a.maxid
  JOIN sensorlist ON sensorlist.id = a.sensorid


Share : facebook icon twitter icon
Which permission do I have to add to the manifest to get gps sensor readings?

Which permission do I have to add to the manifest to get gps sensor readings?


By : Per Haugsöen
Date : March 29 2020, 07:55 AM
I think the issue was by ths following , Which permission needs my application to get access to the location of the user on Android? , you need:
code :
<uses-permission android:name="android.permission.ACCESS_FINE_LOCATION" />
Using XMPP - PEP/PubSub for sensor readings

Using XMPP - PEP/PubSub for sensor readings


By : Dharmendra kumar
Date : March 29 2020, 07:55 AM
This might help you XMPP would certainly be able to handle this use case, and seems a good fit based on the small set of requirements you have shown. I am assuming that the sensor data is small amounts of textual information.
From what I can see, I would not use PEP though, just straight up pubsub with a single node for all the sensors to publish to. Pretty much as simple as it gets.
Strange readings from sensor

Strange readings from sensor


By : abam1046
Date : March 29 2020, 07:55 AM
seems to work fine dwBytes has the bytes actually read. It is very likely that the sensor does not terminate the string input being read with a null byte, because of which cout is printing garbage.
What you can do is to explicitly set input[dwBytes] = '\0' before you call cout.
2 sensor readings fusion (Yaw, pitch)

2 sensor readings fusion (Yaw, pitch)


By : Alan Moore
Date : March 29 2020, 07:55 AM
To fix the issue you can do Basically, this technique is called sensor fusion. Yes, you can use Kalman filter based sensor fusion. Please read this https://home.wlu.edu/~levys/kalman_tutorial/kalman_14.html where it explains without knowing any information about motion model how to perform sensor fusion with an example.
If I explain a little bit more about your problem, you have two sensors which give yaw and pitch. If I follow the same notation which I referred above, C matrix should like, . where I assume that those two reading which comes from two sensors has the same influence. You have to change the dimensionality other matrics. Here (https://github.com/GPrathap/RobotTrajectoryEstimation) you can find some example which I have done. Hope this will help you to resolve your problem.
How can I store sensor readings from raspi to my database?

How can I store sensor readings from raspi to my database?


By : cross
Date : March 29 2020, 07:55 AM
it fixes the issue Unless I'm misunderstanding things, you can simply merge these two scripts, something like
code :
#!/usr/bin/python

import RPi.GPIO as GPIO
import time
import MySQLdb

db = MySQLdb.connect("localhost", "root", "raspberry", "cribdb")

GPIO.setmode(GPIO.BOARD)

GPIO.setup(12, GPIO.IN)  # Right level-1
GPIO.setup(16, GPIO.IN)  # Right level-2
GPIO.setup(18, GPIO.IN)  # Right level-3

last_status = None

while True:
    input_12 = GPIO.input(12)
    input_16 = GPIO.input(16)
    input_18 = GPIO.input(18)
    if input_12 == 1 or input_16 == 1 and input_18 == 0:
        status = "lying down"
    elif input_12 == 1 or input_16 == 1 and input_18 == 1:
        status = "Sitting/Crawling"
    elif input_12 == 0 or input_16 == 0 and input_18 == 1:
        status = "Almost out"
    else:
        status = "Out of the crib"
    time.sleep(1)
    if status != last_status:
        print(status)
        last_status = status
        curs = db.cursor()
        curs.execute(
            """INSERT INTO tbstatus values(NULL, %s)""", (status,)
        )
        db.commit()
Related Posts Related Posts :
  • Why am I getting "Host '192.168.1.220' is not allowed to connect to this MySQL server"?
  • what type of password hash? possible mysql hash?
  • MySQL - Using a date range vs functions
  • Using IS NULL and COALESCE in OrderBy Doctrine Querybuilder
  • How to merge MySQL columns into one column if there are empty values in the table?
  • Cardinality Confusion
  • Renaming column multiple ids in SELECT
  • Group all from and to messages per user
  • How to handled the null values in WHERE IN condition in mysql
  • Is it okay to update primary key field to shift a record to the top?
  • Stop query through pdo
  • How to check whether particular value in field in MySQL exists
  • Execute a statement for every table in a database
  • insertion 74G data into mysql table cost more then 2 days, how to improve insert performance
  • Where can I see the differents "Hibernate Console Configurations" in Eclipse
  • MySql | relational database vs non relational database in terms of Performance
  • Exporting mysql database using mysqldump including procedures
  • Trigger MySQL not working
  • How do I update values for individual rows quickly?
  • MySQL join query with multiple where condition
  • MySQL stored procedure is not working
  • MySQL - Order By exact match at the end
  • Select max date or null date from same table MySQL
  • Best solution workaround for needing a mysql event on insert?
  • MySQL select several columns of several tables at the same time without using JOIN
  • Is it more efficient to have a table with one of its columns mostly empty or make a new table and refer to it?
  • mysql data truncated with ñ character
  • MySQL Distinct Active Users for the last month query
  • Get all rows that match another attribute in the same table
  • MySQL statement to get min value with max ID
  • DATEDIFF Current/Date for Last Record
  • What is the logic or procedure for combining multiple DB query's results into one elegant table?
  • Modify TIMESTAMP MySQL for use as Primary Key
  • Django - Efficiently bulk create inherited models
  • MySQL Dual table Join Dual table
  • Ruby on Rails - database where sorted by id
  • MySQL 5.6 : Access denied for user 'root' @ 'localhost' when password contains backslash
  • sql query case dateadd
  • Click on one button multiple times and mySql
  • Select daily amount of specific values from same column
  • How to send e-mail to users within a database?
  • Updating self joined table
  • Convert query to a Left-Join (?) to include userid's without a match
  • How to Convert from .Net TimeZone to MySQL TimeZone
  • Syntax Error for MySQL Trigger
  • SQL Optimization - 3 Tables - Multiple SUMs - 20k records - 12 Seconds
  • MySQL: Why does query give NULL as a result of SUM?
  • Simple query issue with multiple tables and mismatching IDs
  • MySQL join multiple lines
  • Performing JOIN between two tables
  • Get specific rows from group by clause in PostgreSQL
  • MySQL query to get the sum of a column
  • Why mongoDB takes less time for Select than Fetch time?
  • Laravel 4.2 BIT datatype issue
  • MySQL Join Query confuse
  • Select distinct rows by count in descending order
  • Select query with comma and join
  • Adding a percentage (%) sign to each value while using select sql statement
  • How to access data from foreign key table
  • How to use the SQL MINUS keyword
  • shadow
    Privacy Policy - Terms - Contact Us © ourworld-yourmove.org