[GIS] Importing GeoTIFF into PostGIS without raster2pgsql

postgispostgis-2.0postgis-raster

I know that one can import GeoTIFF into PostGIS via raster2pgsql. I was wondering if there is any easy way (as a sequence of SQL statements) to do the same without resorting to the command line tool?

Essentially, I am interested in a PostgreSQL function that is the inverse of ST_AsTIFF, which given a pathname to a GeoTIFF, creates a pgraster table or a row in such a table.

The reason I ask is that I need such a function in a SQL stored procedure to import and process GeoTIFFs (not known in advance).

Best Answer

Take a look at ST_FromGDALRAster.

http://postgis.net/docs/manual-2.1/RT_ST_FromGDALRaster.html

Though that function assumes you imported the GTIFF as a bytea in PostgreSQL. If you want to keep the file out of the database and you have out-db enabled for your raster, you can use ST_AddBand. There is an example of an out-db tiff on the reference of this page:

http://postgis.net/docs/manual-2.1/RT_ST_AddBand.html

Related Solutions

[GIS] Write spatial operations to PostGIS from QGIS or ArcGIS

Wait a sec. How did we get into CIRCULARSTRINGs? If I understand correctly you have a MULTIPOLYGON layer, LowRez_HighHz, which you want to break up (multipart to single part) into POLYGONs. That's what ST_Dump can do.

-- Create a new table
CREATE TABLE LowRez_HIghHz_single (gid serial, Shape_Leng numeric, Shape_Area numeric);
-- Make it a spatial table
SELECT AddGeometryColumn('lowrez_highhz_single','geom',4283,'POLYGON',2);
-- Use ST_Dump to insert new geometries
INSERT INTO lowrez_highhz_single(geom) SELECT (ST_Dump(lh.geom)).geom FROM lowrez_highhz AS lh;

As for the "Out of Memory" error, I can't seem to understand what you're trying to do. It seems you are breaking up the MULTI features and recombining them into a collection in one select??

[GIS] Loading geotiffs into PostGIS using raster2pgsql

There is no limit on the size of a single image in PostGIS raster but you will be limited by other factors, such as how much memory you have in your computer. PostgreSQL does impose a 1GB limit on any one field PostgreSQL limits.

If you have the GDAL utility gdalinfo, could you run gdalinfo on one of your gtiff files and post the output here? Or if the data is publicly available (I assume the Ordinance Survey data is), can you provide a pointer to which tifs you are attempting to load so that I can test it?

gdalinfo MYGEOTIFF.tiff

The file size 71MB is tiny and should not be an issue. I currently load 10m elevation datasets (~500MB and 10812x10812 each) and 1m aerial imagery (~170MB and 5820x7575 each) without any issues.

Best Answer

Related Solutions

[GIS] Write spatial operations to PostGIS from QGIS or ArcGIS

[GIS] Loading geotiffs into PostGIS using raster2pgsql

Related Question