<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META content="text/html; charset=iso-8859-1" http-equiv=Content-Type>
<STYLE type=text/css>DIV {
MARGIN: 0px
}
</STYLE>
<META name=GENERATOR content="MSHTML 8.00.7601.17537"></HEAD>
<BODY>
<DIV dir=ltr align=left><SPAN class=208011417-05032011><FONT color=#0000ff
size=2 face=Arial>Robert,</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=208011417-05032011><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV 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 dir=ltr align=left><SPAN class=208011417-05032011><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV 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 dir=ltr align=left><SPAN class=208011417-05032011><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV 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 dir=ltr align=left><SPAN class=208011417-05032011><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV 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 dir=ltr align=left><SPAN class=208011417-05032011><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV 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 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 dir=ltr align=left><SPAN class=208011417-05032011><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV 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 dir=ltr align=left><SPAN class=208011417-05032011><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=208011417-05032011><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=208011417-05032011><FONT color=#0000ff
size=2 face=Arial>
<DIV 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 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
ST_DWithin(wt.geom, pt.geom, 10)</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=208011417-05032011></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=208011417-05032011></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=208011417-05032011>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.</SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=208011417-05032011></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=208011417-05032011>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.</SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=208011417-05032011><A
href="http://www.postgis.us/chapter_01">http://www.postgis.us/chapter_01</A></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=208011417-05032011></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=208011417-05032011>Leo</SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=208011417-05032011><A
href="http://www.postgis.us">http://www.postgis.us</A></SPAN></DIV>
<DIV dir=ltr align=left><SPAN
class=208011417-05032011></SPAN></FONT></SPAN> </DIV></DIV>
<DIV dir=ltr align=left><SPAN class=208011417-05032011><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>Robert Buckley<BR><B>Sent:</B> Saturday, March 05, 2011 5:39
AM<BR><B>To:</B> postgis-users@postgis.refractions.net<BR><B>Subject:</B>
[postgis-users] images in postgresql<BR></FONT><BR></DIV>
<DIV></DIV>
<DIV
style="FONT-FAMILY: times new roman,new york,times,serif; COLOR: #40007f; 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><BR></DIV></DIV><BR></BODY></HTML>