[postgis-users] I am probably being stupid but I cannot get my rasters to display using QGIS
Darrel Maddy
darrel.maddy at newcastle.ac.uk
Fri Nov 6 16:53:49 PST 2015
OK I just turned on the logging in pgAdmin and ran the query in QGIS. This is what it produced in the log (sorry this makes no sense to me) - all I get on screen is a filled rectangle and I cannot see anything in the style window which suggests there is variable data here.
The bit that relates to the query : SELECT rid,rast::geometry from testrast.all WHERE rid=1 is as shown below - apologies it seems to be quite long!
Thanks
Darrel
2015-11-07 00:36:30 STATUS : Refreshing database postgisi_in_action...
2015-11-07 00:36:30 QUERY : Set query (localhost:5433): SELECT db.oid, datname, db.dattablespace AS spcoid, spcname, datallowconn, datacl, pg_encoding_to_char(encoding) AS serverencoding, pg_get_userbyid(datdba) AS datowner,has_database_privilege(db.oid, 'CREATE') as cancreate,
current_setting('default_tablespace') AS default_tablespace,
descr.description
, db.datconnlimit as connectionlimit, db.datcollate as collate, db.datctype as ctype,
(SELECT array_agg(label) FROM pg_shseclabel sl1 WHERE sl1.objoid=db.oid) AS labels,
(SELECT array_agg(provider) FROM pg_shseclabel sl2 WHERE sl2.objoid=db.oid) AS providers FROM pg_database db
LEFT OUTER JOIN pg_tablespace ta ON db.dattablespace=ta.OID
LEFT OUTER JOIN pg_shdescription descr ON (db.oid=descr.objoid AND descr.classoid='pg_database'::regclass)
WHERE db.oid=17760::oid
ORDER BY datname
2015-11-07 00:36:30 QUERY : Scalar query (localhost:5433): SELECT version();
2015-11-07 00:36:30 QUERY : Query result: PostgreSQL 9.4.4, compiled by Visual C++ build 1800, 64-bit
2015-11-07 00:36:30 QUERY : Set query (localhost:5433): SET DateStyle=ISO;
SET client_min_messages=notice;
SET bytea_output=escape;
SELECT oid, pg_encoding_to_char(encoding) AS encoding, datlastsysoid
FROM pg_database WHERE oid = 17760
2015-11-07 00:36:30 QUERY : Scalar query (localhost:5433): SELECT proname FROM pg_proc WHERE proname='pg_get_viewdef' AND proargtypes[1]=16
2015-11-07 00:36:30 QUERY : Query result: pg_get_viewdef
2015-11-07 00:36:30 QUERY : Scalar query (localhost:5433): SHOW search_path
2015-11-07 00:36:30 QUERY : Query result: "$user",public
2015-11-07 00:36:30 QUERY : Set query (localhost:5433): SELECT nspname, session_user=nspname AS isuser FROM pg_namespace
2015-11-07 00:36:30 QUERY : Scalar query (localhost:5433): SELECT defaclacl FROM pg_catalog.pg_default_acl dacl WHERE dacl.defaclnamespace = 0::OID AND defaclobjtype='r'
2015-11-07 00:36:30 QUERY : Scalar query (localhost:5433): SELECT defaclacl FROM pg_catalog.pg_default_acl dacl WHERE dacl.defaclnamespace = 0::OID AND defaclobjtype='S'
2015-11-07 00:36:30 QUERY : Scalar query (localhost:5433): SELECT defaclacl FROM pg_catalog.pg_default_acl dacl WHERE dacl.defaclnamespace = 0::OID AND defaclobjtype='f'
2015-11-07 00:36:30 QUERY : Scalar query (localhost:5433): SELECT defaclacl FROM pg_catalog.pg_default_acl dacl WHERE dacl.defaclnamespace = 0::OID AND defaclobjtype='T'
2015-11-07 00:36:30 QUERY : Set query (localhost:5433): SELECT 2 AS nsptyp,
nsp.nspname, nsp.oid, pg_get_userbyid(nspowner) AS namespaceowner, nspacl, description, FALSE as cancreate
FROM pg_namespace nsp
LEFT OUTER JOIN pg_description des ON (des.objoid=nsp.oid AND des.classoid='pg_namespace'::regclass)
WHERE ((nspname = 'pg_catalog' AND EXISTS (SELECT 1 FROM pg_class WHERE relname = 'pg_class' AND relnamespace = nsp.oid LIMIT 1)) OR
(nspname = 'pgagent' AND EXISTS (SELECT 1 FROM pg_class WHERE relname = 'pga_job' AND relnamespace = nsp.oid LIMIT 1)) OR
(nspname = 'information_schema' AND EXISTS (SELECT 1 FROM pg_class WHERE relname = 'tables' AND relnamespace = nsp.oid LIMIT 1)) OR
(nspname LIKE '_%' AND EXISTS (SELECT 1 FROM pg_proc WHERE proname='slonyversion' AND pronamespace = nsp.oid LIMIT 1))
) AND nspname NOT LIKE E'pg\\_temp\\_%'AND nspname NOT LIKE E'pg\\_toast_temp\\_%' ORDER BY 1, nspname
2015-11-07 00:36:30 QUERY : Scalar query (localhost:5433): SELECT defaclacl FROM pg_catalog.pg_default_acl dacl WHERE dacl.defaclnamespace = 11585::oid AND defaclobjtype='r'
2015-11-07 00:36:30 QUERY : Scalar query (localhost:5433): SELECT defaclacl FROM pg_catalog.pg_default_acl dacl WHERE dacl.defaclnamespace = 11585::oid AND defaclobjtype='S'
2015-11-07 00:36:30 QUERY : Scalar query (localhost:5433): SELECT defaclacl FROM pg_catalog.pg_default_acl dacl WHERE dacl.defaclnamespace = 11585::oid AND defaclobjtype='f'
2015-11-07 00:36:30 QUERY : Scalar query (localhost:5433): SELECT defaclacl FROM pg_catalog.pg_default_acl dacl WHERE dacl.defaclnamespace = 11585::oid AND defaclobjtype='T'
2015-11-07 00:36:30 QUERY : Scalar query (localhost:5433): SELECT defaclacl FROM pg_catalog.pg_default_acl dacl WHERE dacl.defaclnamespace = 11::oid AND defaclobjtype='r'
2015-11-07 00:36:30 QUERY : Scalar query (localhost:5433): SELECT defaclacl FROM pg_catalog.pg_default_acl dacl WHERE dacl.defaclnamespace = 11::oid AND defaclobjtype='S'
2015-11-07 00:36:30 QUERY : Scalar query (localhost:5433): SELECT defaclacl FROM pg_catalog.pg_default_acl dacl WHERE dacl.defaclnamespace = 11::oid AND defaclobjtype='f'
2015-11-07 00:36:30 QUERY : Scalar query (localhost:5433): SELECT defaclacl FROM pg_catalog.pg_default_acl dacl WHERE dacl.defaclnamespace = 11::oid AND defaclobjtype='T'
2015-11-07 00:36:30 QUERY : Set query (localhost:5433): SELECT e.oid, e.xmin, e.evtname AS name, REPLACE(e.evtevent, '_', ' ') AS eventname, pg_catalog.pg_get_userbyid(e.evtowner) AS eventowner, CASE e.evtenabled WHEN 'O' THEN 'enabled' WHEN 'R' THEN 'replica' WHEN 'A' THEN 'always' WHEN 'D' THEN 'disabled' END AS enabled, e.evtfoid AS eventfuncoid, e.evtfoid::regproc AS eventfunname, array_to_string(array(select quote_literal(x) from unnest(evttags) as t(x)), ', ') AS when, pg_catalog.obj_description(e.oid, 'pg_event_trigger') AS comment, p.prosrc AS source, p.pronamespace AS schemaoid, l.lanname AS language FROM pg_event_trigger e
LEFT OUTER JOIN pg_proc p ON p.oid=e.evtfoid
LEFT OUTER JOIN pg_language l ON l.oid=p.prolang
WHERE 1=1 ORDER BY e.evtname
2015-11-07 00:36:30 QUERY : Set query (localhost:5433): select x.oid, pg_get_userbyid(extowner) AS owner, x.extname, n.nspname, x.extrelocatable, x.extversion, e.comment FROM pg_extension x
JOIN pg_namespace n on x.extnamespace=n.oid
join pg_available_extensions() e(name, default_version, comment) ON x.extname=e.name
ORDER BY x.extname
2015-11-07 00:36:30 QUERY : Set query (localhost:5433): SELECT CASE WHEN nspname LIKE E'pg\\_temp\\_%' THEN 1
WHEN (nspname LIKE E'pg\\_%') THEN 0
ELSE 3 END AS nsptyp,
nsp.nspname, nsp.oid, pg_get_userbyid(nspowner) AS namespaceowner, nspacl, description, has_schema_privilege(nsp.oid, 'CREATE') as cancreate,
(SELECT array_agg(label) FROM pg_seclabels sl1 WHERE sl1.objoid=nsp.oid) AS labels,
(SELECT array_agg(provider) FROM pg_seclabels sl2 WHERE sl2.objoid=nsp.oid) AS providers
FROM pg_namespace nsp
LEFT OUTER JOIN pg_description des ON (des.objoid=nsp.oid AND des.classoid='pg_namespace'::regclass)
WHERE NOT ((nspname = 'pg_catalog' AND EXISTS (SELECT 1 FROM pg_class WHERE relname = 'pg_class' AND relnamespace = nsp.oid LIMIT 1)) OR
(nspname = 'pgagent' AND EXISTS (SELECT 1 FROM pg_class WHERE relname = 'pga_job' AND relnamespace = nsp.oid LIMIT 1)) OR
(nspname = 'information_schema' AND EXISTS (SELECT 1 FROM pg_class WHERE relname = 'tables' AND relnamespace = nsp.oid LIMIT 1)) OR
(nspname LIKE '_%' AND EXISTS (SELECT 1 FROM pg_proc WHERE proname='slonyversion' AND pronamespace = nsp.oid LIMIT 1))
) AND nspname NOT LIKE E'pg\\_temp\\_%'AND nspname NOT LIKE E'pg\\_toast_temp\\_%' ORDER BY 1, nspname
2015-11-07 00:36:30 QUERY : Scalar query (localhost:5433): SELECT defaclacl FROM pg_catalog.pg_default_acl dacl WHERE dacl.defaclnamespace = 19126::oid AND defaclobjtype='r'
2015-11-07 00:36:30 QUERY : Scalar query (localhost:5433): SELECT defaclacl FROM pg_catalog.pg_default_acl dacl WHERE dacl.defaclnamespace = 19126::oid AND defaclobjtype='S'
2015-11-07 00:36:30 QUERY : Scalar query (localhost:5433): SELECT defaclacl FROM pg_catalog.pg_default_acl dacl WHERE dacl.defaclnamespace = 19126::oid AND defaclobjtype='f'
2015-11-07 00:36:30 QUERY : Scalar query (localhost:5433): SELECT defaclacl FROM pg_catalog.pg_default_acl dacl WHERE dacl.defaclnamespace = 19126::oid AND defaclobjtype='T'
2015-11-07 00:36:30 QUERY : Scalar query (localhost:5433): SELECT defaclacl FROM pg_catalog.pg_default_acl dacl WHERE dacl.defaclnamespace = 2200::oid AND defaclobjtype='r'
2015-11-07 00:36:30 QUERY : Scalar query (localhost:5433): SELECT defaclacl FROM pg_catalog.pg_default_acl dacl WHERE dacl.defaclnamespace = 2200::oid AND defaclobjtype='S'
2015-11-07 00:36:30 QUERY : Scalar query (localhost:5433): SELECT defaclacl FROM pg_catalog.pg_default_acl dacl WHERE dacl.defaclnamespace = 2200::oid AND defaclobjtype='f'
2015-11-07 00:36:30 QUERY : Scalar query (localhost:5433): SELECT defaclacl FROM pg_catalog.pg_default_acl dacl WHERE dacl.defaclnamespace = 2200::oid AND defaclobjtype='T'
2015-11-07 00:36:30 QUERY : Scalar query (localhost:5433): SELECT defaclacl FROM pg_catalog.pg_default_acl dacl WHERE dacl.defaclnamespace = 19135::oid AND defaclobjtype='r'
2015-11-07 00:36:30 QUERY : Scalar query (localhost:5433): SELECT defaclacl FROM pg_catalog.pg_default_acl dacl WHERE dacl.defaclnamespace = 19135::oid AND defaclobjtype='S'
2015-11-07 00:36:30 QUERY : Scalar query (localhost:5433): SELECT defaclacl FROM pg_catalog.pg_default_acl dacl WHERE dacl.defaclnamespace = 19135::oid AND defaclobjtype='f'
2015-11-07 00:36:30 QUERY : Scalar query (localhost:5433): SELECT defaclacl FROM pg_catalog.pg_default_acl dacl WHERE dacl.defaclnamespace = 19135::oid AND defaclobjtype='T'
2015-11-07 00:36:30 QUERY : Set query (localhost:5433): SELECT nsp.oid, substr(nspname, 2) as clustername, nspname, pg_get_userbyid(nspowner) AS namespaceowner, description
FROM pg_namespace nsp
LEFT OUTER JOIN pg_description des ON des.objoid=nsp.oid
JOIN pg_proc pro ON pronamespace=nsp.oid AND proname = 'slonyversion'
ORDER BY nspname
2015-11-07 00:36:30 QUERY : Scalar query (localhost:5433): SELECT COUNT(*) FROM
(SELECT tgargs from pg_trigger tr
LEFT JOIN pg_depend dep ON dep.objid=tr.oid AND deptype = 'i'
LEFT JOIN pg_constraint co ON refobjid = co.oid AND contype = 'f'
WHERE
tgisinternal
AND co.oid IS NULL
GROUP BY tgargs
HAVING count(1) = 3) AS foo
2015-11-07 00:36:30 QUERY : Query result: 0
2015-11-07 00:36:30 QUERY : Set query (localhost:5433): WITH configs AS (SELECT rolname, unnest(setconfig) AS config FROM pg_db_role_setting s LEFT JOIN pg_roles r ON r.oid=s.setrole WHERE s.setdatabase=17760)
SELECT rolname, split_part(config, '=', 1) AS variable, replace(config, split_part(config, '=', 1) || '=', '') AS value
FROM configs
2015-11-07 00:36:30 QUERY : Set query (localhost:5433): SELECT c.oid, c.collname, c.collcollate, c.collctype,
pg_get_userbyid(c.collowner) as cowner, description
FROM pg_collation c
JOIN pg_namespace n ON n.oid=c.collnamespace
LEFT OUTER JOIN pg_description des ON (des.objoid=c.oid AND des.classoid='pg_collation'::regclass)
WHERE c.collnamespace = 19135::oid
ORDER BY c.collname
2015-11-07 00:36:30 QUERY : Set query (localhost:5433): SELECT d.oid, d.typname as domname, d.typbasetype, format_type(b.oid,NULL) as basetype, pg_get_userbyid(d.typowner) as domainowner,
c.oid AS colloid, c.collname, cn.nspname as collnspname,
d.typlen, d.typtypmod, d.typnotnull, d.typdefault, d.typndims, d.typdelim, bn.nspname as basensp,
description, (SELECT COUNT(1) FROM pg_type t2 WHERE t2.typname=d.typname) > 1 AS domisdup,
(SELECT COUNT(1) FROM pg_type t3 WHERE t3.typname=b.typname) > 1 AS baseisdup,
(SELECT array_agg(label) FROM pg_seclabels sl1 WHERE sl1.objoid=d.oid) AS labels,
(SELECT array_agg(provider) FROM pg_seclabels sl2 WHERE sl2.objoid=d.oid) AS providers
FROM pg_type d
JOIN pg_type b ON b.oid = d.typbasetype
JOIN pg_namespace bn ON bn.oid=b.typnamespace
LEFT OUTER JOIN pg_description des ON (des.objoid=d.oid AND des.classoid='pg_type'::regclass)
LEFT OUTER JOIN pg_collation c ON d.typcollation=c.oid
LEFT OUTER JOIN pg_namespace cn ON c.collnamespace=cn.oid
WHERE d.typtype = 'd' AND d.typnamespace = 19135::oid
ORDER BY d.typname
2015-11-07 00:36:30 QUERY : Set query (localhost:5433): SELECT cfg.oid, cfg.cfgname, pg_get_userbyid(cfg.cfgowner) as cfgowner, cfg.cfgparser, parser.prsname as parsername, description
FROM pg_ts_config cfg
LEFT OUTER JOIN pg_ts_parser parser ON parser.oid=cfg.cfgparser
LEFT OUTER JOIN pg_description des ON (des.objoid=cfg.oid AND des.classoid='pg_ts_config'::regclass)
WHERE cfg.cfgnamespace = 19135::oid
ORDER BY cfg.cfgname
2015-11-07 00:36:30 QUERY : Set query (localhost:5433): SELECT dict.oid, dict.dictname, pg_get_userbyid(dict.dictowner) as dictowner, t.tmplname, dict.dictinitoption, description
FROM pg_ts_dict dict
LEFT OUTER JOIN pg_ts_template t ON t.oid=dict.dicttemplate
LEFT OUTER JOIN pg_description des ON (des.objoid=dict.oid AND des.classoid='pg_ts_dict'::regclass)
WHERE dict.dictnamespace = 19135::oid
ORDER BY dict.dictname
2015-11-07 00:36:30 QUERY : Set query (localhost:5433): SELECT prs.oid, prs.prsname, prs.prsstart, prs.prstoken, prs.prsend, prs.prslextype, prs.prsheadline, description
FROM pg_ts_parser prs
LEFT OUTER JOIN pg_description des ON (des.objoid=prs.oid AND des.classoid='pg_ts_parser'::regclass)
WHERE prs.prsnamespace = 19135::oid
ORDER BY prs.prsname
2015-11-07 00:36:30 QUERY : Set query (localhost:5433): SELECT tmpl.oid, tmpl.tmplname, tmpl.tmplinit, tmpl.tmpllexize, description
FROM pg_ts_template tmpl
LEFT OUTER JOIN pg_description des ON (des.objoid=tmpl.oid AND des.classoid='pg_ts_template'::regclass)
WHERE tmpl.tmplnamespace = 19135::oid
ORDER BY tmpl.tmplname
2015-11-07 00:36:30 QUERY : Set query (localhost:5433): SELECT proname, pronargs, proargtypes[0] AS arg0, proargtypes[1] AS arg1, proargtypes[2] AS arg2
FROM pg_proc
JOIN pg_namespace n ON n.oid=pronamespace
WHERE proname IN ('pg_tablespace_size', 'pg_file_read', 'pg_logfile_rotate', 'pg_postmaster_starttime', 'pg_terminate_backend', 'pg_reload_conf', 'pgstattuple', 'pgstatindex')
AND nspname IN ('pg_catalog', 'public')
2015-11-07 00:36:30 QUERY : Scalar query (localhost:5433): SELECT count(*) FROM pg_attribute WHERE attrelid = 'pg_catalog.pg_proc'::regclass AND attname = 'proargdefaults'
2015-11-07 00:36:30 QUERY : Query result: 1
2015-11-07 00:36:30 QUERY : Set query (localhost:5433): SELECT pr.oid, pr.xmin, pr.*, format_type(TYP.oid, NULL) AS typname, typns.nspname AS typnsp, lanname, proargnames, pg_get_expr(proargdefaults, 'pg_catalog.pg_class'::regclass) AS proargdefaultvals, pronargdefaults, proconfig, pg_get_userbyid(proowner) as funcowner, description,
(SELECT array_agg(label) FROM pg_seclabels sl1 WHERE sl1.objoid=pr.oid) AS labels,
(SELECT array_agg(provider) FROM pg_seclabels sl2 WHERE sl2.objoid=pr.oid) AS providers
FROM pg_proc pr
JOIN pg_type typ ON typ.oid=prorettype
JOIN pg_namespace typns ON typns.oid=typ.typnamespace
JOIN pg_language lng ON lng.oid=prolang
LEFT OUTER JOIN pg_description des ON (des.objoid=pr.oid AND des.classoid='pg_proc'::regclass)
WHERE proisagg = FALSE AND pronamespace = 19135::oid
AND typname NOT IN ('trigger', 'event_trigger')
ORDER BY proname
2015-11-07 00:36:30 QUERY : Set query (localhost:5433): SELECT oid, format_type(oid, NULL) AS typname FROM pg_type
2015-11-07 00:36:30 QUERY : Set query (localhost:5433): SELECT cl.oid, relname, pg_get_userbyid(relowner) AS seqowner, relacl, description,
(SELECT array_agg(label) FROM pg_seclabels sl1 WHERE sl1.objoid=cl.oid) AS labels,
(SELECT array_agg(provider) FROM pg_seclabels sl2 WHERE sl2.objoid=cl.oid) AS providers
FROM pg_class cl
LEFT OUTER JOIN pg_description des ON (des.objoid=cl.oid AND des.classoid='pg_class'::regclass)
WHERE relkind = 'S' AND relnamespace = 19135::oid
ORDER BY relname
2015-11-07 00:36:30 QUERY : Set query (localhost:5433): SELECT rel.oid, rel.relname, rel.reltablespace AS spcoid, spc.spcname, pg_get_userbyid(rel.relowner) AS relowner, rel.relacl, rel.relhasoids, rel.relhassubclass, rel.reltuples, des.description, con.conname, con.conkey,
EXISTS(select 1 FROM pg_trigger
JOIN pg_proc pt ON pt.oid=tgfoid AND pt.proname='logtrigger'
JOIN pg_proc pc ON pc.pronamespace=pt.pronamespace AND pc.proname='slonyversion'
WHERE tgrelid=rel.oid) AS isrepl,
(select count(*) FROM pg_trigger
WHERE tgrelid=rel.oid AND tgisinternal = FALSE) AS triggercount
, rel.relpersistence
, substring(array_to_string(rel.reloptions, ',') FROM 'fillfactor=([0-9]*)') AS fillfactor
, substring(array_to_string(rel.reloptions, ',') FROM 'autovacuum_enabled=([a-z|0-9]*)') AS autovacuum_enabled
, substring(array_to_string(rel.reloptions, ',') FROM 'autovacuum_vacuum_threshold=([0-9]*)') AS autovacuum_vacuum_threshold
, substring(array_to_string(rel.reloptions, ',') FROM 'autovacuum_vacuum_scale_factor=([0-9]*[.][0-9]*)') AS autovacuum_vacuum_scale_factor
, substring(array_to_string(rel.reloptions, ',') FROM 'autovacuum_analyze_threshold=([0-9]*)') AS autovacuum_analyze_threshold
, substring(array_to_string(rel.reloptions, ',') FROM 'autovacuum_analyze_scale_factor=([0-9]*[.][0-9]*)') AS autovacuum_analyze_scale_factor
, substring(array_to_string(rel.reloptions, ',') FROM 'autovacuum_vacuum_cost_delay=([0-9]*)') AS autovacuum_vacuum_cost_delay
, substring(array_to_string(rel.reloptions, ',') FROM 'autovacuum_vacuum_cost_limit=([0-9]*)') AS autovacuum_vacuum_cost_limit
, substring(array_to_string(rel.reloptions, ',') FROM 'autovacuum_freeze_min_age=([0-9]*)') AS autovacuum_freeze_min_age
, substring(array_to_string(rel.reloptions, ',') FROM 'autovacuum_freeze_max_age=([0-9]*)') AS autovacuum_freeze_max_age
, substring(array_to_string(rel.reloptions, ',') FROM 'autovacuum_freeze_table_age=([0-9]*)') AS autovacuum_freeze_table_age
, substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_enabled=([a-z|0-9]*)') AS toast_autovacuum_enabled
, substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_vacuum_threshold=([0-9]*)') AS toast_autovacuum_vacuum_threshold
, substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_vacuum_scale_factor=([0-9]*[.][0-9]*)') AS toast_autovacuum_vacuum_scale_factor
, substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_analyze_threshold=([0-9]*)') AS toast_autovacuum_analyze_threshold
, substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_analyze_scale_factor=([0-9]*[.][0-9]*)') AS toast_autovacuum_analyze_scale_factor
, substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_vacuum_cost_delay=([0-9]*)') AS toast_autovacuum_vacuum_cost_delay
, substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_vacuum_cost_limit=([0-9]*)') AS toast_autovacuum_vacuum_cost_limit
, substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_freeze_min_age=([0-9]*)') AS toast_autovacuum_freeze_min_age
, substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_freeze_max_age=([0-9]*)') AS toast_autovacuum_freeze_max_age
, substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_freeze_table_age=([0-9]*)') AS toast_autovacuum_freeze_table_age
, rel.reloptions AS reloptions, tst.reloptions AS toast_reloptions
, (CASE WHEN rel.reltoastrelid = 0 THEN false ELSE true END) AS hastoasttable
, rel.reloftype, typ.typname
,
(SELECT array_agg(label) FROM pg_seclabels sl1 WHERE sl1.objoid=rel.oid AND sl1.objsubid=0) AS labels,
(SELECT array_agg(provider) FROM pg_seclabels sl2 WHERE sl2.objoid=rel.oid AND sl2.objsubid=0) AS providers FROM pg_class rel
LEFT OUTER JOIN pg_tablespace spc on spc.oid=rel.reltablespace
LEFT OUTER JOIN pg_description des ON (des.objoid=rel.oid AND des.objsubid=0 AND des.classoid='pg_class'::regclass)
LEFT OUTER JOIN pg_constraint con ON con.conrelid=rel.oid AND con.contype='p'
LEFT OUTER JOIN pg_class tst ON tst.oid = rel.reltoastrelid
LEFT JOIN pg_type typ ON rel.reloftype=typ.oid
WHERE rel.relkind IN ('r','s','t') AND rel.relnamespace = 19135::oid
ORDER BY rel.relname
2015-11-07 00:36:30 QUERY : Set query (localhost:5433): SELECT pr.oid, pr.xmin, pr.*, format_type(TYP.oid, NULL) AS typname, typns.nspname AS typnsp, lanname, proargnames, pg_get_expr(proargdefaults, 'pg_catalog.pg_class'::regclass) AS proargdefaultvals, pronargdefaults, proconfig, pg_get_userbyid(proowner) as funcowner, description,
(SELECT array_agg(label) FROM pg_seclabels sl1 WHERE sl1.objoid=pr.oid) AS labels,
(SELECT array_agg(provider) FROM pg_seclabels sl2 WHERE sl2.objoid=pr.oid) AS providers
FROM pg_proc pr
JOIN pg_type typ ON typ.oid=prorettype
JOIN pg_namespace typns ON typns.oid=typ.typnamespace
JOIN pg_language lng ON lng.oid=prolang
LEFT OUTER JOIN pg_description des ON (des.objoid=pr.oid AND des.classoid='pg_proc'::regclass)
WHERE proisagg = FALSE AND pronamespace = 19135::oid
AND (typname IN ('trigger', 'event_trigger')
AND lanname NOT IN ('edbspl', 'sql', 'internal')) ORDER BY proname
2015-11-07 00:36:30 QUERY : Set query (localhost:5433): SELECT oid, format_type(oid, NULL) AS typname FROM pg_type
2015-11-07 00:36:30 QUERY : Set query (localhost:5433): SELECT c.oid, c.xmin, c.relname,c.reltablespace AS spcoid, c.relkind, c.relispopulated AS ispopulated,spc.spcname, pg_get_userbyid(c.relowner) AS viewowner, c.relacl, description, pg_get_viewdef(c.oid, true) AS definition,
(SELECT array_agg(label) FROM pg_seclabels sl1 WHERE sl1.objoid=c.oid AND sl1.objsubid=0) AS labels,
(SELECT array_agg(provider) FROM pg_seclabels sl2 WHERE sl2.objoid=c.oid AND sl2.objsubid=0) AS providers,
substring(array_to_string(c.reloptions, ',') FROM 'security_barrier=([a-z|0-9]*)') AS security_barrier, substring(array_to_string(c.reloptions, ',') FROM 'fillfactor=([0-9]*)') AS fillfactor
, substring(array_to_string(c.reloptions, ',') FROM 'autovacuum_enabled=([a-z|0-9]*)') AS autovacuum_enabled
, substring(array_to_string(c.reloptions, ',') FROM 'autovacuum_vacuum_threshold=([0-9]*)') AS autovacuum_vacuum_threshold
, substring(array_to_string(c.reloptions, ',') FROM 'autovacuum_vacuum_scale_factor=([0-9]*[.][0-9]*)') AS autovacuum_vacuum_scale_factor
, substring(array_to_string(c.reloptions, ',') FROM 'autovacuum_analyze_threshold=([0-9]*)') AS autovacuum_analyze_threshold
, substring(array_to_string(c.reloptions, ',') FROM 'autovacuum_analyze_scale_factor=([0-9]*[.][0-9]*)') AS autovacuum_analyze_scale_factor
, substring(array_to_string(c.reloptions, ',') FROM 'autovacuum_vacuum_cost_delay=([0-9]*)') AS autovacuum_vacuum_cost_delay
, substring(array_to_string(c.reloptions, ',') FROM 'autovacuum_vacuum_cost_limit=([0-9]*)') AS autovacuum_vacuum_cost_limit
, substring(array_to_string(c.reloptions, ',') FROM 'autovacuum_freeze_min_age=([0-9]*)') AS autovacuum_freeze_min_age
, substring(array_to_string(c.reloptions, ',') FROM 'autovacuum_freeze_max_age=([0-9]*)') AS autovacuum_freeze_max_age
, substring(array_to_string(c.reloptions, ',') FROM 'autovacuum_freeze_table_age=([0-9]*)') AS autovacuum_freeze_table_age
, substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_enabled=([a-z|0-9]*)') AS toast_autovacuum_enabled
, substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_vacuum_threshold=([0-9]*)') AS toast_autovacuum_vacuum_threshold
, substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_vacuum_scale_factor=([0-9]*[.][0-9]*)') AS toast_autovacuum_vacuum_scale_factor
, substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_analyze_threshold=([0-9]*)') AS toast_autovacuum_analyze_threshold
, substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_analyze_scale_factor=([0-9]*[.][0-9]*)') AS toast_autovacuum_analyze_scale_factor
, substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_vacuum_cost_delay=([0-9]*)') AS toast_autovacuum_vacuum_cost_delay
, substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_vacuum_cost_limit=([0-9]*)') AS toast_autovacuum_vacuum_cost_limit
, substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_freeze_min_age=([0-9]*)') AS toast_autovacuum_freeze_min_age
, substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_freeze_max_age=([0-9]*)') AS toast_autovacuum_freeze_max_age
, substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_freeze_table_age=([0-9]*)') AS toast_autovacuum_freeze_table_age
, c.reloptions AS reloptions, tst.reloptions AS toast_reloptions
, (CASE WHEN c.reltoastrelid = 0 THEN false ELSE true END) AS hastoasttable
,
substring(array_to_string(c.reloptions, ',') FROM 'check_option=([a-z]*)') AS check_option
FROM pg_class c
LEFT OUTER JOIN pg_tablespace spc on spc.oid=c.reltablespace
LEFT OUTER JOIN pg_description des ON (des.objoid=c.oid and des.objsubid=0 AND des.classoid='pg_class'::regclass)
LEFT OUTER JOIN pg_class tst ON tst.oid = c.reltoastrelid
WHERE ((c.relhasrules AND (EXISTS (
SELECT r.rulename FROM pg_rewrite r
WHERE ((r.ev_class = c.oid)
AND (bpchar(r.ev_type) = '1'::bpchar)) ))) OR (c.relkind = 'v'::char))
AND c.relnamespace = 19135::oid
ORDER BY relname
2015-11-07 00:36:30 QUERY : Scalar query (localhost:5433): SELECT defaclacl FROM pg_catalog.pg_default_acl dacl WHERE dacl.defaclnamespace = 19135::oid AND defaclobjtype='r'
2015-11-07 00:36:30 QUERY : Scalar query (localhost:5433): SELECT defaclacl FROM pg_catalog.pg_default_acl dacl WHERE dacl.defaclnamespace = 19135::oid AND defaclobjtype='S'
2015-11-07 00:36:30 QUERY : Scalar query (localhost:5433): SELECT defaclacl FROM pg_catalog.pg_default_acl dacl WHERE dacl.defaclnamespace = 19135::oid AND defaclobjtype='f'
2015-11-07 00:36:30 QUERY : Scalar query (localhost:5433): SELECT defaclacl FROM pg_catalog.pg_default_acl dacl WHERE dacl.defaclnamespace = 19135::oid AND defaclobjtype='T'
2015-11-07 00:36:30 STATUS : Refreshing database postgisi_in_action... (0.19 secs)
From: postgis-users [mailto:postgis-users-bounces at lists.osgeo.org] On Behalf Of Darrel Maddy
Sent: 07 November 2015 00:27
To: PostGIS Users Discussion <postgis-users at 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-bounces at lists.osgeo.org] On Behalf Of Paragon Corporation
Sent: 06 November 2015 22:13
To: 'PostGIS Users Discussion' <postgis-users at lists.osgeo.org<mailto:postgis-users at 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-bounces at lists.osgeo.org] On Behalf Of Darrel Maddy
Sent: Friday, November 06, 2015 9:15 AM
To: postgis-users at lists.osgeo.org<mailto:postgis-users at 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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20151107/d9889ea4/attachment.html>
More information about the postgis-users
mailing list