3

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?

Andre Silva
  • 10,259
  • 12
  • 54
  • 106
Randomize
  • 271
  • 1
  • 11
  • A PostgreSQL extension for storing point cloud (LIDAR) data. https://github.com/pgpointcloud/pointcloud – Mapperz May 02 '18 at 18:41
  • 1
    raster2pgsql should be able to read most .asc formats with metadata header (if raster data is what you want, as opposed to point clouds). also, you can run the command to batch insert via shell/terminal script. – geozelot May 02 '18 at 18:57
  • @Mapperz @ThingumaBob my final goal is intersecting this elevations with some polygons representing buildings using standard Postgis like ST_Intersects in WGS84. Is it better Raster or PointCloud? – Randomize May 02 '18 at 19:05
  • the pgpointcloud suite is mighty and fast, but not exactly user friendly. it's worth to get into it when you plan on, say, some serious 3D modeling. raster in PostGIS are somewhat more straight forward, albeit slow sometimes, but processing comes shipped with the extension. much depends on what you plan to do with those intersections I'd say. – geozelot May 02 '18 at 20:02
  • @ThingumaBob I have 40m buildings in a separate table to associate with their heights from Lidar (an average height of the Lidar points that fall into the single building shape) – Randomize May 02 '18 at 20:17
  • @ThingumaBob in case I go for pointcloud is there any way to import asc files into it? I cannot find anything in that sense on the project homepage. – Randomize May 02 '18 at 20:18
  • raster2pgsql is the way I have done it. Trivial to run in batch, fast, can index, set srid, etc. – John Powell May 03 '18 at 19:16

1 Answers1

5

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?).

Andre Silva
  • 10,259
  • 12
  • 54
  • 106