Re: [sqlite] calculation of a fraction stored in a text column
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
Simon, > Date: Thu, 20 Feb 2014 14:04:59 + > From: Simon Slavin > > On 20 Feb 2014, at 1:56pm, Patrick Proniewskiwrote: > >> 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
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
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
On 20 Feb 2014, at 1:56pm, Patrick Proniewskiwrote: > 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
hi Kevin, On 20 févr. 2014, at 14:11, Kevin Martin wrote: > > On 20 Feb 2014, at 12:54, Patrick Proniewskiwrote: > >> 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
On 20 Feb 2014, at 12:54, Patrick Proniewskiwrote: > 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