<div dir="ltr"><div><div><div>Hey,<br></div>if perfo is important, <br></div>you may want to use the version with many points at the same time :<br><a href="http://postgis.net/docs/RT_ST_SetValues.html">http://postgis.net/docs/RT_ST_SetValues.html</a>.<br>
<br></div>Cheers,<br>Rémi-C<br></div><div class="gmail_extra"><br><br><div class="gmail_quote">2014-05-15 1:05 GMT+02:00 Jason Mathis <span dir="ltr"><<a href="mailto:jmathis@redzonesoftware.com" target="_blank">jmathis@redzonesoftware.com</a>></span>:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div style="word-wrap:break-word"><div style="font-family:Helvetica,Arial;font-size:13px;color:rgba(0,0,0,1.0);margin:0px;line-height:auto">
So I figured this out. It was my lack of understanding the functions, projections, and coordinates. Seems all the data is correct but my queries were not. My final query that works is:</div><div style="font-family:Helvetica,Arial;font-size:13px;color:rgba(0,0,0,1.0);margin:0px;line-height:auto">
<br></div><div style="margin:0px"> SELECT ST_Value(rast,(ST_Transform(ST_SetSRID(ST_MakePoint(-111.574508,35.296170),4326),5070)))</div><div style="margin:0px"> FROM tbl_raster </div><div style="font-family:Helvetica,Arial;font-size:13px;color:rgba(0,0,0,1.0);margin:0px;line-height:auto">
WHERE st_intersects(rast, (ST_Transform(ST_SetSRID(ST_MakePoint(-111.574508,35.296170),4326),5070))) </div> <div><div style="font-family:helvetica,arial;font-size:13px"><br></div><div style="font-family:helvetica,arial;font-size:13px">
<br></div><div style="font-family:helvetica,arial;font-size:13px">I needed to first set the srid in 4326 AND then transform to 5070. Pretty simple solution! </div><div style="font-family:helvetica,arial;font-size:13px"><br>
</div><div style="font-family:helvetica,arial;font-size:13px">Thanks,</div><div style="font-family:helvetica,arial;font-size:13px">jason </div><div style="font-family:helvetica,arial;font-size:13px"><br></div><div style="font-family:helvetica,arial;font-size:13px">
<br></div></div><div><div class="h5"> <br><p style="color:#000">On May 14, 2014 at 1:46:04 PM, Jason Mathis (<a href="mailto:jmathis@redzonesoftware.com" target="_blank">jmathis@redzonesoftware.com</a>) wrote:</p> <blockquote type="cite">
<span><div style="word-wrap:break-word"><div></div><div>
<div style="font-family:Helvetica,Arial;font-size:13px;color:rgba(0,0,0,1.0);margin:0px;line-height:auto">
Hi Guido,</div>
<div style="font-family:Helvetica,Arial;font-size:13px;color:rgba(0,0,0,1.0);margin:0px;line-height:auto">
<br></div>
<div style="font-family:Helvetica,Arial;font-size:13px;color:rgba(0,0,0,1.0);margin:0px;line-height:auto">
The example I gave in my question is just the start of how we want
to analyze the data. Giving a point and getting back the value I
thought would be fairly easy to do, so I started there. I believe
some other calculations maybe more complicated and better suited to
raster. One example, given a line we would need to calculate all
the values along that line to a given point. I can’t go into too
much detail because I fairly new to this world, and are being told
to do things from much smarter people. </div>
<div style="font-family:Helvetica,Arial;font-size:13px;color:rgba(0,0,0,1.0);margin:0px;line-height:auto">
<br></div>
<div style="font-family:Helvetica,Arial;font-size:13px;color:rgba(0,0,0,1.0);margin:0px;line-height:auto">
Also this data was originally raster data. Its actually LandFire
data if you are familiar. We partnered with another company who
vectorized the data probably for similar reasons you stated. They
also reprojected the data before giving it to us. Sooooo hence the
process of reproject and rasterize back to the original
formats. </div>
<div style="font-family:Helvetica,Arial;font-size:13px;color:rgba(0,0,0,1.0);margin:0px;line-height:auto">
<br></div>
<div style="font-family:Helvetica,Arial;font-size:13px;color:rgba(0,0,0,1.0);margin:0px;line-height:auto">
Hope that answers your question. We got about 11GB of shape files
after the rasterize and loading it was only a little over 1GB of
data in the db; so space was not too much of a concern. </div>
<div style="font-family:Helvetica,Arial;font-size:13px;color:rgba(0,0,0,1.0);margin:0px;line-height:auto">
<br></div>
<div style="font-family:Helvetica,Arial;font-size:13px;color:rgba(0,0,0,1.0);margin:0px;line-height:auto">
<br></div>
<div style="font-family:Helvetica,Arial;font-size:13px;color:rgba(0,0,0,1.0);margin:0px;line-height:auto">
thanks for all the help hopefully I can figure this
out:) </div>
<div style="font-family:Helvetica,Arial;font-size:13px;color:rgba(0,0,0,1.0);margin:0px;line-height:auto">
<br></div>
<div style="font-family:Helvetica,Arial;font-size:13px;color:rgba(0,0,0,1.0);margin:0px;line-height:auto">
-jason </div>
<div style="font-family:Helvetica,Arial;font-size:13px;color:rgba(0,0,0,1.0);margin:0px;line-height:auto">
<br></div>
<div style="font-family:Helvetica,Arial;font-size:13px;color:rgba(0,0,0,1.0);margin:0px;line-height:auto">
</div>
<div>
<div style="font-family:helvetica,arial;font-size:13px">
<br></div>
</div>
<br>
<p style="color:#000">On May 14, 2014 at 12:29:46 AM, Guido
Lemoine (<a href="mailto:guido.lemoine@jrc.ec.europa.eu" target="_blank">guido.lemoine@jrc.ec.europa.eu</a>)
wrote:</p>
<blockquote type="cite">
<div link="blue" vlink="purple" lang="EN-GB">
<div><span></span>
<div>
<p class="MsoNormal"><span><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d">
Why rasterize polygons at all in this case, if you can run much
simpler queries directly on the polygons?</span></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d">
Rasterizing polygons is actually a waste of disk space. All
rasterized pixels will supposedly have the same (single?) attribute
as the polygon itself.</span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d">
Try using shp2pgsql first to check if you can directly work on the
polygons. The attribute(s) will appear as (a) separate column(s),
so you can include them in your predicates</span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d">
(e.g. where desc = “Very High”, etc.). Reprojection works on
polygons as well, of course.</span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d">
</span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d">
GL</span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d">
</span></p>
<div>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d">
Guido Lemoine</span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d">
European Commission, Joint Research Centre</span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d">
Institute for Environment and Sustainability, Monitoring
Agricultural Resources Unit</span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d">
TP 266 – 21027 Ispra, Italy, T. <a href="tel:%2B39%200332%20786239" value="+390332786239" target="_blank">+39 0332 786239</a>, F. +39 0332
785162</span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d">
</span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d">
E-mail: <a href="mailto:guido.lemoine@jrc.ec.europa.eu" target="_blank"><span style="color:blue">guido.lemoine@jrc.ec.europa.eu</span></a>
URL: <a href="http://ec.europa.eu/jrc" target="_blank"><span style="color:blue">http://ec.europa.eu/jrc</span></a></span></p>
</div>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d">
</span></p>
<div>
<div style="border:none;border-top:solid #b5c4df 1.0pt;padding:3.0pt 0cm 0cm 0cm">
<p class="MsoNormal"><b><span style="font-size:10.0pt;font-family:"Tahoma","sans-serif"" lang="EN-US">From:</span></b> <span style="font-size:10.0pt;font-family:"Tahoma","sans-serif"" lang="EN-US"><a href="mailto:postgis-users-bounces@lists.osgeo.org" target="_blank">postgis-users-bounces@lists.osgeo.org</a>
[mailto:<a href="mailto:postgis-users-bounces@lists.osgeo.org" target="_blank">postgis-users-bounces@lists.osgeo.org</a>] <b>On Behalf Of</b>
Jason Mathis<br>
<b>Sent:</b> 14 May 2014 00:30<br>
<b>To:</b> PostGIS Users Discussion; Pierre Racine<br>
<b>Subject:</b> Re: [postgis-users] getting raster values for point
from st_value</span></p>
</div>
</div>
<p class="MsoNormal"> </p>
<div>
<p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Helvetica","sans-serif"">
Hi Pierre,</span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Helvetica","sans-serif"">
</span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Helvetica","sans-serif"">
Thanks so much for super quick response. You will have to forgive
me I am fairly new to postgis. </span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Helvetica","sans-serif"">
</span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Helvetica","sans-serif"">
Creating the table is no problem but how would I check if it
overlaps with my points? </span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Helvetica","sans-serif"">
</span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Helvetica","sans-serif"">
thanks!</span></p>
</div>
<div>
<div>
<p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Helvetica","sans-serif"">
</span></p>
</div>
</div>
<p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Helvetica","sans-serif"">
</span></p>
<p><span style="font-size:10.0pt;font-family:"Helvetica","sans-serif";color:black">
On May 13, 2014 at 4:08:40 PM, Pierre Racine (<a href="mailto:pierre.racine@sbf.ulaval.ca" target="_blank">pierre.racine@sbf.ulaval.ca</a>)
wrote:</span></p>
<blockquote style="margin-top:5.0pt;margin-bottom:5.0pt">
<div>
<div>
<p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Helvetica","sans-serif"">
Create a new table with the extents of the raster tiles and check
if it overlaps with your points:<br>
<br>
CREATE TABLE rastextets AS<br>
SELECT rast::geometry<br>
FROM tbl_raster<br>
<br>
Pierre<br>
<br>
> -----Original Message-----<br>
> From: <a href="mailto:postgis-users-bounces@lists.osgeo.org" target="_blank">postgis-users-bounces@lists.osgeo.org</a>
[mailto:<a href="mailto:postgis-users-" target="_blank">postgis-users-</a><br>
> <a href="mailto:bounces@lists.osgeo.org" target="_blank">bounces@lists.osgeo.org</a>] On
Behalf Of Jason Mathis<br>
> Sent: Tuesday, May 13, 2014 5:56 PM<br>
> To: <a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
> Subject: [postgis-users] getting raster values for point from
st_value<br>
><br>
> Hi all,<br>
><br>
> I have recently loaded a good amount of rasterized shape files
into a db. I<br>
> mainly used gdal_rasterize and raster2pgsql. The shape files
were in NAD83<br>
> so I used the below commands to rasterized and load.<br>
><br>
> gdal_rasterize -at -a desc -where "desc='Very High'" -burn 2
-tr 30 30 -ot<br>
> byte -a_nodata -99 shape_file.shp raster.tiff<br>
> raster2pgsql -d -s 5070 -t 100x100 -F -I -C -Y raster.tiff
tbl_raster | psql -d<br>
> raster_db<br>
><br>
> I had two sets of shape files one set had about 10 burn values
and another<br>
> had about 50. The issue is getting these values out of the
raster. I believe I<br>
> should be using something from this page:<br>
> <a href="http://postgis.net/docs/RT_ST_Value.html" target="_blank">http://postgis.net/docs/RT_ST_Value.html</a>.<br>
><br>
> I can visualize this file in QGIS and it looks good, the
histogram shows<br>
> values. I can also use the postgis functions, st_summarystats
or<br>
> st_histogram to see some data but I need to be able to query a
given point<br>
> and return the raster value.<br>
><br>
> I have this:<br>
><br>
> SELECT ST_Value(rast,(ST_Point( -111.750185,
34.886948)))<br>
> FROM tbl_raster<br>
> WHERE st_intersects(rast, (ST_Point(-111.750185,
34.886948)))<br>
><br>
> Then i thought oh wait should I set the srid?:<br>
><br>
> SELECT ST_Value(rast,(ST_SetSRID(ST_Point( -111.750185,<br>
> 34.886948),5070)))<br>
> FROM tbl_raster<br>
> WHERE st_intersects(rast,
(ST_SetSRID(ST_Point(-111.750185,<br>
> 34.886948),5070)))<br>
><br>
> Still I get nothing, then I thought I need to transform so I
got this:<br>
><br>
> SELECT ST_Value(rast,(ST_Transform(ST_SetSRID(ST_Point(
-111.762866,<br>
> 34.874309),5070),4326)))<br>
> FROM tbl_raster<br>
> WHERE st_intersects(rast, (ST_Transform(ST_SetSRID(ST_Point(
-<br>
> 111.762866, 34.874309),5070),4326)))<br>
><br>
> But in the end nothing.<br>
><br>
> I did need to add the SRID to the spatial_ref_sys table:<br>
><br>
> INSERT into spatial_ref_sys (srid, auth_name, auth_srid,
proj4text, srtext)<br>
> values ( 5070, 'EPSG', 5070, '+proj=aea +lat_1=29.5
+lat_2=45.5 +lat_0=23<br>
> +lon_0=-96 +x_0=0 +y_0=0 +ellps=GRS80
+towgs84=0,0,0,0,0,0,0<br>
> +units=m +no_defs ', 'PROJCS["NAD83 / Conus<br>
>
Albers",GEOGCS["NAD83",DATUM["North_American_Datum_1983",SPHER<br>
> OID["GRS<br>
>
1980",6378137,298.257222101,AUTHORITY["EPSG","7019"]],TOWGS84[0,<br>
>
0,0,0,0,0,0],AUTHORITY["EPSG","6269"]],PRIMEM["Greenwich",0,AUTHORIT<br>
>
Y["EPSG","8901"]],UNIT["degree",0.0174532925199433,AUTHORITY["EPSG<br>
>
","9122"]],AUTHORITY["EPSG","4269"]],PROJECTION["Albers_Conic_Equal_<br>
>
Area"],PARAMETER["standard_parallel_1",29.5],PARAMETER["standard_par<br>
>
allel_2",45.5],PARAMETER["latitude_of_center",23],PARAMETER["longitude<br>
> _of_center",-<br>
>
96],PARAMETER["false_easting",0],PARAMETER["false_northing",0],UNIT["<br>
>
metre",1,AUTHORITY["EPSG","9001"]],AXIS["X",EAST],AXIS["Y",NORTH],AUT<br>
> HORITY["EPSG","5070"]]’);<br>
><br>
> But ultimately I have nothing I can’t seem to get any data out
using a query<br>
> with a point value. I am thinking it has to do with the srid?
Since I can<br>
> visually inspect it and see data.<br>
><br>
> Anyone have any hot ideas as to why I am not seeing
data?<br>
><br>
> Thanks!<br>
><br>
><br>
><br>
> This transmission contains confidential and privileged
information intended<br>
> solely for the party identified above. If you receive this
message in error,<br>
> you must not use it or convey it to others. Please destroy it
immediately<br>
> and contact the sender at (303) 386-3955 or by return e-mail
to the sender.<br>
<br>
_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a></span></p>
</div>
</div>
</blockquote>
<p class="MsoNormal"> </p>
<p><span style="font-size:10.0pt;font-family:"Arial","sans-serif"">
This transmission contains confidential and privileged information
intended solely for the party identified above. If you receive this
message in error, you must not use it or convey it to others.
Please destroy it immediately and contact the sender at (303)
386-3955 or by return e-mail to the sender.</span></p>
</div>
_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a></div>
</div>
</blockquote>
</div></div></span></blockquote></div></div></div><div class="HOEnZb"><div class="h5">
<br>
<p><font face="Arial">This transmission contains confidential and privileged
information intended solely for the party identified above. If you receive this message in error, you must not use
it or convey it to others. Please destroy it immediately and contact the sender at
(303) 386-3955 or by return e-mail to the sender.</font></p>
</div></div><br>_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br></blockquote></div><br></div>