This is question regarding storing of image in PostgreSQL and display in OpenLayers popup through GeoServer (using WMS service).

PostgreSQL is installed in my virtual machine.

I tried to store image in my spatial table where I added new image column(bytea) then by using following query

update latlong set image = 'c:\\img.jpg' where gid = 1;

I could be able to store image but I think image not stored but its path.

Now when I click on my overlays map then I can see other attributes plus image attribute also in popup but no image just some encrypted code which I did not be able to understand.

So my first question is that if that's the correct way to store image so that I can get image attribute with image using WMS service by OpenLayers.

and second question if this is not right way then please give me some information how to accomplish that. I want to display image in popup with other attribute by using database which is PostgreSQL. Please if anyone can help I am desperate here...

  • OpenLayers doesn't have a WMS service, openlayers consumes WMS services provided elsewhere.
    – nmtoken
    Mar 13, 2019 at 15:14

3 Answers 3


there is no correct way. some people prefer store big binaries to disk and other to postgresql using bytea or lob type . You can store images into postgresql as bytea or raster (postgis 2.0) or lob. for more info https://stackoverflow.com/questions/54500/storing-images-in-postgresql

basicly you convert that bytea in your javascipt to image class and display it

  • Thanks for reply Simplexio but I need more explanation.First of all I want to tell you about my database i.e. about my table which consist of all attribute columns with new added image columns(bytea).Now can u tell me how will I convert that bytea in my javascript to image class and display in same popup with other attributes.Please don't mind but needed more explanation.Thanks in advance.
    – Adarsh
    Apr 4, 2013 at 11:11
  • Actually it seems to be little more comlicated in javascrip. When you get data from postgresql you base64encode that bytea column, in receiver end you follow this example danielmclaren.com/node/90 and you should be good. Apr 4, 2013 at 11:32
  • It seems bit complicated could you give me some other link or example where this kind of query has been solved in easy way.Sorry for asking again but if you have any other example please help me.Thanks in advance.
    – Adarsh
    Apr 4, 2013 at 11:55
  • Example define SQL view layer "select encode(image, 'base64') as img, geom , someother colums from table whereyouhaveimage". Enable WFS, use examples from see docs.geoserver.org/latest/en/user/tutorials/GetFeatureInfo/… end of page. Apr 4, 2013 at 12:45
  • Hi again Simplexio sorry to bother you again,I have already seen that example actually(geoserver photo template one) but could not be able to figure out that how to get image from table in database since in that example link is used directly to get image.So if you can tell me how to write in template to get image from table.Sorry for asking silly questions. Thanks in advance.
    – Adarsh
    Apr 5, 2013 at 5:21

You can't store the image by file type. You have to read the image into memory and then convert it to the postgreSQL-Format. The input format can either be hexadecimal or escaped, but since version 9.0 postgreSQL has changed the output format from escape to hex. http://www.postgresql.org/docs/9.0/static/datatype-binary.html

Hope, this will help you.


You can store images in postgresql using the following function :

create or replace function bytea_import(p_path text, p_result out bytea)
               language plpgsql as $$
  l_oid oid;
  r record;
  p_result := '';
  select lo_import(p_path) into l_oid;
  for r in ( select data
             from pg_largeobject
             where loid = l_oid 
             order by pageno ) loop
    p_result = p_result || r.data;
  end loop;
  perform lo_unlink(l_oid);

Then, do the insert of update as examples below :

insert into my_table(bytea_data) select bytea_import('/my/file.name');

update "Employees" set "Photo" = ( select bytea_import('/usr/local/pgsql/backups/nwindpics/emp/1.jpg')) where "EmployeeID"=1;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.