Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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 of 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...Thanks in advance.

Best Regards, ADK

share|improve this question
add comment

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 http://stackoverflow.com/questions/54500/storing-images-in-postgresql

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

share|improve this answer
    
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. –  Akash Apr 4 '13 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. –  simplexio Apr 4 '13 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. –  Akash Apr 4 '13 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. –  simplexio Apr 4 '13 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. –  Akash Apr 5 '13 at 5:21
show 2 more comments

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.

share|improve this answer
add comment

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 $$
declare
  l_oid oid;
  r record;
begin
  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);
end;$$;

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;
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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