[postgis-users] images in postgresql

Tue Mar 8 03:32:40 PST 2011

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-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at 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 <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 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/51d72cd7/attachment.html>