Re: [postgis-users] images in postgresql
thanks for the tip, your book looks great...ill have to get it. 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. 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 l...@pcorp.us An: PostGIS Users Discussion postgis-users@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-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Robert Buckley Sent: Saturday, March 05, 2011 5:39 AM To: postgis-users@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@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] images in postgresql
Hi Robert, Can you do turbines as WFS with one of the fields the URL for the image, so the client has the image URL readily available? Or if you stick with WMS, you can still query the feature to get the URL of the image, see how to set up a query layer via your WMS server application. Brent Wood --- On Sun, 3/6/11, Robert Buckley robertdbuck...@yahoo.com wrote: From: Robert Buckley robertdbuck...@yahoo.com Subject: Re: [postgis-users] images in postgresql To: PostGIS Users Discussion postgis-users@postgis.refractions.net Date: Sunday, March 6, 2011, 9:28 PM thanks for the tip, your book looks great...ill have to get it. 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. 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 l...@pcorp.us An: PostGIS Users Discussion postgis-users@postgis.refractions.net Gesendet: Samstag, den 5. März 2011, 18:21:49 Uhr Betreff: Re: [postgis-users] images in postgresql #yiv1693993084 DIV { MARGIN:0px;} 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-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Robert Buckley Sent: Saturday, March 05, 2011 5:39 AM To: postgis-users@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 -Inline Attachment Follows- ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] meters to degrees
Hello everyone, I am new to Postgres and PostGIS. I have encountered one problem, that I can not deal with.I have table with various points that have SRID=3044, which should be UTM zone including Jutland. I have created function to loop through all points in table and check if they are within given radius(using geometry and function st_point_inside_circle). Now to the problem, for some reason st_point_inside_circle takes radius in degrees, which is very inconvenient for me. I would really need to change to metres. If anyone has a suggestion, pls let me know. Thank you for help Michal ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] meters to degrees
Hello everyone, I am new to Postgres and PostGIS. I have encountered one problem, that I can not deal with.I have table with various points that have SRID=3044, which should be UTM zone including Jutland. I have created function to loop through all points in table and check if they are within given radius(using geometry and function st_point_inside_circle). Now to the problem, for some reason st_point_inside_circle takes radius in degrees, which is very inconvenient for me. I would really need to change to metres. If anyone has a suggestion, pls let me know. Thank you for help Michal ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users