[postgis-users] images in postgresql

Tue Mar 8 00:19:52 PST 2011

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 <lr at pcorp.us>
An: PostGIS Users Discussion <postgis-users at 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-bounces at postgis.refractions.net  
[mailto:postgis-users-bounces at 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 <lr at pcorp.us>
>An: PostGIS Users Discussion <postgis-users at 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-bounces at postgis.refractions.net [mailto:postgis-users-bounces at postgis.refractions.net] On
n Behalf Of Robert    Buckley
>Sent: Saturday, March 05, 2011 5:39    AM
>To: postgis-users at 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 at postgis.refractions.net
>http://postgis.refractions.net/mailman/listinfo/postgis-users
>

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20110308/b51fd2c6/attachment.html>