Darrel,
Even better, replace your version of this function with this patched one,
and hopefully that should fix your issue:
CREATE OR REPLACE FUNCTION _raster_constraint_info_scale(rastschema name,
rasttable name, rastcolumn name, axis char)
RETURNS double precision AS $$
WITH c AS (SELECT
regexp_replace(
replace(
split_part(
split_part(s.consrc, ' = ', 2),
'::', 1
),
'round(', ''
),
'[ ''''\(\)]', '', 'g'
)::text AS val
FROM pg_class c, pg_namespace n, pg_attribute a,
pg_constraint s
WHERE n.nspname = $1
AND c.relname = $2
AND a.attname = $3
AND a.attrelid = c.oid
AND s.connamespace = n.oid
AND s.conrelid = c.oid
AND a.attnum = ANY (s.conkey)
AND s.consrc LIKE '%st_scale' || $4 || '(% =
%')
-- if it is a comma separated list of two numbers then need to use round
SELECT CASE WHEN split_part(c.val,',', 2) > ''
THEN round( split_part(c.val, ',',1)::numeric,
split_part(c.val,',',2)::integer )::float8
ELSE c.val::float8 END
FROM c;
$$ LANGUAGE sql STABLE STRICT
COST 100;
Hope that helps,
Regina
From: postgis-users [mailto:postgis-users-boun...@lists.osgeo.org] On Behalf
Of Paragon Corporation
Sent: Saturday, November 07, 2015 4:33 AM
To: 'PostGIS Users Discussion' <postgis-users@lists.osgeo.org>
Subject: Re: [postgis-users] I am probably being stupid but I cannot get my
rasters to display using QGIS
Darrel,
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.
Can you run this query:
SELECT * FROM raster_columns;
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:
https://trac.osgeo.org/postgis/ticket/3360
As a workaround for now until we have a patch, drop the constraints named:
enforce_scalex_rast, enforce_scaley_rast
with something like: ALTER TABLE public.prec DROP CONSTRAINT
enforce_scalex_rast;
If you are using pgAdmin, you can just expand the table and constraints and
right-click and drop.
Hope that helps,
Regina
From: postgis-users [mailto:postgis-users-boun...@lists.osgeo.org] On Behalf
Of Darrel Maddy
Sent: Friday, November 06, 2015 7:27 PM
To: PostGIS Users Discussion <postgis-users@lists.osgeo.org
<mailto:postgis-users@lists.osgeo.org> >
Subject: Re: [postgis-users] I am probably being stupid but I cannot get my
rasters to display using QGIS
Dear Regina,
The query returns
"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"
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.
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).
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).
Best wishes
Darrel
From: postgis-users [mailto:postgis-users-boun...@lists.osgeo.org] On Behalf
Of Paragon Corporation
Sent: 06 November 2015 22:13
To: 'PostGIS Users Discussion' <postgis-users@lists.osgeo.org
<mailto:postgis-users@lists.osgeo.org> >
Subject: Re: [postgis-users] I am probably being stupid but I cannot get my
rasters to display using QGIS
Darrel,
What you are doing sounds fine. So not sure off hand why it doesn't work.
Can you return the output of
SELECT postgis_full_version();
Just want to see your GDAL version and also that your data directory is
being picked up.
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.
Logs are usually kept in postgres data folder in folder pg_logs.
Hope that helps,
Regina
http://www.postgis.us
http://postgis.net
From: postgis-users [mailto:postgis-users-boun...@lists.osgeo.org] On Behalf
Of Darrel Maddy
Sent: Friday, November 06, 2015 9:15 AM
To: postgis-users@lists.osgeo.org <mailto:postgis-users@lists.osgeo.org>
Subject: [postgis-users] I am probably being stupid but I cannot get my
rasters to display using QGIS
Dear all,
Please accept this elementary question but I have been struggling with this
despite working through my copy of postgis in action (ver 2).
I have imported my tifs into a table as follows (I am using postgres 9.4 and
postgis 2.2.0):
raster2pgsql -I -C -M *.tif -F -s 27700 testrast.all | psql -h localhost -U
postgres -p 5433 -d postgisi_in_action
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.
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
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).
How can I get it to display the actual raster image itself?
Frankly I am embarrassed asking this question but I just don't see how to do
this.
Darrel
_______________________________________________
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/postgis-users
