calculation of a fraction stored in a text column

classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

calculation of a fraction stored in a text column

Patrick Proniewski
Hello,

I'm coming back with my EXIF database. I have a TEXT column, ExposureTime, where I store the EXIF representation of photograph's exposure time. Sample values: "1/6000", "1/250", "0.5", "1", "6"...
I need to retain this representation, because it's how photographers deal with exposure time. 0.004 would be great for math, sorting, etc. but the real life thing is "1/250".

My problem is that my database holds too many different values for ExposureTime, so the resulting plot is unreadable. I want to be able to "bin" those values to create a proper histogram. It's not possible to "bin" string values, because SQLite has no idea that "1/60" has nothing to do near "1/6000".

I need to convert strings like "1/6000" and "1/250" into their REAL counterparts "0.000166", "0.004" during my SELECT request for "binning"/sorting and counting purposes. I've started to work on an over-complex substr()+instr() combo that is not finished yet, but will probably be a dead-end.

Is there a straightforward way to do this, instead of conditionally decomposing the TEXT and recomposing a mathematical expression that SELECT can calculate?

regards,
Patrick

(you can Cc me, I'm subscribed to digest)
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: calculation of a fraction stored in a text column

Kevin Martin

On 20 Feb 2014, at 12:54, Patrick Proniewski <[hidden email]> wrote:

> My problem is that my database holds too many different values for ExposureTime, so the resulting plot is unreadable. I want to be able to "bin" those values to create a proper histogram. It's not possible to "bin" string values, because SQLite has no idea that "1/60" has nothing to do near "1/6000".

Are you able to use an extension? A custom collation on the ExposureTime column seems pretty simple (although not thought about it in detail). You should then be able to bin the values as they are.

Thanks,
Kevin  


_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: calculation of a fraction stored in a text column

Patrick Proniewski
hi Kevin,

On 20 févr. 2014, at 14:11, Kevin Martin wrote:

>
> On 20 Feb 2014, at 12:54, Patrick Proniewski <[hidden email]> wrote:
>
>> My problem is that my database holds too many different values for ExposureTime, so the resulting plot is unreadable. I want to be able to "bin" those values to create a proper histogram. It's not possible to "bin" string values, because SQLite has no idea that "1/60" has nothing to do near "1/6000".
>
> Are you able to use an extension? A custom collation on the ExposureTime column seems pretty simple (although not thought about it in detail). You should then be able to bin the values as they are.


Thanks for you reply. In fact I would like the script to remain portable. I'm running all this on Mac OS X 10.6.8 (sqlite3 3.6.12) but I plan to share it and use it on FreeBSD too. And developing a extension is probably out of my reach :)

Patrick
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: calculation of a fraction stored in a text column

Simon Slavin-3

On 20 Feb 2014, at 1:56pm, Patrick Proniewski <[hidden email]> wrote:

> Thanks for you reply. In fact I would like the script to remain portable. I'm running all this on Mac OS X 10.6.8 (sqlite3 3.6.12) but I plan to share it and use it on FreeBSD too. And developing a extension is probably out of my reach :)

I don't think the thing you want to do can be done easily within SQLite.  I would expect to see it done in whatever programming language you're using.

One approach would be to save two columns when you extract from EXIF.  One has exactly the text from the EXIF column.  Another would be to save the factor as a REAL number.  Do some string processing to get the bit before the '/' and the bit after it, and divide one by the other.

You might be able to do the same calculation inside SQLite but it'll be horrid to look at.  Something like

SELECT expTime,substr(expTime,1,instr(expTime,'/'))/substr(expTime,instr(expTime,'/')+1) AS etAsReal FROM photos

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: calculation of a fraction stored in a text column

Donald Griggs
In reply to this post by Patrick Proniewski
Hi Patrick,

Am I right that fractional exposures will always have one as the numerator?
  I.e., you might record an exposure as "1.5" seconds, but never as "3/2"
seconds?   If so, then that might simplify things.

The example below creates a column named "canon" to hold the canonical
exposure string value for sorting and grouping.
It assumes any non-decimal fractions will begin with the string "1/"


CREATE TABLE expo (str TEXT, canon TEXT);
INSERT INTO expo(str) VALUES ('1/30'), ('1/500'), ('1/6000'), ('.5'), ('6');


UPDATE expo
 SET canon =
 CASE WHEN substr(str,1,2) == '1/'
   THEN printf( '%014.8f',  (1.0 / substr(str,3)) )
 ELSE
   printf( '%014.8f', str)
 END;

.mode tabs
SELECT * FROM expo;

1/30    00000.03333333
1/500   00000.00200000
1/6000  00000.00016667
.5      00000.50000000
6       00006.00000000

Note that I used a newer sqlite version which includes the handy printf()
function.

Donald
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: calculation of a fraction stored in a text column

Patrick Proniewski
Donald,

On 20 févr. 2014, at 15:16, Donald Griggs wrote:

> Am I right that fractional exposures will always have one as the numerator?   I.e., you might record an exposure as "1.5" seconds, but never as "3/2" seconds?   If so, then that might simplify things.

Yes, no 3/2, only 1/x and regular REALs.


> The example below creates a column named "canon" to hold the canonical exposure string value for sorting and grouping.
> It assumes any non-decimal fractions will begin with the string "1/"

Thank you for this example, the idea of storing the computed number into the database is very good and made me rethink the initial database feeding. I'm using exiftool to script EXIF reading from my files. exiftool has a very nice option that allows the reading of raw data. Exposure Time, displayed in "raw" is the REAL equivalent to my strings: 1/200 is read as 0.005. Very handy.
I'm going to redesign my database in order to include raw data aside human-readable data when I need it.

thanks,
Patrick
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: calculation of a fraction stored in a text column

Patrick Proniewski
In reply to this post by Patrick Proniewski
Simon,

> Date: Thu, 20 Feb 2014 14:04:59 +0000
> From: Simon Slavin
>
> On 20 Feb 2014, at 1:56pm, Patrick Proniewski <[hidden email]> wrote:
>
>> Thanks for you reply. In fact I would like the script to remain portable. I'm running all this on Mac OS X 10.6.8 (sqlite3 3.6.12) but I plan to share it and use it on FreeBSD too. And developing a extension is probably out of my reach :)
>
> I don't think the thing you want to do can be done easily within SQLite.  I would expect to see it done in whatever programming language you're using.
>
> One approach would be to save two columns when you extract from EXIF.  One has exactly the text from the EXIF column.  Another would be to save the factor as a REAL number.  Do some string processing to get the bit before the '/' and the bit after it, and divide one by the other.
>
> You might be able to do the same calculation inside SQLite but it'll be horrid to look at.  Something like
>
> SELECT expTime,substr(expTime,1,instr(expTime,'/'))/substr(expTime,instr(expTime,'/')+1) AS etAsReal FROM photos


I'm going to rework my database to include proper "already calculated" columns, but using bash to do the math would have been a little bit unpleasant (not enough streamlined for my liking). Fortunately exiftool has an option to extract raw value for EXIF tags, and I've found a way to read them exactly as I need, "one-pot".

thanks,
Patrick
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: calculation of a fraction stored in a text column

R Smith
In reply to this post by Patrick Proniewski
Ensure you store the string representation of the reals (floats w/e) of precise numerical format and length, such that:
0.3, 12 and 1.456 all look alike and sorts correct ex:

"000.300000"   and
"001.456000"   and
"012.000000"   etc.

or whatever similar format you may choose as Simon (I think) suggested so that you will have those values correctly
sorted/grouped/distinct-ed in any string-based SQL function. A further enhancement I like to do in such cases is add some alpha char
in front, like:
"F01.456000"   and
"F12.000000"   etc.
so that any output I do create, which invariably ends up being copied to excel or calc or such, does not get confused with actual
numerals and stripped of leading zeroes etc. It's much easier to apply a formula to make that into numerals should the need arise,
than to avoid it being done automatically sans the leading alpha char.



On 2014/02/20 20:50, Patrick Proniewski wrote:

> Donald,
>
> On 20 févr. 2014, at 15:16, Donald Griggs wrote:
>
>> Am I right that fractional exposures will always have one as the numerator?   I.e., you might record an exposure as "1.5" seconds, but never as "3/2" seconds?   If so, then that might simplify things.
> Yes, no 3/2, only 1/x and regular REALs.
>
>
>> The example below creates a column named "canon" to hold the canonical exposure string value for sorting and grouping.
>> It assumes any non-decimal fractions will begin with the string "1/"
> Thank you for this example, the idea of storing the computed number into the database is very good and made me rethink the initial database feeding. I'm using exiftool to script EXIF reading from my files. exiftool has a very nice option that allows the reading of raw data. Exposure Time, displayed in "raw" is the REAL equivalent to my strings: 1/200 is read as 0.005. Very handy.
> I'm going to redesign my database in order to include raw data aside human-readable data when I need it.
>
> thanks,
> Patrick
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users