<html><head><style type="text/css"><!-- DIV {margin:0px;} --></style></head><body><div style="font-family:times new roman,new york,times,serif;font-size:10pt">thanks for the tip,<br><br>your book looks great...ill have to get it.<br><br>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><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><br></div><div style="font-family: times new roman,new york,times,serif; font-size: 10pt;"><br><div style="font-family: times new roman,new york,times,serif; font-size: 12pt;"><font face="Tahoma" size="2"><hr size="1"><b><span style="font-weight: bold;">Von:</span></b> Paragon Corporation
<lr@pcorp.us><br><b><span style="font-weight: bold;">An:</span></b> PostGIS Users Discussion <postgis-users@postgis.refractions.net><br><b><span style="font-weight: bold;">Gesendet:</span></b> Samstag, den 5. März 2011, 18:21:49 Uhr<br><b><span style="font-weight: bold;">Betreff:</span></b> Re: [postgis-users] images in postgresql<br></font><br>
<style type="text/css">DIV {
MARGIN:0px;}
</style>
<div dir="ltr" align="left"><span class="208011417-05032011"><font color="#0000ff" face="Arial" size="2">Robert,</font></span></div>
<div dir="ltr" align="left"><span class="208011417-05032011"><font color="#0000ff" face="Arial" size="2"></font></span> </div>
<div dir="ltr" align="left"><span class="208011417-05032011"><font color="#0000ff" face="Arial" size="2">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" face="Arial" size="2"></font></span> </div>
<div dir="ltr" align="left"><span class="208011417-05032011"><font color="#0000ff" face="Arial" size="2"> 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" face="Arial" size="2"></font></span> </div>
<div dir="ltr" align="left"><span class="208011417-05032011"><font color="#0000ff" face="Arial" size="2">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" face="Arial" size="2"></font></span> </div>
<div dir="ltr" align="left"><span class="208011417-05032011"><font color="#0000ff" face="Arial" size="2">So it would be of the form</font></span></div>
<div dir="ltr" align="left"><span class="208011417-05032011"><font color="#0000ff" face="Arial" size="2"></font></span> </div>
<div dir="ltr" align="left"><span class="208011417-05032011"><font color="#0000ff" face="Arial" size="2">SELECT wt.wt_id, wt.geom, p.picture</font></span></div>
<div dir="ltr" align="left"><span class="208011417-05032011"><font color="#0000ff" face="Arial" size="2">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" face="Arial" size="2"></font></span> </div>
<div dir="ltr" align="left"><span class="208011417-05032011"><font color="#0000ff" face="Arial" size="2">or if they are spatially related by space</font></span></div>
<div dir="ltr" align="left"><span class="208011417-05032011"><font color="#0000ff" face="Arial" size="2"></font></span> </div>
<div dir="ltr" align="left"><span class="208011417-05032011"><font color="#0000ff" face="Arial" size="2"></font></span> </div>
<div dir="ltr" align="left"><span class="208011417-05032011"><font color="#0000ff" face="Arial" size="2">
</font><div dir="ltr" align="left"><span class="208011417-05032011"><font color="#0000ff" face="Arial" size="2"><font color="#0000ff" face="Arial" size="2">SELECT wt.wt_id, wt.geom, p.picture</font></font></span></div>
<div dir="ltr" align="left"><span class="208011417-05032011"><font color="#0000ff" face="Arial" size="2"><font color="#0000ff" face="Arial" size="2">FROM windturbines As wt INNER JOIN pictures As p ON
ST_DWithin(wt.geom, pt.geom, 10)</font></font></span></div>
<div dir="ltr" align="left"><span class="208011417-05032011"></span><font color="#0000ff" face="Arial" size="2"> </font></div>
<div dir="ltr" align="left"><span class="208011417-05032011"></span><font color="#0000ff" face="Arial" size="2"> </font></div>
<div dir="ltr" align="left"><span class="208011417-05032011"><font color="#0000ff" face="Arial" size="2">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 dir="ltr" align="left"><span class="208011417-05032011"></span><font color="#0000ff" face="Arial" size="2"> </font></div>
<div dir="ltr" align="left"><span class="208011417-05032011"><font color="#0000ff" face="Arial" size="2">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 dir="ltr" align="left"><span class="208011417-05032011"><font color="#0000ff" face="Arial" size="2"><a rel="nofollow" target="_blank" href="http://www.postgis.us/chapter_01">http://www.postgis.us/chapter_01</a></font></span></div>
<div dir="ltr" align="left"><span class="208011417-05032011"></span><font color="#0000ff" face="Arial" size="2"> </font></div>
<div dir="ltr" align="left"><span class="208011417-05032011"><font color="#0000ff" face="Arial" size="2">Leo</font></span></div>
<div dir="ltr" align="left"><span class="208011417-05032011"><font color="#0000ff" face="Arial" size="2"><a rel="nofollow" target="_blank" href="http://www.postgis.us">http://www.postgis.us</a></font></span></div>
<div dir="ltr" align="left"><span class="208011417-05032011"></span><font color="#0000ff" face="Arial" size="2"></font> </div></span></div>
<div dir="ltr" align="left"><span class="208011417-05032011"><font color="#0000ff" face="Arial" size="2"></font></span> </div><br>
<div dir="ltr" class="OutlookMessageHeader" align="left" lang="en-us">
<hr tabindex="-1">
<font face="Tahoma" size="2"><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: 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><br></div></div><br></div></div>
</div><br></body></html>