[postgis-users] images in postgresql

[Ben Madin]

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 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/1bd389b9/attachment.html>