2

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?

  • A PostgreSQL extension for storing point cloud (LIDAR) data. github.com/pgpointcloud/pointcloud – Mapperz May 2 '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. – ThingumaBob May 2 '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 2 '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. – ThingumaBob May 2 '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 2 '18 at 20:17
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.