0
139
VisualizaçõesRoot Mean Square in Postgresql
I am working on Image Duplication Library which uses ML to predict image similarity. In this process Root Mean Square is used to calculate the similarity between two images ( I'm not going into how). The function which does it looks like this.
# Function that calulates the mean squared error (mse) between two image matrices
def _mse(imageA, imageB):
err = np.sum((imageA.astype("float") - imageB.astype("float")) ** 2)
err /= float(imageA.shape[0] * imageA.shape[1])
return err
My model worked fine when I tested it on folders containing 5K images but it took way too much time. So I decided to refactor my code and store all tensors in a database. Why?
If I store the tensors of all images in a database than query upcoming image tensor with it I will get result quickly. Looping over all images again and again + matching one image RMS with others will result into many combinations which will take time.
Solution
If I store all tensors which are list or Array and store them in database like Postgres than I can easily query them with RMS w.r.t. to getting all images at once than looping over them and finding out duplicity.
I need your help to figure out if there is any way to query Postgres for getting images with closest RMS
Something like this:
SELECT ID_PARTNER, ID_ACCOUNT
, SQRT(Avg( POWER(Act_F_1 - Pred_F_1 , 2) ) ) as feature_1_rmse
FROM ...
GROUP BY ID_PARTNER, ID_ACCOUNT
Similar Question: Get RMSE score while fetching data from the Table directly.Write a query for that
This is what DataBase looks like
1 Respostas
Responde à pergunta0
If I'm understanding what you want to do correctly you just need to create a column to hold the RMSE calculation and then update the table to do the calculation.
UPDATE table_name as orig set RMSE=(select SQRT(Avg( POWER(Act_F_1 - Pred_F_1 , 2) ) ) from table_name as copy where orig.ID_PARTNER=copy.ID_PARTNER and orig.ID_ACCOUNT=copy.ID_ACCOUNT GROUP BY ID_PARTNER, ID_ACCOUNT);
edit: I hadn't noticed the group by. I think this creates a pre-calculated field correctly though it is likely very inefficient as I believe it will recalculate for each ID_PARTNER ID_ACCOUNT pair. There might be a better way to do this in SQL. I'd just SELECT DISTINCT ID_PARTNER, ID_ACCOUNT from table_name then run through that in code and SELECT SQRT(Avg( POWER(Act_F_1 - Pred_F_1 , 2) ) ) from table_name where ID_PARTNER=? and ID_ACCOUNT=? for each of tuples I have then update table_name set RMSE=? for each of those values. Alternatively you could have a table that's just ID_PARTNER, ID_ACCOUNT, RMSE and just put the results in there once.
Then the table in the database will have all the pre-computed values and you can run queries on it. If you want things close to a value of search_RMSE
SELECT ...,ABS(RMSE - search_RMSE) as RMSE_DIFF from table_name order by RMSE_DIFF;
That isn't necessarily a super optimised way to do the calcs. You talk about duplicity which would mean you're looking for identical values but your calcs are floating point so you could even end up with values that should be identical but don't end up that way. Do you only care about the closest value or do you care about a number of the closest values? If you just make a giant table and sort it by RMSE the closest value for each row will be the row above it or the row below and the collection of closest values will be just growing up and down from the row.
I'm not entirely sure why you want to involve a database in this unless you can't fit all the data in memory. If that is the case you could just put the raw data in the database and then use the select you reference with an order by clause and store that in chunks outside the db if you want.
Anyway lots of ways to do this, myself I'd just use a cython module to avoid the extremely slow math in python (it's notably faster than numpy and is almost identical to python,) and do all of this with in memory data structures and maybe some temporary files.