6 ms·

PixQL: SQL for image processing

85 points by GuiA 11y ago

hmans 11y ago
No.
mappu 11y ago
Interesting! Can you support multiple image buffers? e.g. for xor or morphing operations. I would suggest taking a look at avisynth as an example of a media processing language.
- itsbits 11y ago
  may be we can use BETWEEN..
jitl 11y ago
This looks amazing, but it might be easier to express these sorts of ideas with a simple functional language. Does anyone know of a similar library in a lisp or ML?
- abecedarius 11y ago
  There were some systems in the 90s in Common Lisp and Scheme, like http://www.nic.funet.fi/index/Science/audio/tmp/forweb.pdf http://www.nic.funet.fi/index/Science/audio/tmp/forweb.pdf (I've forgotten the name or authors of the Scheme one.)
ddebernardy 11y ago
Does this have any practical use? It seems like the image equivalent of treating an HTML file as a huge string of characters -- instead of as the representation of a DOM tree. Which makes very little sense. Plus, you seldom want to do trivial things like selecting red pixels and flipping their color to green. You want to select red eyes and flip those to their correct color.
- kubiiii 11y ago
  I find this interesting, but I also would like to see more complex use cases. For example using using some kind of select union on several images to output a synthetic image highlighting differences or similarities?
  - JonaSmith 11y ago
    Same here. A more complex exemple/case study would be great.
- Keyframe 11y ago
  Declarative language seems appropriate for image manipulation, at least for selecting an area for operation and then running a kernel over it. It would be interesting to see how one could implement, let's say, luminance calculation (any type) or anything that needs to go back and forth over pixels. Also, pixels which aren't 24-bit per pixel, but more.
- phildo3 11y ago
  I initially made this as a tool to help alongside game development - specifically spritesheet editing, and futzing with textures-as-data and the like. Was never intended to be a high level replacement for photoshop or anything hah. (ugh, what is this "limit 2 posts per hour" madness... lol)
kbenson 11y ago
If you are going to use SQL select, why use a separate operate statement when you could just use SQL's method for this, update? For that matter, if you are going to use SQL syntax, I would imagine you would want to keep one of the main features, which is joining of datasets. Being able to join an image with itself in some manner (or another image...), would be really useful. Instead of globals, use the images as tables of pixels with those attributes. Imagine breaking an image down into pixels, actually storing them into a database, performing some queries, and outputting the result. Something equivalent to that (sans database) sounds interesting to me.
- richmarr 11y ago
  There are lots of fun things you can do with a few gigs of images and an inverted index.
- phildo3 11y ago
  Author here- My reason for deviating slightly from traditional SQL syntax is that I don't find it to be super intuitive. Yes, "UPDATE color SET r = 255 WHERE col = 100" is a pretty straightforward looking thing for those of us familiar with SQL, but simple things like the fact that you set the value before deciding what you're setting the value of are a bit off putting. The separation of queries into multiple statements along with this fact lead to just universally cleaner and more consistent looking syntax even as queries get complex. That said, I'm still very much in the "trying things out" phase w.r.t. syntax 'n such. So the feedback is appreciated! (And you're not the only one who has suggested this...) Curious what you'd expect the result of a join to be? I assume you mean across multiple images? Currently, that would be a bit odd as the function of "select" exists more along the lines of "composing a mask" more than it does "fetching data", so not entirely sure how that'd fit in. Still very interesting. If you're interested in fleshing that idea out here, it could be helpful for me! Thanks!
  - kubiiii 11y ago
    You can imagine composing a mask from several images using Join or Union. Summing up images to make HDR, only on some areas of the pictures based on different criteria to produce the mask. Another possibility would be to output a mosaic based on properties of a set of pictures. I also vote for the use of UPDATE and also the use of INSERT to insert some part of image 1 x mask in image 2 x mask, or insert geometric features x mask. I think this project is a great idea, thanks for submiting it.
joelthelion 11y ago
SQL is awesome, but it doesn't mean you have to keep the CAPITAL LETTERS.
- creshal 11y ago
  Aren't SQL keywords case insensitive anyway?
  - charriu 11y ago
    Everything except quoted strings is case insensitive.
neuro_sys 11y ago
I like the part where he mentions why using a traditional GUI tool is more difficult than using an expressive language with basic syntax. "But in photoshop, I instead am relying on the problem being solved externally ("ok, how can I find the right menu that deals with this kind of operation? what's the word the designers of photoshop used to describe this... I hope if I google it in plain text it might link to a forum post or something of someone with the same problem?")." This is valid for most cases when GUIs are harder to use than a CLI with syntax.
no_gravity 11y ago
Wouldn't it be easier to use JavaScript? PixQL: SELECT WHERE COLOR = #FF0000FF; OPERATE SET COLOR = #00FF00FF JS : if (rgba=='FF0000FF') rgba='00ff00ff' PixQL: OPERATE SET G = R JS : g=r PixQL: SELECT WHERE ROW % 100 = 0 OR COL % 100 = 0; OPERATE SET COLOR = WHITE JS : if (!(row%100) || !(col%100)) rgba="ffffffff"
- brobdingnagian 11y ago
  No I'm pretty sure it would be easier (aka shorter) using 7zipped brainfuck.
  - curiously 11y ago
    I don't see how that's any easier.
- phildo3 11y ago
  maybe. but as it stands, so long as you can compile it, it doesn't need any external libraries or installs or anything like that, which is something I value (I assume your post is hinting at using node, rather than some in-browser thing... if not, there are plenty of other reasons I prefer to keep it out of the browser hah).
juleska 11y ago
where I can apply it ? i mean, what would be a usefull usage for the lib?
paulasmuth 11y ago
I love this! Now all we need is some higher level DRAW operations (draw a line from here to there, draw a piece of rendered text here) and we can glue PixQL and ChartSQL together to implement SQL query result visualization to bitmaps completely in SQL (without going through SVG). PS: Under which license is the PixSQL code available?
- phildo3 11y ago
  Thanks for the feedback! Definitely considering where to prioritize features, and drawing or "select where in polygon(x1,y1,x2,y2,...)", etc... are definitely on the table (first I need to fiddle with some compatibility stuff and clean up the code a bit). As far as the license, I haven't yet decided... pretty new/naive when it comes to deciding something like that... I don't plan on selling it for money, and I obviously like that the source is available, and want contributions, etc... but yeah. not sure. (Any tips would be welcome :P)
  - mappu 11y ago
    >not sure. (Any tips would be welcome :P) Check out http://choosealicense.com/ http://choosealicense.com/ .
TeeWEE 11y ago
So how do i blur an image, probably its not possible with this kind of SQL language, right? Or can you do something like this: SELECT *: OPERATE SET COLOR AVG(ROW[-1], ROW[0], ROW[+1])) To apply a 1 pixel horizontal blur?
- kubiiii 11y ago
  You could even apply it to the mask resulting from the select. Mathematical morphology, advection using several pictures, interpolation, there are many use case that would be possible with a single query. But maybe I'm too optimistic with the current possibilities. Promising project anyway.
- phildo3 11y ago
  It's possible, but unfortunately a bit obtuse in this language (you might want to just use photoshop for something like that...). One way you could do it is as follows: OPERATE SET R = (IN[COL,ROW-1].R + IN[COL-1,ROW] + ...)/8; Unfortunately, that's just the R channel. I should find a way to consider COLOR that would be smart enough to just have it apply the operation across all channels. (right now, the COLOR keyword is nice for simple "set color to this" type of things)
  - phildo3 11y ago
    (not to mention correcting for gamma depending on exactly what you want to do with your blur... again, pixQL probably isn't the right tool for this as it currently stands hah)
rburhum 11y ago
It reminds me of Map Algebra https://www.nrem.iastate.edu/files/w11-MapAlgebra_presentation5.pdf https://www.nrem.iastate.edu/files/w11-MapAlgebra_presentati... Very cool.

85 points by GuiA 11y ago

hmans 11y ago

No.

mappu 11y ago

Interesting! Can you support multiple image buffers? e.g. for xor or morphing operations. I would suggest taking a look at avisynth as an example of a media processing language.

itsbits 11y ago
may be we can use BETWEEN..

jitl 11y ago

This looks amazing, but it might be easier to express these sorts of ideas with a simple functional language. Does anyone know of a similar library in a lisp or ML?

abecedarius 11y ago
There were some systems in the 90s in Common Lisp and Scheme, like http://www.nic.funet.fi/index/Science/audio/tmp/forweb.pdf http://www.nic.funet.fi/index/Science/audio/tmp/forweb.pdf (I've forgotten the name or authors of the Scheme one.)

ddebernardy 11y ago

Does this have any practical use? It seems like the image equivalent of treating an HTML file as a huge string of characters -- instead of as the representation of a DOM tree. Which makes very little sense. Plus, you seldom want to do trivial things like selecting red pixels and flipping their color to green. You want to select red eyes and flip those to their correct color.

kubiiii 11y ago
I find this interesting, but I also would like to see more complex use cases. For example using using some kind of select union on several images to output a synthetic image highlighting differences or similarities?
- JonaSmith 11y ago
  Same here. A more complex exemple/case study would be great.
Keyframe 11y ago
Declarative language seems appropriate for image manipulation, at least for selecting an area for operation and then running a kernel over it. It would be interesting to see how one could implement, let's say, luminance calculation (any type) or anything that needs to go back and forth over pixels. Also, pixels which aren't 24-bit per pixel, but more.
phildo3 11y ago
I initially made this as a tool to help alongside game development - specifically spritesheet editing, and futzing with textures-as-data and the like. Was never intended to be a high level replacement for photoshop or anything hah. (ugh, what is this "limit 2 posts per hour" madness... lol)

kbenson 11y ago

If you are going to use SQL select, why use a separate operate statement when you could just use SQL's method for this, update? For that matter, if you are going to use SQL syntax, I would imagine you would want to keep one of the main features, which is joining of datasets. Being able to join an image with itself in some manner (or another image...), would be really useful. Instead of globals, use the images as tables of pixels with those attributes. Imagine breaking an image down into pixels, actually storing them into a database, performing some queries, and outputting the result. Something equivalent to that (sans database) sounds interesting to me.

richmarr 11y ago
There are lots of fun things you can do with a few gigs of images and an inverted index.
phildo3 11y ago
Author here- My reason for deviating slightly from traditional SQL syntax is that I don't find it to be super intuitive. Yes, "UPDATE color SET r = 255 WHERE col = 100" is a pretty straightforward looking thing for those of us familiar with SQL, but simple things like the fact that you set the value before deciding what you're setting the value of are a bit off putting. The separation of queries into multiple statements along with this fact lead to just universally cleaner and more consistent looking syntax even as queries get complex. That said, I'm still very much in the "trying things out" phase w.r.t. syntax 'n such. So the feedback is appreciated! (And you're not the only one who has suggested this...) Curious what you'd expect the result of a join to be? I assume you mean across multiple images? Currently, that would be a bit odd as the function of "select" exists more along the lines of "composing a mask" more than it does "fetching data", so not entirely sure how that'd fit in. Still very interesting. If you're interested in fleshing that idea out here, it could be helpful for me! Thanks!
- kubiiii 11y ago
  You can imagine composing a mask from several images using Join or Union. Summing up images to make HDR, only on some areas of the pictures based on different criteria to produce the mask. Another possibility would be to output a mosaic based on properties of a set of pictures. I also vote for the use of UPDATE and also the use of INSERT to insert some part of image 1 x mask in image 2 x mask, or insert geometric features x mask. I think this project is a great idea, thanks for submiting it.

joelthelion 11y ago

SQL is awesome, but it doesn't mean you have to keep the CAPITAL LETTERS.

creshal 11y ago
Aren't SQL keywords case insensitive anyway?
- charriu 11y ago
  Everything except quoted strings is case insensitive.

neuro_sys 11y ago

I like the part where he mentions why using a traditional GUI tool is more difficult than using an expressive language with basic syntax. "But in photoshop, I instead am relying on the problem being solved externally ("ok, how can I find the right menu that deals with this kind of operation? what's the word the designers of photoshop used to describe this... I hope if I google it in plain text it might link to a forum post or something of someone with the same problem?")." This is valid for most cases when GUIs are harder to use than a CLI with syntax.

no_gravity 11y ago

Wouldn't it be easier to use JavaScript? PixQL: SELECT WHERE COLOR = #FF0000FF; OPERATE SET COLOR = #00FF00FF JS : if (rgba=='FF0000FF') rgba='00ff00ff' PixQL: OPERATE SET G = R JS : g=r PixQL: SELECT WHERE ROW % 100 = 0 OR COL % 100 = 0; OPERATE SET COLOR = WHITE JS : if (!(row%100) || !(col%100)) rgba="ffffffff"

brobdingnagian 11y ago
No I'm pretty sure it would be easier (aka shorter) using 7zipped brainfuck.
- curiously 11y ago
  I don't see how that's any easier.
phildo3 11y ago
maybe. but as it stands, so long as you can compile it, it doesn't need any external libraries or installs or anything like that, which is something I value (I assume your post is hinting at using node, rather than some in-browser thing... if not, there are plenty of other reasons I prefer to keep it out of the browser hah).

juleska 11y ago

where I can apply it ? i mean, what would be a usefull usage for the lib?

paulasmuth 11y ago

I love this! Now all we need is some higher level DRAW operations (draw a line from here to there, draw a piece of rendered text here) and we can glue PixQL and ChartSQL together to implement SQL query result visualization to bitmaps completely in SQL (without going through SVG). PS: Under which license is the PixSQL code available?

phildo3 11y ago
Thanks for the feedback! Definitely considering where to prioritize features, and drawing or "select where in polygon(x1,y1,x2,y2,...)", etc... are definitely on the table (first I need to fiddle with some compatibility stuff and clean up the code a bit). As far as the license, I haven't yet decided... pretty new/naive when it comes to deciding something like that... I don't plan on selling it for money, and I obviously like that the source is available, and want contributions, etc... but yeah. not sure. (Any tips would be welcome :P)
- mappu 11y ago
  >not sure. (Any tips would be welcome :P) Check out http://choosealicense.com/ http://choosealicense.com/ .

TeeWEE 11y ago

So how do i blur an image, probably its not possible with this kind of SQL language, right? Or can you do something like this: SELECT *: OPERATE SET COLOR AVG(ROW[-1], ROW[0], ROW[+1])) To apply a 1 pixel horizontal blur?

kubiiii 11y ago
You could even apply it to the mask resulting from the select. Mathematical morphology, advection using several pictures, interpolation, there are many use case that would be possible with a single query. But maybe I'm too optimistic with the current possibilities. Promising project anyway.
phildo3 11y ago
It's possible, but unfortunately a bit obtuse in this language (you might want to just use photoshop for something like that...). One way you could do it is as follows: OPERATE SET R = (IN[COL,ROW-1].R + IN[COL-1,ROW] + ...)/8; Unfortunately, that's just the R channel. I should find a way to consider COLOR that would be smart enough to just have it apply the operation across all channels. (right now, the COLOR keyword is nice for simple "set color to this" type of things)
- phildo3 11y ago
  (not to mention correcting for gamma depending on exactly what you want to do with your blur... again, pixQL probably isn't the right tool for this as it currently stands hah)

rburhum 11y ago

It reminds me of Map Algebra https://www.nrem.iastate.edu/files/w11-MapAlgebra_presentation5.pdf https://www.nrem.iastate.edu/files/w11-MapAlgebra_presentati... Very cool.