[postgis-users] images in postgresql

Paragon Corporation lr at pcorp.us
Sat Mar 5 09:21:49 PST 2011

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.


From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Robert
Sent: Saturday, March 05, 2011 5:39 AM
To: postgis-users at postgis.refractions.net
Subject: [postgis-users] images in postgresql


I am just experimenting at the moment with a project and could do with some

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,


-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20110305/35eb460d/attachment.html>

More information about the postgis-users mailing list