[GIS] How to import raster (.asc) data to PostGIS

importlidarpostgispostgresqlraster

I am using Postgres 10.3 / Postgis 2.4.2, and I have thousands of ".asc" files (LiDAR DTM/DSM files) to import to the db. However, the format is something like:

ncols 1000
nrows 1000
xllcorner    297000
yllcorner    519000
cellsize     1
NODATA_value -9999
-3.616 -3.617 -3.618 etc

What is an easy way to import this data into PostgreSQL/PostGis?

Best Answer

The .asc file is a raster format know as ARC/INFO ASCII GRID (also known as ESRI ASCII raster) (see for example, here and here). It serves mostly the purpose of exchanging data among software/platforms.

As others have pointed out, raster2pgsql is a tool which can import .asc files to PostgreSQL. The syntax is:

raster2pgsql raster_options_go_here raster_file someschema.sometable > out.sql

An example which takes into account importing multiple .asc files is:

raster2pgsql -c -I -F -s 3857 -t 128x128  path_to_folder/*.asc someschema.sometable | psql -U postgres -d gisdb -h localhost -p 5432.

It creates a new table (-c) named 'sometable' within schema named 'somescheme' and imports all .asc files within 'path_to_folder'.

All files are assigned the Coordinate Reference System (-s) equal to 3857 (this is the EPSG code); and indexed (-I). Moreover, the command line adds a column in 'sometable' to store each .asc filename (-F). Last, -t tiles each raster with both width and height as 128 pixels. This is helpful to improve performance in polygon/raster overlay operations (see for example, Importing multiple .asc files into PostGIS violates check constraint. Why?).

Related Question