Re: [sqlite] calculation of a fraction stored in a text column

[RSmith]

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.30"   and
"001.456000"   and
"012.00"   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.00"   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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] calculation of a fraction stored in a text column

[Patrick Proniewski]

Simon,

> Date: Thu, 20 Feb 2014 14:04:59 +
> From: Simon Slavin 
> 
> On 20 Feb 2014, at 1:56pm, Patrick Proniewski  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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] calculation of a fraction stored in a text column

[Donald Griggs]

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/300.0333
1/500   0.0020
1/6000  0.00016667
.5  0.5000
6   6.

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

Donald
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] calculation of a fraction stored in a text column

[Simon Slavin]

On 20 Feb 2014, at 1:56pm, Patrick Proniewski  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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 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  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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] calculation of a fraction stored in a text column

[Kevin Martin]

On 20 Feb 2014, at 12:54, Patrick Proniewski  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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users