Category: PostGIS

GeoServer SQL views.

Posted by – Monday 2012-08-20

In this post we will see a simple example of how GeoServer’s SQL views [note 1] can be used to derive multiple layers from a single SQL table.

1. The arena.

I had a table in a PostGIS database that stores the geometries of all roads in the province of Lugo. Each road has a geometry and a set of attributes. One of these attributes is ‘owner’, which stores the road owner: a municipality, a province, a regional government or the central government.

From this table I needed to generate four WMS layers: roads owned by municipalities, roads owned by the the province, and so on.


Efficient LiDAR data bulk load in a PostGIS database.

Posted by – Sunday 2011-02-06

In this post we will see how to efficiently perform data bulk load in a spatial PostGIS database running in a Linux environment, coming the data from an airborne LiDAR survey.

Spatial databases can be used to store data collected in remote sensing Light Detection and Ranging[1] (LiDAR) surveys. LiDAR technology has many applications is forestry, where it is used in the generation Digital Terrain Models and Digital Surface Models[2], estimation of forest fuel variables…

In an airborne LiDAR survey, a LiDAR sensor emits laser pulses towards ground. Laser pulses are reflected when reaching obstacles as trees or the ground and, after a time elapse, echoes are received by the sensor, which records elapsed time and echo intensity. In addition, this kind of sensors have a kinematic Global Positioning System[3] (GPS) and an Inertial Measurement Unit[4] (IMU) that continuously registers the spatial position of the aircraft. Thus, high precision geo-referenced elevation and laser reflection intensity data is collected.

LiDAR surveys produce high volume of data. For instance, as of 2010 a commercial airborne LiDAR survey provided 12 points per square meter, which sums 120,000 points for an area as small as a hectare.

Thus, the problem of efficiently loading LiDAR data in a spatial database arises. The obvious approach, using INSERT queries, will work, but at the expense of consuming a lot of time. In sections 1, 2 and 3 we will see how to achieve efficient bulk data load in PostGIS by using the command COPY[5]. In section 4 some benchmarking is done, comparing the perfomance of data loading with COPY and INSERT.