Why put the images in the database at all? If you leave them on disk and put a relative path into postgres, you can host them anywhere using apache and the client can fetch them via url.
charles On Mar 8, 2011, at 6:32 AM, Paragon Corporation wrote: > 300 is nothing. For simplicity especially if it is a 1 to 1 relationship, I > would keep it in a single table. > > If you do plan to have multiple pictures per turbine like different side > views, then you would keep the pictures in a separate table. > > What Leo was talking about when he said painful was if you are updating > millions of records at once. One record here and there or even 500 records > at once is not that big of a deal. All that would be completed in a flip of > an eyelid. > > I would also use bytea type for picture storage than OID (LOID). I think the > non-bytea way is considered deprecated these days and suffers from the > problem that you have to delete the data separately from the record otherwise > you get orphaned objects. I think the bytea way is also easier for most > applications to read. > > The Oversized-Attribute Storage Technique (TOAST) is a side topic -- really a > behind the scenes implementation detail that PostgreSQL uses to get over its > 8kb page size limit. Has nothing to do with data type choice. You don't > usually need to think about it much whether your data is using TOAST or not > except when really bad things happen like one of the toast tables becomes > corrupt. (which usually signals disk failure anyway). > > Hope that helps, > Regina > http://www.postgis.us > > > From: postgis-users-boun...@postgis.refractions.net > [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Robert > Buckley > Sent: Tuesday, March 08, 2011 3:20 AM > To: PostGIS Users Discussion > Subject: Re: [postgis-users] images in postgresql > > Thanks for all the replies. > > My Wind turbine table will only have around 300 - maximum 500 points. The > photos shouldn´t change until either a turbine vanishes or gets repowered > (ie. upgraded). > > I am pretty new to postgis so when you all start talking about TOAST I start > thinking about food rather than data formats. TOAST, Large blob, OID...how do > I decide? As the images will be (hopefully) displayed over the web and they > shouldn´t be more than 50 kb each. They are simply there to display a nice > picture when someonw clicks on the map. > > The table will however be updated with new turbines and we will certainly > find errors so that some will have to be deleted or moved. > > Would it be easier to keep them all in one table rather than in separate > tables? > > yours, > > Rob > > > Von: Paragon Corporation <l...@pcorp.us> > An: PostGIS Users Discussion <postgis-users@postgis.refractions.net> > Gesendet: Dienstag, den 8. März 2011, 6:00:00 Uhr > Betreff: Re: [postgis-users] images in postgresql > > Ben, > > My understanding is the same (as long as you don't select the column that is) > otherwise has to be detoasted. As I recall, I think a small bit will be > stored and then the rest that doesn't fit into (I can't recall maximum > space), gets chunked into toast records. > > Its true for most of the databases I've worked with - e.g. large text or > blobs just the pointer is stored in the main table, except PostgreSQL makes > this decision conditionally on size and other databases make it beforehand > based on data type. > > However -- UPDATES will be painful I think because even though the data is > toasted, PostgreSQL will still create an MVCC copy of the whole record when > doing updates and slushing around big pictures and geometries can be painful. > So if your other wind turbine info gets changed more often than the photos, > I would keep them separate. > > > Leo > http://www.postgis.us > > > From: postgis-users-boun...@postgis.refractions.net > [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Ben Madin > Sent: Monday, March 07, 2011 8:31 PM > To: PostGIS Users Discussion > Subject: Re: [postgis-users] images in postgresql > > Robert, > > On 06/03/2011, at 4:28 PM, Robert Buckley wrote: > >> The windturbine table exists in EPSG:4326. I made a seperate table for the >> images because I didn´t wan´t to blow the size of the wind turbine table out >> of proportion and jeopardize performance. > > My understanding - and if I'm wrong I need to know(!) - is that the sort of > data you are talking about (large geometries or blobs - for your pictures) > are not stored in the primary table, but in associated storage space, known > as TOAST tables. > > This has important implications for indexing, but is brilliant because the > content of these data fields does not directly impact on the number of pages > that the table takes, hence rapid searching is still possible. > > cheers > > Ben > > > > >> >> I am making a simple application to show wind turbines as wms and I wanted >> to show the turbine in a popup. I´m not sure how to get the popup to display >> though. >> >> Any examples? >> Thanks, >> >> Rob >> >> >> Von: Paragon Corporation <l...@pcorp.us> >> An: PostGIS Users Discussion <postgis-users@postgis.refractions.net> >> Gesendet: Samstag, den 5. März 2011, 18:21:49 Uhr >> Betreff: Re: [postgis-users] images in postgresql >> >> Robert, >> >> Is there a reason why you have the points in a separate table or do you have >> points in both tables and you want to relate by a spatial join? >> >> If its a 1 to 1 relationship, we would just put them in the same table. >> >> As far as foreign keys go, you should have some identifier the same in the >> two tables. Do you? >> >> So it would be of the form >> >> SELECT wt.wt_id, wt.geom, p.picture >> FROM windturbines As wt INNER JOIN pictures As p ON wt.wt_id = p.wt_id >> >> or if they are spatially related by space >> >> >> SELECT wt.wt_id, wt.geom, p.picture >> FROM windturbines As wt INNER JOIN pictures As p ON ST_DWithin(wt.geom, >> pt.geom, 10) >> >> >> The 10 depends on the spatial reference system or if you are using geography >> type then it means 10 meters. So I'm treating the wind turbine location and >> picture location as the same if they are within 10 meters apart. >> >> BTW: you might want to read the first chapter of our upcoming book. It's a >> free download and answers this type of question with concrete examples. >> http://www.postgis.us/chapter_01 >> >> Leo >> http://www.postgis.us >> >> >> >> From: postgis-users-boun...@postgis.refractions.net >> [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Robert >> Buckley >> Sent: Saturday, March 05, 2011 5:39 AM >> To: postgis-users@postgis.refractions.net >> Subject: [postgis-users] images in postgresql >> >> Hi, >> >> I am just experimenting at the moment with a project and could do with some >> advice. >> >> I have created a database which contains photos of Windturbines. I also >> have a postgis database with the locations (points) of the wind turbines and >> would like join the photos to the points via a link table or foreign key. >> >> As you can tell, I haven´t too much experience with postgresql and >> relational database design. But i can imagine that the task should not be >> too difficult. >> >> I am just a bit unsure how to go about it. The photos are on the linux >> server and the creation of the table and the insert of the image was >> successfull. But how do i get the join and how would I display this photo >> in a geoext project? >> >> thanks for any tips, >> >> Robert
_______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
