[postgis-users] Quick Question re: raster2pgsql

[Mike Treglia]

Hi All,

I've had an import of a raster going on my computer for a few days (going on 3 days) now via rater2pgsql.  It was pretty comparable to a raster import that took something around ~12 hours, except I was building additional overview levels - my command line looks something like this:

raster2pgsql -s 2263 -d -C -t 128x128 -M -I -l 4,16,128,256 "LandCover.tif" base_rasters.landcover6in_2010 | psql.exe -h localhost -U postgres -d db -v ON_ERROR_STOP=1

(the overview levels I added were 128 and 256... maybe should've tested on a subset of the data... first. It is a large raster - 320,000 x 320,000 8 bit unsigned int pixels)

I'm getting to a point where I'll need to restart my pc for some administrative processes - thus, I was wondering - if I kill the raster2pgsql process before it complete, will I need to clean up those files somewhere? (at this point I haven't seen any of the files showing up in my database, so simply dropping the tables doesn't seem like it would work)

Any insights are greatly appreciated - thank you!

Mike

[Jorge Gustavo Rocha]

Hi Mike,

The raster and the overviews will be in your base_rasters schema. The
raster itself will be landcover6in_2010. Overviews will be
o_256_landcover6in_2010, o_128_landcover6in_2010 and so on.

If there are no tables there, you don't need to do anything. Just
restart the import again.

I would recommend bigger tiles. 128x128 tiles are quite small and you
will end up with too many rows. Try 1000x1000 tiles if you really don't
need 128x128 for any special processing.

raster2pgsql is slow. It will take a couple of hours.

Regards,

Jorge

Às 15:30 de 15/02/21, Mike Treglia escreveu:

> _______________________________________________
> postgis-users mailing list
> postgi...@lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
>

J. Gustavo
--
Jorge Gustavo Rocha
Departamento de Informática
Universidade do Minho
4710-057 Braga
Gabinete 3.29 (Piso 3)
Tel: +351 253604480
Fax: +351 253604471
Móvel: +351 910333888
skype: nabocudnosor
_______________________________________________
postgis-users mailing list
postgi...@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users

[Mike Treglia]

Thanks, Jorge, very much!

My main concern was that data was being written somewhere for the database postgres in the raster2pgsql process before it was established as a table that I'd see, but it sounds like that's not the case.

And thanks for the feedback on Tile Size... ages ago I had done some experimentation and that size ended up working well.  I have no recollection what potential issues were coming up with coarser tile sizes, but in case it helps for context, I ultimately use these data for spatial overlays with fairly small polygons across the landscape.

And I'll just note that comparable raster2pgsql code worked for a similar raster, only slightly smaller in dimensions, but as you note - it is slow and took a while (albeit finished within about half a day if I recall correctly).

Thanks again! As someone who's largely self taught with PostGIS & Postgresql, with support from books and blogs, I always appreciate the invaluable insights folks here have :-)

Mike