<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META content="text/html; charset=iso-8859-1" http-equiv=Content-Type><BASE
href="x-msg://25/">
<META name=GENERATOR content="MSHTML 8.00.7601.17537"></HEAD>
<BODY
style="WORD-WRAP: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space">
<DIV dir=ltr align=left><SPAN class=953075204-08032011><FONT color=#0000ff
size=2 face=Arial>Ben,</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=953075204-08032011><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=953075204-08032011><FONT color=#0000ff
size=2 face=Arial>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.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=953075204-08032011><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=953075204-08032011><FONT color=#0000ff
size=2 face=Arial>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.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=953075204-08032011><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=953075204-08032011><FONT color=#0000ff
size=2 face=Arial>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.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=953075204-08032011><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=953075204-08032011><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=953075204-08032011><FONT color=#0000ff
size=2 face=Arial>Leo</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=953075204-08032011><FONT color=#0000ff
size=2 face=Arial><A
href="http://www.postgis.us">http://www.postgis.us</A></FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=953075204-08032011><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV><BR>
<DIV dir=ltr lang=en-us class=OutlookMessageHeader align=left>
<HR tabIndex=-1>
<FONT size=2 face=Tahoma><B>From:</B>
postgis-users-bounces@postgis.refractions.net
[mailto:postgis-users-bounces@postgis.refractions.net] <B>On Behalf Of </B>Ben
Madin<BR><B>Sent:</B> Monday, March 07, 2011 8:31 PM<BR><B>To:</B> PostGIS Users
Discussion<BR><B>Subject:</B> Re: [postgis-users] images in
postgresql<BR></FONT><BR></DIV>
<DIV></DIV>Robert,
<DIV><BR>
<DIV>
<DIV>On 06/03/2011, at 4:28 PM, Robert Buckley wrote:</DIV><BR
class=Apple-interchange-newline>
<BLOCKQUOTE type="cite"><SPAN
style="WIDOWS: 2; TEXT-TRANSFORM: none; TEXT-INDENT: 0px; BORDER-COLLAPSE: separate; FONT: medium Helvetica; WHITE-SPACE: normal; ORPHANS: 2; LETTER-SPACING: normal; WORD-SPACING: 0px; -webkit-border-horizontal-spacing: 0px; -webkit-border-vertical-spacing: 0px; -webkit-text-decorations-in-effect: none; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px"
class=Apple-style-span>
<DIV>
<DIV
style="MARGIN: 0px; FONT-FAMILY: 'times new roman', 'new york', times, serif; FONT-SIZE: 10pt">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.<BR></DIV></DIV></SPAN></BLOCKQUOTE>
<DIV><BR></DIV>
<DIV>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. </DIV>
<DIV><BR></DIV>
<DIV>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.</DIV>
<DIV><BR></DIV>
<DIV>cheers</DIV>
<DIV><BR></DIV>
<DIV>Ben</DIV>
<DIV><BR></DIV>
<DIV><BR></DIV>
<DIV><BR></DIV><BR>
<BLOCKQUOTE type="cite"><SPAN
style="WIDOWS: 2; TEXT-TRANSFORM: none; TEXT-INDENT: 0px; BORDER-COLLAPSE: separate; FONT: medium Helvetica; WHITE-SPACE: normal; ORPHANS: 2; LETTER-SPACING: normal; WORD-SPACING: 0px; -webkit-border-horizontal-spacing: 0px; -webkit-border-vertical-spacing: 0px; -webkit-text-decorations-in-effect: none; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px"
class=Apple-style-span>
<DIV>
<DIV
style="MARGIN: 0px; FONT-FAMILY: 'times new roman', 'new york', times, serif; FONT-SIZE: 10pt"><BR>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.<BR><BR>Any examples?<BR>Thanks,<BR><BR>Rob<BR>
<DIV style="MARGIN: 0px"><BR></DIV>
<DIV
style="MARGIN: 0px; FONT-FAMILY: 'times new roman', 'new york', times, serif; FONT-SIZE: 10pt"><BR>
<DIV
style="MARGIN: 0px; FONT-FAMILY: 'times new roman', 'new york', times, serif; FONT-SIZE: 12pt"><FONT
size=2 face=Tahoma>
<HR SIZE=1>
<B><SPAN style="FONT-WEIGHT: bold">Von:</SPAN></B><SPAN
class=Apple-converted-space> </SPAN>Paragon Corporation <<A
href="mailto:lr@pcorp.us">lr@pcorp.us</A>><BR><B><SPAN
style="FONT-WEIGHT: bold">An:</SPAN></B><SPAN
class=Apple-converted-space> </SPAN>PostGIS Users Discussion <<A
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A>><BR><B><SPAN
style="FONT-WEIGHT: bold">Gesendet:</SPAN></B><SPAN
class=Apple-converted-space> </SPAN>Samstag, den 5. März 2011, 18:21:49
Uhr<BR><B><SPAN style="FONT-WEIGHT: bold">Betreff:</SPAN></B><SPAN
class=Apple-converted-space> </SPAN>Re: [postgis-users] images in
postgresql<BR></FONT><BR>
<DIV style="MARGIN: 0px" dir=ltr align=left><SPAN
class=208011417-05032011><FONT color=#0000ff size=2
face=Arial>Robert,</FONT></SPAN></DIV>
<DIV style="MARGIN: 0px" dir=ltr align=left><SPAN
class=208011417-05032011><FONT color=#0000ff size=2
face=Arial></FONT></SPAN> </DIV>
<DIV style="MARGIN: 0px" dir=ltr align=left><SPAN
class=208011417-05032011><FONT color=#0000ff size=2 face=Arial>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?</FONT></SPAN></DIV>
<DIV style="MARGIN: 0px" dir=ltr align=left><SPAN
class=208011417-05032011><FONT color=#0000ff size=2
face=Arial></FONT></SPAN> </DIV>
<DIV style="MARGIN: 0px" dir=ltr align=left><SPAN
class=208011417-05032011><FONT color=#0000ff size=2 face=Arial> If its a
1 to 1 relationship, we would just put them in the same
table.</FONT></SPAN></DIV>
<DIV style="MARGIN: 0px" dir=ltr align=left><SPAN
class=208011417-05032011><FONT color=#0000ff size=2
face=Arial></FONT></SPAN> </DIV>
<DIV style="MARGIN: 0px" dir=ltr align=left><SPAN
class=208011417-05032011><FONT color=#0000ff size=2 face=Arial>As far as
foreign keys go, you should have some identifier the same in the two
tables. Do you? </FONT></SPAN></DIV>
<DIV style="MARGIN: 0px" dir=ltr align=left><SPAN
class=208011417-05032011><FONT color=#0000ff size=2
face=Arial></FONT></SPAN> </DIV>
<DIV style="MARGIN: 0px" dir=ltr align=left><SPAN
class=208011417-05032011><FONT color=#0000ff size=2 face=Arial>So it would be
of the form</FONT></SPAN></DIV>
<DIV style="MARGIN: 0px" dir=ltr align=left><SPAN
class=208011417-05032011><FONT color=#0000ff size=2
face=Arial></FONT></SPAN> </DIV>
<DIV style="MARGIN: 0px" dir=ltr align=left><SPAN
class=208011417-05032011><FONT color=#0000ff size=2 face=Arial>SELECT
wt.wt_id, wt.geom, p.picture</FONT></SPAN></DIV>
<DIV style="MARGIN: 0px" dir=ltr align=left><SPAN
class=208011417-05032011><FONT color=#0000ff size=2 face=Arial>FROM
windturbines As wt INNER JOIN pictures As p ON wt.wt_id =
p.wt_id</FONT></SPAN></DIV>
<DIV style="MARGIN: 0px" dir=ltr align=left><SPAN
class=208011417-05032011><FONT color=#0000ff size=2
face=Arial></FONT></SPAN> </DIV>
<DIV style="MARGIN: 0px" dir=ltr align=left><SPAN
class=208011417-05032011><FONT color=#0000ff size=2 face=Arial>or if they are
spatially related by space</FONT></SPAN></DIV>
<DIV style="MARGIN: 0px" dir=ltr align=left><SPAN
class=208011417-05032011><FONT color=#0000ff size=2
face=Arial></FONT></SPAN> </DIV>
<DIV style="MARGIN: 0px" dir=ltr align=left><SPAN
class=208011417-05032011><FONT color=#0000ff size=2
face=Arial></FONT></SPAN> </DIV>
<DIV style="MARGIN: 0px" dir=ltr align=left><SPAN
class=208011417-05032011><FONT color=#0000ff size=2 face=Arial></FONT>
<DIV style="MARGIN: 0px" dir=ltr align=left><SPAN
class=208011417-05032011><FONT color=#0000ff size=2 face=Arial><FONT
color=#0000ff size=2 face=Arial>SELECT wt.wt_id, wt.geom,
p.picture</FONT></FONT></SPAN></DIV>
<DIV style="MARGIN: 0px" dir=ltr align=left><SPAN
class=208011417-05032011><FONT color=#0000ff size=2 face=Arial><FONT
color=#0000ff size=2 face=Arial>FROM windturbines As wt INNER JOIN pictures As
p ON ST_DWithin(wt.geom, pt.geom, 10)</FONT></FONT></SPAN></DIV>
<DIV style="MARGIN: 0px" dir=ltr align=left><SPAN
class=208011417-05032011></SPAN><FONT color=#0000ff size=2
face=Arial></FONT> </DIV>
<DIV style="MARGIN: 0px" dir=ltr align=left><SPAN
class=208011417-05032011></SPAN><FONT color=#0000ff size=2
face=Arial></FONT> </DIV>
<DIV style="MARGIN: 0px" dir=ltr align=left><SPAN
class=208011417-05032011><FONT color=#0000ff size=2 face=Arial>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.</FONT></SPAN></DIV>
<DIV style="MARGIN: 0px" dir=ltr align=left><SPAN
class=208011417-05032011></SPAN><FONT color=#0000ff size=2
face=Arial></FONT> </DIV>
<DIV style="MARGIN: 0px" dir=ltr align=left><SPAN
class=208011417-05032011><FONT color=#0000ff size=2 face=Arial>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.</FONT></SPAN></DIV>
<DIV style="MARGIN: 0px" dir=ltr align=left><SPAN
class=208011417-05032011><FONT color=#0000ff size=2 face=Arial><A
href="http://www.postgis.us/chapter_01" rel=nofollow
target=_blank>http://www.postgis.us/chapter_01</A></FONT></SPAN></DIV>
<DIV style="MARGIN: 0px" dir=ltr align=left><SPAN
class=208011417-05032011></SPAN><FONT color=#0000ff size=2
face=Arial></FONT> </DIV>
<DIV style="MARGIN: 0px" dir=ltr align=left><SPAN
class=208011417-05032011><FONT color=#0000ff size=2
face=Arial>Leo</FONT></SPAN></DIV>
<DIV style="MARGIN: 0px" dir=ltr align=left><SPAN
class=208011417-05032011><FONT color=#0000ff size=2 face=Arial><A
href="http://www.postgis.us" rel=nofollow
target=_blank>http://www.postgis.us</A></FONT></SPAN></DIV>
<DIV style="MARGIN: 0px" dir=ltr align=left><SPAN
class=208011417-05032011></SPAN><FONT color=#0000ff size=2
face=Arial></FONT> </DIV></SPAN></DIV>
<DIV style="MARGIN: 0px" dir=ltr align=left><SPAN
class=208011417-05032011><FONT color=#0000ff size=2
face=Arial></FONT></SPAN> </DIV><BR>
<DIV style="MARGIN: 0px" dir=ltr lang=en-us class=OutlookMessageHeader
align=left>
<HR tabIndex=-1>
<FONT size=2 face=Tahoma><B>From:</B><SPAN
class=Apple-converted-space> </SPAN><A
href="mailto:postgis-users-bounces@postgis.refractions.net">postgis-users-bounces@postgis.refractions.net</A><SPAN
class=Apple-converted-space> </SPAN>[mailto:postgis-users-bounces@postgis.refractions.net]<SPAN
class=Apple-converted-space> </SPAN><B>On Behalf Of<SPAN
class=Apple-converted-space> </SPAN></B>Robert
Buckley<BR><B>Sent:</B><SPAN
class=Apple-converted-space> </SPAN>Saturday, March 05, 2011 5:39
AM<BR><B>To:</B><SPAN class=Apple-converted-space> </SPAN><A
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A><BR><B>Subject:</B><SPAN
class=Apple-converted-space> </SPAN>[postgis-users] images in
postgresql<BR></FONT><BR></DIV>
<DIV style="MARGIN: 0px"></DIV>
<DIV
style="MARGIN: 0px; FONT-FAMILY: 'times new roman', 'new york', times, serif; COLOR: rgb(64,0,127); FONT-SIZE: 10pt">Hi,<BR><BR>I
am just experimenting at the moment with a project and could do with some
advice.<BR><BR>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.<BR><BR>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.<BR><BR>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?<BR><BR>thanks for any
tips,<BR><BR>Robert<BR><BR>
<DIV
style="MARGIN: 0px"><BR></DIV></DIV><BR></DIV></DIV></DIV><BR>_______________________________________________<BR>postgis-users
mailing list<BR><A
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A><BR><A
href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR></DIV></SPAN></BLOCKQUOTE></DIV><BR></DIV></BODY></HTML>