How to limit location storage in Home Assistant?

Question

Is it possible to limit or disable storing GeoLocation data in Home Assistant (e.g. sensor.<device>_geocoded_location)?

I don't want to disable geolocation all together, because in the present (right now), I do want to know where a device is located to run zone-based automations.

However, I do not want to retain this data (beyond a certain period of time anyway).

Answer 1

One way, perhaps the simplest way, would be to create a UNIQUE index on the respective column or columns that form the data for which you want only 1 row to exist and then use an INSERT OR IGNORE. If the UNIQUE constraint (rule) is broken then the INSERT will be IGNOREd.

• INSERT OR REPLACE will instead of IGNOREing the insert UPDATE(replace) the existing row being inserted if it is not UNIQUE. (see the example/demonstration below)

The PRIMARY KEY, which can be a composite (made up of more than 1 column) is implicitly UNIQUE so is a candidate for the index

Perhaps consider the following demonstration, that may well mimic the assumed requirement from your question.

DROP TABLE IF EXISTS thetable;
CREATE TABLE IF NOT EXISTS the_table (
    device TEXT, 
    location_lattitude REAL, 
    location_longitude REAL, 
    other_column TEXT /* etc */, 
    PRIMARY KEY (device,location_lattitude,location_longitude) /* PRIMARY KEY IS IMPLICITLY UNIQUE */
);
INSERT OR IGNORE INTO the_table VALUES
    ('d1',100.1,200.1,'blah1'), /* WILL INSERT AS UNIQUE (row rows exist)*/
    ('d2',100.1,200.1,'blah1'), /* WILL INSERT AS UNIQUE (device is unique even though location isn't)*/
    ('d1',100.1,200.1,'blah2'), /* WILL NOT INSERT AS NOT UNIQUE (device,lat,long combination exists SEE BELOW)*/
    ('d3',100.1,200.1,'blah1'), /* WILL INSERT AS UNIQUE (device unique)*/
    ('d1',300.1,200.1,'blah1')  /* WILL INSERT AS UNIQUE (lat not unqiue)*/
;
SELECT *,rowid FROM the_table;
/* ALL insertes will be ignored */
INSERT OR IGNORE INTO the_table VALUES
    ('d1',100.1,200.1,'blah1'), /* WILL INSERT AS UNIQUE (row rows exist)*/
    ('d2',100.1,200.1,'blah10'), /* WILL INSERT AS UNIQUE (device is unique even though location isn't)*/
    ('d1',100.1,200.1,'blah20'), /* WILL NOT INSERT AS NOT UNIQUE (device,lat,long combination exists SEE BELOW)*/
    ('d3',100.1,200.1,'blah10'), /* WILL INSERT AS UNIQUE (device unique)*/
    ('d1',300.1,200.1,'blah10')  /* WILL INSERT AS UNIQUE (lat not unqiue)*/
;
SELECT *,rowid FROM the_table;
/* instead of ignoring the insert, the existing unique row is replaced with the updated/changed data */
/* i.e. ALL ROWS WILL BE UPDATED */
INSERT OR REPLACE INTO the_table VALUES
    ('d1',100.1,200.1,'blah10'), /* WILL INSERT AS UNIQUE (row rows exist)*/
    ('d2',100.1,200.1,'blah10'), /* WILL INSERT AS UNIQUE (device is unique even though location isn't)*/
    ('d1',100.1,200.1,'blah20'), /* WILL NOT INSERT AS NOT UNIQUE (device,lat,long combination exists SEE BELOW)*/
    ('d3',100.1,200.1,'blah10'), /* WILL INSERT AS UNIQUE (device unique)*/
    ('d1',300.1,200.1,'blah10')  /* WILL INSERT AS UNIQUE (lat not unqiue)*/
;
SELECT *,rowid FROM the_table;
DROP TABLE IF EXISTS the_table;

The first insert will insert 4 of the 5 (see comments in the SQL) as per:-

The second insert will not change anything as every row has already been inserted and thus will be in conflict with the UNIQUE PRIAMRY KEY:-

• i.e. the ONLY difference is that this is Result 2 not Result 1

However, the third insert will not insert any rows BUT WILL update all rows (actually delete and insert as that is how REPLACE works):-

As can be seen the new value for the other_column has been changed accordingly.

Note the rowid. This is a normally hidden column that will exist in all normal tables (not in tables defined with the WITHOUT ROWID clause). It's value will be generated unless aliased and a value is provided. It will typically be 1 greater than the highest existing rowid.

The result clearly shows that the rowid has changed. This is because the REPLACE deletes the row and then inserts another and thus generates another rowid that is 1 greater than highest existing rowid.

• e.g.

  1. when the attempt to insert the first row, when using OR REPLACE, rowid 1 is deleted, then as rowid 4 exists, the replaced row, is inserted with a rowid of 4+1 = 5.
  2. when the attempt to insert the second, rowid 2 becomes 6 (5+1)
  3. when the attempt to insert the third, as it conflicts with the first, then rowid 5 (which was originally 1) becomes 7 (6+1)
     • hence why d1 drops down in the list (which is displayed in order of the rowid as the Query Optimiser decides that is the most efficient index to process all the rows, i.e. the rowid, at the time of the SELECTS has changed from 1 to 7 so d2 with a rowid of 6 is output first (of course an ORDER BY may change this))
  4. forth and rowid 3 becomes 8, fifth and rowid 4 becomes 9

• hence why the rowid was included in the selects. Of course the rowid is frequently ignored (although it can be useful at times)

The above is just an overview of some of the pertinent matters. You may wish to refer to (and links contained in the following) for a more comprehensive explanation :-

• https://www.sqlite.org/lang_createtable.html#the_primary_key
• https://www.sqlite.org/lang_createtable.html#unique_constraints
• https://www.sqlite.org/lang_createtable.html#rowids_and_the_integer_primary_key
• https://www.sqlite.org/lang_conflict.html