[postgis-users] Qgis + PostGis. Low operating speed.
Alessandro Pasotti
elpaso-lists at itopen.it
Wed Dec 14 07:45:01 PST 2022
Il 14/12/22 16:25, Regina Obe ha scritto:
>
> That’s a bummer. You know how long that has been? I recall it
> working when postgis raster access from QGIS was more or less a
> straight connection to GDAL. I think that changed in the past 2-3
> years or so.
>
Hi,
PG raster QGIS data provider was merged in 2019, it does use overviews
except when there is a SQL where condition in the layer definition, if
it doesn't it is a bug.
See: https://github.com/qgis/QGIS/issues/47815
Relevant parts:
https://github.com/qgis/QGIS/blob/master/src/providers/postgres/raster/qgspostgresrasterprovider.cpp#L2340
https://github.com/qgis/QGIS/blob/master/src/providers/postgres/raster/qgspostgresrasterprovider.cpp#L403
> *From:*postgis-users [mailto:postgis-users-bounces at lists.osgeo.org]
> *On Behalf Of *Alex German
> *Sent:* Wednesday, December 14, 2022 1:36 AM
> *To:* PostGIS Users Discussion <postgis-users at lists.osgeo.org>
> *Subject:* Re: [postgis-users] Qgis + PostGis. Low operating speed.
>
> Thanks, Regina! On the Qgis forum, I was told that overviews from
> PostGIS to Qgis do not work.
>
> ср, 14 дек. 2022 г. в 09:02, Regina Obe <lr at pcorp.us>:
>
> That comes as a big surprise to me. It’s been a while since I’ve
> used QGIS on raster, but last time I tried it was able to read the
> overview tables.
>
> I know there was a time not too long ago when QGIS was revising
> how it queries PostGIS raster. I forget what that revision was
> about, if it was just changing how it used GDAL to query PostGIS
> or getting rid of GDAL in some cases.
>
> So this might have caused an issue with its reading of overviews.
>
> I’ll try to test this out with latest QGIS in sometime this or
> next week to verify what the issue is here.
>
> Thanks,
>
> Regina
>
> *From:*postgis-users
> [mailto:postgis-users-bounces at lists.osgeo.org] *On Behalf Of *Alex
> German
> *Sent:* Tuesday, December 13, 2022 4:12 AM
> *To:* PostGIS Users Discussion <postgis-users at lists.osgeo.org>
> *Subject:* Re: [postgis-users] Qgis + PostGis. Low operating speed.
>
> QGis does not pick up overviews.
>
> Separately, overviews are loaded very quickly.
>
> I.e. if I load o_16_t6 in qgis, then the download speed is excellent.
>
> In the properties of the table, there are overviews.
>
> вт, 13 дек. 2022 г. в 12:29, Alex German <gr1047 at gmail.com>:
>
> image.png
>
> SRID in postgis 4326
>
> image.png
>
> SRID in QGis 4326
>
> вт, 13 дек. 2022 г. в 12:05, Bo Victor Thomsen
> <bo.victor.thomsen at gmail.com>:
>
> Is the rasters in same projection (CRS) as the QGIS project ?
>
> Med venlig hilsen / Best regards
>
>
>
> Bo Victor Thomsen
>
> Den 13-12-2022 kl. 07:50 skrev Alex German:
>
> I did everything as described in the documentation.
>
> raster2pgsql -I -C -e -Y -F -s 4326 -t 128x128 -l
> 2,4,8,16,32 "d:\Temp\060 tiff\res\t1.tif" testrastr.t1
> | psql.
>
> The download is very slow.
>
> https://youtu.be/LULngZIs4nc
>
> вт, 13 дек. 2022 г. в 09:45, Regina Obe <lr at pcorp.us>:
>
> PostGIS raster sadly doesn’t use the pyramids in
> the Tifs. Or if it does no one told me.
>
> You should build raster overviews using the
> raster2pgsql –l switch.
>
> https://postgis.net/docs/using_raster_dataman.html#RT_Raster_Loader
>
> Doc example is this which builds a level 2 and 4
> pyramid
>
> raster2pgsql -I -C -e -Y -F -s 26986 -t 128x128 -l 2,4 bostonaerials2008/*.jpg aerials.boston | psql -U postgres -d gisdb -h localhost -p 5432
>
> QGIS knows how to use the overviews built that way
> and should be much faster to view in QGIS
>
> raster2pgsql -s 4326 -t 256x256 -C -I -F
> "d:\Temp\060 tiff\res\OCG_t1.tif" OCG_t1 -R| psql.
>
> *From:*postgis-users
> [mailto:postgis-users-bounces at lists.osgeo.org] *On
> Behalf Of *Alex German
> *Sent:* Monday, December 12, 2022 11:34 PM
> *To:* PostGIS Users Discussion
> <postgis-users at lists.osgeo.org>
> *Subject:* [postgis-users] Qgis + PostGis. Low
> operating speed.
>
> Hello everyone!
>
> I have a GeoTIFF.
>
> I built the pyramids in an external file using GDAL
>
> "gdaladdo d:\Temp\060 tiff\res\t1.tif -r lanczos
> -ro 2 4 8 16 32 64 128 256 512.
>
> Uploaded to PostGIS using
>
> raster2pgsql -s 4326 -t 256x256 -C -I -F
> "d:\Temp\060 tiff\res\OCG_t1.tif" OCG_t1 -R| psql.
>
> The number of files is more than 1000.
>
> When trying to load tables in QGis, the loading is
> very slow.
>
> At the same time, downloading to QGis from files
> is fast. Question. What is not configured
> correctly and how to speed up the download to Qgis
> from PostGIS?
>
> --
>
> Alex German
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
>
>
> --
>
> Алексей Герман
>
> 8-905-86-00-416
>
> _______________________________________________
>
> postgis-users mailing list
>
> postgis-users at lists.osgeo.org
>
> https://lists.osgeo.org/mailman/listinfo/postgis-users
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
>
>
> --
>
> Алексей Герман
>
> 8-905-86-00-416
>
>
> --
>
> Алексей Герман
>
> 8-905-86-00-416
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
>
>
> --
>
> Алексей Герман
>
> 8-905-86-00-416
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
--
Alessandro Pasotti
w3: www.itopen.it
More information about the postgis-users
mailing list