<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40"><head><meta http-equiv=Content-Type content="text/html; charset=us-ascii"><meta name=Generator content="Microsoft Word 15 (filtered medium)"><style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri",sans-serif;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:#0563C1;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:#954F72;
text-decoration:underline;}
span.EmailStyle17
{mso-style-type:personal;
font-family:"Calibri",sans-serif;
color:windowtext;}
span.EmailStyle18
{mso-style-type:personal;
font-family:"Calibri",sans-serif;
color:#1F497D;}
span.EmailStyle19
{mso-style-type:personal;
font-family:"Calibri",sans-serif;
color:#1F497D;}
span.EmailStyle20
{mso-style-type:personal;
font-family:"Calibri",sans-serif;
color:windowtext;}
span.EmailStyle21
{mso-style-type:personal;
font-family:"Calibri",sans-serif;
color:#1F497D;}
span.EmailStyle22
{mso-style-type:personal-reply;
font-family:"Calibri",sans-serif;
color:#1F497D;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@page WordSection1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]--></head><body lang=EN-US link="#0563C1" vlink="#954F72"><div class=WordSection1><p class=MsoNormal><span style='color:#1F497D'>Darrel,<o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D'>Even better, replace your version of this function with this patched one, and hopefully that should fix your issue:<o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='color:#1F497D'>CREATE OR REPLACE FUNCTION _raster_constraint_info_scale(rastschema name, rasttable name, rastcolumn name, axis char)<o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D'> RETURNS double precision AS $$<o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D'> WITH c AS (SELECT<o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D'> regexp_replace(<o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D'> replace(<o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D'> split_part(<o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D'> split_part(s.consrc, ' = ', 2),<o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D'> '::', 1<o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D'> ),<o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D'> 'round(', ''<o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D'> ),<o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D'> '[ ''''\(\)]', '', 'g'<o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D'> )::text AS val<o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D'> FROM pg_class c, pg_namespace n, pg_attribute a, pg_constraint s<o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D'> WHERE n.nspname = $1<o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D'> AND c.relname = $2<o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D'> AND a.attname = $3<o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D'> AND a.attrelid = c.oid<o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D'> AND s.connamespace = n.oid<o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D'> AND s.conrelid = c.oid<o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D'> AND a.attnum = ANY (s.conkey)<o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D'> AND s.consrc LIKE '%st_scale' || $4 || '(% = %') <o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D'>-- if it is a comma separated list of two numbers then need to use round<o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D'> SELECT CASE WHEN split_part(c.val,',', 2) > '' <o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D'> THEN round( split_part(c.val, ',',1)::numeric, split_part(c.val,',',2)::integer )::float8 <o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D'> ELSE c.val::float8 END<o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D'> FROM c;<o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D'> $$ LANGUAGE sql STABLE STRICT<o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D'> COST 100;<o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='color:#1F497D'>Hope that helps,<o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D'>Regina<o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p><div><div style='border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0in 0in 0in'><p class=MsoNormal style='margin-left:.5in'><b>From:</b> postgis-users [mailto:postgis-users-bounces@lists.osgeo.org] <b>On Behalf Of </b>Paragon Corporation<br><b>Sent:</b> Saturday, November 07, 2015 4:33 AM<br><b>To:</b> 'PostGIS Users Discussion' <postgis-users@lists.osgeo.org><br><b>Subject:</b> Re: [postgis-users] I am probably being stupid but I cannot get my rasters to display using QGIS<o:p></o:p></p></div></div><p class=MsoNormal style='margin-left:.5in'><o:p> </o:p></p><p class=MsoNormal style='margin-left:.5in'><span style='color:#1F497D'>Darrel,<o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:.5in'><span style='color:#1F497D'>This might be a bug in 2.2.0. I'm investigating. I tried loading up a raster too in QGIS and ran into problems if I include constraints on raster.<o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:.5in'><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:.5in'><span style='color:#1F497D'>Can you run this query:<o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:.5in'><span style='color:#1F497D'>SELECT * FROM raster_columns;<o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:.5in'><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:.5in'><span style='color:#1F497D'>If it gives you an error like " invalid input syntax for type double precision" then you are running into the bug I opened in this ticket:<o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:.5in'><span style='color:#1F497D'><a href="https://trac.osgeo.org/postgis/ticket/3360">https://trac.osgeo.org/postgis/ticket/3360</a><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:.5in'><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:.5in'><span style='color:#1F497D'>As a workaround for now until we have a patch, drop the constraints named:<o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:.5in'><span style='color:#1F497D'>enforce_scalex_rast, enforce_scaley_rast<o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:.5in'><span style='color:#1F497D'>with something like: ALTER TABLE public.prec DROP CONSTRAINT enforce_scalex_rast;<o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:.5in'><span style='color:#1F497D'>If you are using pgAdmin, you can just expand the table and constraints and right-click and drop.<o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:.5in'><span style='color:#1F497D'>Hope that helps,<o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'><span style='color:#1F497D'>Regina<o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'><span style='color:#1F497D'><o:p> </o:p></span></p><div><div style='border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0in 0in 0in'><p class=MsoNormal style='margin-left:1.0in'><b>From:</b> postgis-users [<a href="mailto:postgis-users-bounces@lists.osgeo.org">mailto:postgis-users-bounces@lists.osgeo.org</a>] <b>On Behalf Of </b>Darrel Maddy<br><b>Sent:</b> Friday, November 06, 2015 7:27 PM<br><b>To:</b> PostGIS Users Discussion <<a href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a>><br><b>Subject:</b> Re: [postgis-users] I am probably being stupid but I cannot get my rasters to display using QGIS<o:p></o:p></p></div></div><p class=MsoNormal style='margin-left:1.0in'><o:p> </o:p></p><p class=MsoNormal style='margin-left:1.0in'><span lang=EN-GB style='color:#1F497D'>Dear Regina,<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span lang=EN-GB style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span lang=EN-GB style='color:#1F497D'>The query returns<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span lang=EN-GB style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span lang=EN-GB style='color:#1F497D'>"POSTGIS="2.2.0 r14208" GEOS="3.5.0-CAPI-1.9.0 r4090" PROJ="Rel. 4.9.1, 04 March 2015" GDAL="GDAL 2.0.1, released 2015/09/15" LIBXML="2.7.8" LIBJSON="0.12" RASTER"<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span lang=EN-GB style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span lang=EN-GB style='color:#1F497D'>I just did a quick search for the logs (I am running this on x64 windows 10) and could not locate anything. I therefore assume that logging is turned off. I’m not sure exactly how to turn it on but will do some digging and report back with any logged errors as soon as I figure this out.<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span lang=EN-GB style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span lang=EN-GB style='color:#1F497D'>The tifs are created using the gdal 2 library (using C) and they are currently only up to ~50MB in size - they are just matrices which contain doubles from my model outputs (actually these data are DEMs). They have spatial referencing in the metadata and can be displayed without any problem in QGIS directly. The problem is I am wanting to extract cell data from transects (defined using points in shapefiles) from hundreds, possibly thousands of tifs which are output as a timeseries from the model. Doing this manually would be a little time-consuming hence my desire to do this via postgis. I think once it can see the tifs, the actual data extraction will be fairly trivial (I hope).<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span lang=EN-GB style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span lang=EN-GB style='color:#1F497D'>By the way – thanks for the book! I have version one also, but only just returned to experimenting with postgis after a break of two years or so (hence I am really having to re-learn everything).<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span lang=EN-GB style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span lang=EN-GB style='color:#1F497D'>Best wishes<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span lang=EN-GB style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span lang=EN-GB style='color:#1F497D'>Darrel<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span lang=EN-GB style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span lang=EN-GB style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span lang=EN-GB style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span lang=EN-GB style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span lang=EN-GB style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span lang=EN-GB style='color:#1F497D'><o:p> </o:p></span></p><div><div style='border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0in 0in 0in'><p class=MsoNormal style='margin-left:1.0in'><b>From:</b> postgis-users [<a href="mailto:postgis-users-bounces@lists.osgeo.org">mailto:postgis-users-bounces@lists.osgeo.org</a>] <b>On Behalf Of </b>Paragon Corporation<br><b>Sent:</b> 06 November 2015 22:13<br><b>To:</b> 'PostGIS Users Discussion' <<a href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a>><br><b>Subject:</b> Re: [postgis-users] I am probably being stupid but I cannot get my rasters to display using QGIS<o:p></o:p></p></div></div><p class=MsoNormal style='margin-left:1.0in'><span lang=EN-GB><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span style='color:#1F497D'>Darrel,<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span style='color:#1F497D'>What you are doing sounds fine. So not sure off hand why it doesn't work.<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span style='color:#1F497D'>Can you return the output of <o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span style='color:#1F497D'>SELECT postgis_full_version();<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span style='color:#1F497D'>Just want to see your GDAL version and also that your data directory is being picked up.<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span style='color:#1F497D'>Also there should be an error in the logs of PostGIS that should give more info about the error and if not, you can turn on logging in your db to see what queries QGIS is pushing.<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span style='color:#1F497D'>Logs are usually kept in postgres data folder in folder pg_logs.<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span style='color:#1F497D'>Hope that helps,<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span style='color:#1F497D'>Regina<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span style='color:#1F497D'><a href="http://www.postgis.us">http://www.postgis.us</a><o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span style='color:#1F497D'><a href="http://postgis.net">http://postgis.net</a><o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span style='color:#1F497D'><o:p> </o:p></span></p><div><div style='border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0in 0in 0in'><p class=MsoNormal style='margin-left:1.5in'><b>From:</b> postgis-users [<a href="mailto:postgis-users-bounces@lists.osgeo.org">mailto:postgis-users-bounces@lists.osgeo.org</a>] <b>On Behalf Of </b>Darrel Maddy<br><b>Sent:</b> Friday, November 06, 2015 9:15 AM<br><b>To:</b> <a href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><br><b>Subject:</b> [postgis-users] I am probably being stupid but I cannot get my rasters to display using QGIS<o:p></o:p></p></div></div><p class=MsoNormal style='margin-left:1.5in'><o:p> </o:p></p><p class=MsoNormal style='margin-left:1.5in'><span lang=EN-GB>Dear all,<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span lang=EN-GB><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span lang=EN-GB>Please accept this elementary question but I have been struggling with this despite working through my copy of postgis in action (ver 2).<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span lang=EN-GB><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span lang=EN-GB>I have imported my tifs into a table as follows (I am using postgres 9.4 and postgis 2.2.0): <o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span lang=EN-GB><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span lang=EN-GB>raster2pgsql -I -C -M *.tif -F -s 27700 testrast.all | psql -h localhost -U postgres -p 5433 -d postgisi_in_action<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span lang=EN-GB><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span lang=EN-GB>The table appears in pgAdmin as expected. At this stage all I am trying to do is visualise one of the tifs (there are 30) in QGIS (2.10). I connect via dbmanager and can see the table.<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span lang=EN-GB><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span lang=EN-GB>I have tried add to canvas on the ‘all’ table but it simply falls over. I also tried running a simple query in the sql window<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span lang=EN-GB><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span lang=EN-GB>select rid,rast::geometry from testrast.all WHERE rid=1 and adding this as a layer. This produces a box (presumably just of the dimensions of the raster). <o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span lang=EN-GB><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span lang=EN-GB>How can I get it to display the actual raster image itself?<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span lang=EN-GB><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span lang=EN-GB>Frankly I am embarrassed asking this question but I just don’t see how to do this.<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span lang=EN-GB><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span lang=EN-GB>Darrel<o:p></o:p></span></p></div></body></html>