[postgis-users] How can I optimize a raster in postgis 2 for display?
Ivan Santiago
isantiago at ogp.pr.gov
Fri Mar 1 05:41:20 PST 2013
thanks for your interest.
as Pierre advised I need to be sure the GDAL version is the latest.
I do have QGIS 1.8 but many plugins were not updated due to account privileges limitations. (I think)...
100mb... mmm... that would be a problem. many of the images i have in sde/sql server are in the order of hundreds of gigabytes.
I was testing on smaller ones (geologic maps).
One of the rasters metadata:
ab_geol_20k
General info
Relation type:
Table
Owner:
postgres
Pages:
96
Rows (estimation):
10578
Rows (counted):
Unknown (find out<action:rows/count>)
Privileges:
select, insert, update, delete
PostGIS
Column:
rast
Geometry:
RASTER
Spatial ref:
NAD83 / Puerto Rico & Virgin Is. (32161)
Extent:
(unknown) (find out<action:extent/get>)
Fields
#
Name
Type
Length
Null
Default
1
rid
int4
4
N
nextval('ab_geol_20k_rid_seq'::regclass)
2
rast
raster
Y
3
filename
text
Y
Constraints
Name
Type
Column(s)
enforce_max_extent_rast
Check
rast
enforce_out_db_rast
Check
rast
enforce_nodata_values_rast
Check
rast
enforce_pixel_types_rast
Check
rast
enforce_num_bands_rast
Check
rast
enforce_same_alignment_rast
Check
rast
enforce_height_rast
Check
rast
enforce_width_rast
Check
rast
enforce_scaley_rast
Check
rast
enforce_scalex_rast
Check
rast
enforce_srid_rast
Check
rast
ab_geol_20k_pkey
Primary key
rid
Indexes
Name
Column(s)
ab_geol_20k_rast_gist
??? (#0)
What does this mean?----> ??? (#0)
Second one:
ab_ele
General info
Relation type:
Table
Owner:
postgres
Pages:
5
Rows (estimation):
506
Rows (counted):
Unknown (find out<action:rows/count>)
Privileges:
select, insert, update, delete
PostGIS
Column:
rast
Geometry:
RASTER
Spatial ref:
NAD83 / Puerto Rico & Virgin Is. (32161)
Extent:
(unknown) (find out<action:extent/get>)
Fields
#
Name
Type
Length
Null
Default
1
rid
int4
4
N
nextval('ambiental_topografia_rasters_mdt.ab_ele_rid_seq'::regclass)
2
rast
raster
Y
3
filename
text
Y
Constraints
Name
Type
Column(s)
enforce_max_extent_rast
Check
rast
enforce_out_db_rast
Check
rast
enforce_pixel_types_rast
Check
rast
enforce_num_bands_rast
Check
rast
enforce_same_alignment_rast
Check
rast
enforce_height_rast
Check
rast
enforce_width_rast
Check
rast
enforce_scaley_rast
Check
rast
enforce_scalex_rast
Check
rast
enforce_srid_rast
Check
rast
ab_ele_pkey
Primary key
rid
Indexes
Name
Column(s)
ab_ele_rast_gist
??? (#0)
When the raster (finally) appears in QGIS canvas, it is not georeferenced even though spatial reference appears in the metadata, as you can see.
This is the command I used to load this raster:
raster2pgsql
-I
-C
-e
-Y
E:\0_Postgis_Exports\Ambiental_Geologia_Rasters\ab_ele.img (Erdas img, 32mb)
-F
-M
-s 32161 (PR and USVI, NAD83, State Plane Coord Syst)
-t 128x128 (maybe this could be less than 100x100)
-l 6 (I do need to read more about this. I let ArcGIS decide that by default. The sde raster indicated "6" levels and I just used that number here.
ambiental_topografia_rasters_mdt.ab_ele |
psql
-d gisogp
-h 172.16.110.238
-p 5432
-U postgres
This raster (a DEM) appears all gray. Looks like it does not have statistics computed, just like happens in sde when statistics are not calculated.
I'd like to know more about how overviews work in PGIS Raster and also about if statistics are needed in PGIS Raster.
Thanks in advance.
---------------------------
Iván Santiago
GIS Specialist
Information Technologies
Office of Management and Budget<https://mail.ogp.gobierno.pr/exchweb/bin/redir.asp?URL=https://mail.ogp.gobierno.pr/exchweb/bin/redir.asp?URL=http://www.ogp.gobierno.pr/>
787.725.9420 x 2378
Calle Cruz 254
PO Box 9023228
San Juan, PR 00902-3228
From: postgis-users-bounces at lists.osgeo.org [mailto:postgis-users-bounces at lists.osgeo.org] On Behalf Of José Pedro Santos
Sent: Friday, March 01, 2013 5:05 AM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] How can I optimize a raster in postgis 2 for display?
Hi Santiago,
Which version of QGIS you are using? I have QGIS 1.8 and works fine for displaying rasters (at 100mb) more than that sometimes I have troubles. See the metadata of the files after import to QGIS to check if everything is OK and try to change the appearance of the files in the properties (color ramp).
QGIS 1.7 had some problems with the Postgis 2.0
Best Regards,
Jose Santos
> From: Pierre.Racine at sbf.ulaval.ca<mailto:Pierre.Racine at sbf.ulaval.ca>
> To: postgis-users at lists.osgeo.org<mailto:postgis-users at lists.osgeo.org>
> Date: Thu, 28 Feb 2013 11:22:49 -0500
> Subject: Re: [postgis-users] How can I optimize a raster in postgis 2 for display?
>
> The QGIS PostGIS raster plugin use GDAL to read rasters stored in PostGIS. I guess (but this is still not clear to me as I use OpenJump, not QGIS, to display vectorizations of PostGIS rasters), the last development version of the GDAL driver supports overviews. So:
>
> 1) Find out which version of GDAL your QGIS installation is using (I have no idea how and it depends on your platform).
>
> 2) Make sure to load your rasters with overviews. There is unfortunately no way yet to build overviews for already loaded raster tables. You will have to reload with the right option.
>
> Pierre
>
> > -----Original Message-----
> > From: postgis-users-bounces at lists.osgeo.org<mailto:postgis-users-bounces at lists.osgeo.org> [mailto:postgis-users-
> > bounces at lists.osgeo.org<mailto:bounces at lists.osgeo.org>] On Behalf Of Ivan Santiago
> > Sent: Thursday, February 28, 2013 11:13 AM
> > To: PostGIS Users Discussion
> > Subject: [postgis-users] How can I optimize a raster in postgis 2 for display?
> >
> > Hello all:
> >
> >
> >
> > I ran rtpostgis.sql to enable postgis raster to our gis db.
> >
> > Thanks for the advice, Pierre.
> >
> >
> >
> > Now...
> >
> > Two rasters were added to the new database
> >
> > but I have problems viewing them in QGIS using the available plugin.
> >
> > plugin: Load Postgis Raster to QGIS Ver 0.0.5.4
> >
> >
> >
> > Original size erdas img files: 32mb, and 379mb
> >
> > raster_columns view:
> >
> >
> >
> >
> >
> > "gisogp";"ambiental_geologia_rasters_cuadrangulos";"o_6_ab_geol_20k";"rast
> > ";32161;6;-6;128;128;t;f;3;"{8BUI,8BUI,8BUI}";"{255,255,255}";"{f,f,f}";
> >
> > "gisogp";"ambiental_topografia_rasters_mdt";"o_6_ab_ele";"rast";32161;6;-
> > 6;128;128;t;f;1;"{32BF}";"";"{f}";
> > "gisogp";"ambiental_topografia_rasters_mdt";"ab_ele";"rast";32161;1;-
> > 1;128;128;t;f;1;"{32BF}";"";"{f}";
> > "gisogp";"ambiental_geologia_rasters_cuadrangulos";"ab_geol_20k";"rast";321
> > 61;1;-1;128;128;t;f;3;"{8BUI,8BUI,8BUI}";"{255,255,255}";"{f,f,f}";
> >
> >
> >
> > The main problem with these rasters is the extremely slow performance in QGIS.
> >
> > Maybe the problem lies in QGIS and not in postgis raster.
> >
> >
> >
> > gvSIG does not support postgis 2.0 as far as I know.
> >
> >
> >
> > Then, how can I optimize a raster in postgis 2 for display?
> >
> >
> >
> > OS: CentOS 5
> >
> > PG version: 2.0.2
> >
> > PSQL vers: 9.2
> >
> > ---------------------------
> >
> > Iván Santiago
> >
> > GIS Specialist
> >
> > Information Technologies
> >
> > Office of Management and Budget
> >
> > 787.725.9420 x 2378
> >
> > Calle Cruz 254
> >
> > PO Box 9023228
> >
> > San Juan, PR 00902-3228
> >
> >
> >
> > -----Original Message-----
> > From: postgis-users-bounces at lists.osgeo.org<mailto:postgis-users-bounces at lists.osgeo.org> [mailto:postgis-users-
> > bounces at lists.osgeo.org<mailto:bounces at lists.osgeo.org>] On Behalf Of Pierre Racine
> > Sent: Monday, February 25, 2013 5:51 PM
> > To: PostGIS Users Discussion
> > Subject: Re: [postgis-users] Postgis raster question: how to enable raster type in
> > 2.0
> >
> >
> >
> > rtpostgis.sql if for loading raster definition and function into the database. Not
> > to upgrade.
> >
> >
> >
> > http://postgis.net/docs/manual-
> > dev/postgis_installation.html#create_new_db_extensions
> > <http://postgis.net/docs/manual-
<http://postgis.net/docs/manual-%0b>> > dev/postgis_installation.html#create_new_db_extensions>
> >
> >
> >
> > http://postgis.net/docs/manual-dev/postgis_installation.html#upgrading
> > <http://postgis.net/docs/manual-dev/postgis_installation.html#upgrading>
> >
> >
> >
> > Pierre
> >
> >
> >
> > > -----Original Message-----
> >
> > > From: postgis-users-bounces at lists.osgeo.org<mailto:postgis-users-bounces at lists.osgeo.org> <mailto:postgis-users-
<mailto:postgis-users-%0b>> > bounces at lists.osgeo.org<mailto:bounces at lists.osgeo.org>> [mailto:postgis-users-
> >
> > > bounces at lists.osgeo.org<mailto:bounces at lists.osgeo.org> <mailto:bounces at lists.osgeo.org> ] On Behalf Of
> > Ivan Santiago
> >
> > > Sent: Monday, February 25, 2013 3:22 PM
> >
> > > To: PostGIS Users Discussion
> >
> > > Subject: Re: [postgis-users] Postgis raster question: how to enable
> >
> > > raster type in
> >
> > > 2.0
> >
> > >
> >
> > >
> >
> > >
> >
> > > Turns out it raster support was installed. I made another DB without
> >
> > > enabling postgis extension which includes raster.
> >
> > >
> >
> > >
> >
> > >
> >
> > > I hope that solves the problem. Still need to load a raster to see if this worked.
> >
> > >
> >
> > >
> >
> > >
> >
> > > Did you load rtpostgis.sql?
> >
> > >
> >
> > >
> >
> > >
> >
> > > It looks like rtpostgis.sql is for upgrading postgis. would you
> >
> > > recommend me to run it?
> >
> > >
> >
> > >
> >
> > >
> >
> > > we are on version 2.0.2
> >
> > >
> >
> > >
> >
> > >
> >
> > > ---------------------------
> >
> > > Iván Santiago
> >
> > > GIS Specialist
> >
> > > Information Technologies
> >
> > > Office of Management and Budget
> >
> > > <https://mail.ogp.gobierno.pr/exchweb/bin/redir.asp?URL=https://mail.o
<https://mail.ogp.gobierno.pr/exchweb/bin/redir.asp?URL=https://mail.o%0b>> >
> > > gp.go bierno.pr/exchweb/bin/redir.asp?URL=http://www.ogp.gobierno.pr/>
> >
> > > 787.725.9420 x 2378
> >
> > > Calle Cruz 254
> >
> > > PO Box 9023228
> >
> > > San Juan, PR 00902-3228
> >
> > >
> >
> > >
> >
> > >
> >
> > > From: postgis-users-bounces at lists.osgeo.org<mailto:postgis-users-bounces at lists.osgeo.org> <mailto:postgis-users-
<mailto:postgis-users-%0b>> > bounces at lists.osgeo.org<mailto:bounces at lists.osgeo.org>> [mailto:postgis-users-
> >
> > > bounces at lists.osgeo.org<mailto:bounces at lists.osgeo.org> <mailto:bounces at lists.osgeo.org> ] On Behalf Of
> > Pierre Racine
> >
> > > Sent: Monday, February 25, 2013 4:06 PM
> >
> > > To: PostGIS Users Discussion
> >
> > > Subject: Re: [postgis-users] Postgis raster question: how to enable
> >
> > > raster type in
> >
> > > 2.0
> >
> > >
> >
> > >
> >
> > >
> >
> > > Did you load rtpostgis.sql?
> >
> > >
> >
> > >
> >
> > >
> >
> > > Pierre
> >
> > >
> >
> > >
> >
> > >
> >
> > > From: postgis-users-bounces at lists.osgeo.org<mailto:postgis-users-bounces at lists.osgeo.org> <mailto:postgis-users-
<mailto:postgis-users-%0b>> > bounces at lists.osgeo.org<mailto:bounces at lists.osgeo.org>> [mailto:postgis-users-
> >
> > > bounces at lists.osgeo.org<mailto:bounces at lists.osgeo.org> <mailto:bounces at lists.osgeo.org> ] On Behalf Of
> > Ivan Santiago
> >
> > > Sent: Monday, February 25, 2013 1:42 PM
> >
> > > To: postgis-users at lists.osgeo.org<mailto:postgis-users at lists.osgeo.org> <mailto:postgis-users at lists.osgeo.org>
> >
> > > Subject: [postgis-users] Postgis raster question: how to enable raster
> >
> > > type in 2.0
> >
> > >
> >
> > >
> >
> > >
> >
> > > Hello all:
> >
> > >
> >
> > >
> >
> > >
> >
> > > We installed postgresql 9.2 and postgis 2.0 in a CentOS 5 virtual server.
> >
> > >
> >
> > >
> >
> > >
> >
> > > after running:
> >
> > >
> >
> > > select postgis_full_version();
> >
> > >
> >
> > >
> >
> > >
> >
> > > I get the following:
> >
> > >
> >
> > >
> >
> > >
> >
> > > "POSTGIS="2.0.2 r10789" GEOS="3.3.6-CAPI-1.7.6" PROJ="Rel. 4.8.0, 6
> >
> > >
> >
> > > March 2012" LIBXML="2.7.6" LIBJSON="UNKNOWN""
> >
> > >
> >
> > >
> >
> > >
> >
> > > Raster does not appear.
> >
> > >
> >
> > >
> >
> > >
> >
> > > How can I enable raster data type?
> >
> > >
> >
> > >
> >
> > >
> >
> > > Thanks in advance.
> >
> > >
> >
> > > ---------------------------
> >
> > > Iván Santiago
> >
> > > GIS Specialist
> >
> > > Information Technologies
> >
> > > Office of Management and Budget
> >
> > > <https://mail.ogp.gobierno.pr/exchweb/bin/redir.asp?URL=https://mail.o
<https://mail.ogp.gobierno.pr/exchweb/bin/redir.asp?URL=https://mail.o%0b>> >
> > > gp.go bierno.pr/exchweb/bin/redir.asp?URL=http://www.ogp.gobierno.pr/>
> >
> > > 787.725.9420 x 2378
> >
> > > Calle Cruz 254
> >
> > > PO Box 9023228
> >
> > > San Juan, PR 00902-3228
> >
> > >
> >
> > >
> >
> >
> >
> > _______________________________________________
> >
> > postgis-users mailing list
> >
> > postgis-users at lists.osgeo.org<mailto:postgis-users at lists.osgeo.org> <mailto:postgis-users at lists.osgeo.org>
> >
> > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
> > <http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org<mailto:postgis-users at lists.osgeo.org>
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20130301/e2d5b4c5/attachment.html>
More information about the postgis-users
mailing list