Category: databases

Exporting MS Access databases to SQLite.

Posted by – Saturday 2015-07-25

Sometimes you are given a database in MS Access (MDB) format. If your operating system is MS Windows and you have a MS Access license, working with this database should not be a issue.

If your operating system is Linux, the odds of working smoothly with such database are not good. The usual recipe for opening a MS Accesss database – actually, a MDB file – in a Linux machine is using LibreOffice Base plus the appropriate database driver. Usually this recipe will not work at the first attempt – and neither after many.

MS Access to SQLite

The alternative explored in this post is exporting the database from MS Access to SQLite. Both database technologies have in common the feature of being serverless. The output of this export process is a SQLite database which, like a MS Access database, is stored in a single file.

More…

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.

More…

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.

More…