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 n 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 >
_______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users