Benchmark (aka "comparing apples and oranges")

Introduction

Test configuration

• Intel Core i5 3.46 GHz
• 4 GB RAM
• Windows 7 64 bit (physical)
• Debian Squeeze 32 bit (virtual machine)

The sample dataset

Fig. 1 This one is the original set of Contour Lines to be clipped.
Fig. 2 This is the final result of the clipping operation. the Study Area boundary (polygon) is represented in red. the Contour Lines are represented in dark blue. the azure rectangle marks the detail area shown in the next figure.
Fig. 3 This is a magnified detail of the Study Area and Clipped Contour Lines. As you can easily notice there are lots of Contour Lines, and each Line has many vertexes. This one is a rather complex problem to be resolved: and it surely represents a thorough and very demanding test.

The Spatial SQL approach

Preparing the SQL script

--
-- SQL script performing the Clipping Contest
-- Target DBMS: SQLite with SpatiaLite extension
--

SELECT DateTime('now'), 'Loading the StudyArea SHP';
--
-- loading the StudyArea Shapefile into the DB
--
.loadshp StudyArea1MileBuffer StudyArea1MileBuffer CP1252 2260 Geometry Id POLYGON 2d no no_spatial_index

SELECT DateTime('now'), 'Loading the Contours SHP';
--
-- loading the Contour Shapefile into the DB
--
.loadshp Contours20Ft Contours20Ft CP1252 2260 Geometry Id LINESTRING 2d no with_spatial_index

SELECT DateTime('now'), 'Clipping ... please wait';
--
-- creating a new table containing the Clipped Contours
--
CREATE TABLE Contours20FtClipped AS
SELECT c.Id AS Id, c.OBJECTID AS OBJECTID, 
  c.Contour AS Contour, c.Shape_Leng AS Shape_Leng,
  CastToMultiLinestring(ST_Intersection(
      c.Geometry, a.Geometry)) AS Geometry
FROM Contours20Ft AS c, StudyArea1MileBuffer AS a
WHERE c.ROWID IN (
    SELECT ROWID 
    FROM SpatialIndex
    WHERE f_table_name = 'Contours20Ft' 
        AND search_frame = a.Geometry
);

SELECT DateTime('now'), 'deleting NULL Geometries';
--
-- clean up
-- deleting any NULL Geometry from Clipped Contours
--
DELETE FROM Contours20FtClipped 
WHERE Geometry IS NULL;

SELECT DateTime('now'), 'recovering a genuine Geometry column';
--
-- recovering a genuine Geometry Column (Clipped Contours)
---
SELECT RecoverGeometryColumn('Contours20FtClipped', 'Geometry', 2260, 'MULTILINESTRING', 2);

SELECT DateTime('now'), 'creating the Spatial Index';
--
-- creating a Spatial (Index supporting Clipped Contours)
--
SELECT CreateSpatialIndex('Contours20FtClipped', 'Geometry');


SELECT DateTime('now'), 'all done ... quitting';
--
-- end job
--

CREATE TABLE Contours20FtClipped AS
SELECT c.Id AS Id, c.OBJECTID AS OBJECTID, 
  c.Contour AS Contour, c.Shape_Leng AS Shape_Leng,
  CastToMultiLinestring(ST_Intersection(
      c.Geometry, a.Geometry)) AS Geometry
FROM Contours20Ft AS c, StudyArea1MileBuffer AS a
WHERE c.ROWID IN (
    SELECT ROWID 
    FROM SpatialIndex
    WHERE f_table_name = 'Contours20Ft' 
        AND search_frame = a.Geometry
);

DELETE FROM Contours20FtClipped 
WHERE Geometry IS NULL;

SELECT RecoverGeometryColumn('Contours20FtClipped', 'Geometry', 
    2260, 'MULTILINESTRING', 2);
SELECT CreateSpatialIndex('Contours20FtClipped', 'Geometry');

SELECT DateTime('now'), 'some message';

Bringing things to a head: running the test

# spatialite my_test.sqlite < test.sql

SpatiaLite version ..: 4.0.0-RC2	Supported Extensions:
	- 'VirtualShape'	[direct Shapefile access]
	- 'VirtualDbf'		[direct DBF access]
	- 'VirtualXL'		[direct XLS access]
	- 'VirtualText'		[direct CSV/TXT access]
	- 'VirtualNetwork'	[Dijkstra shortest path]
	- 'RTree'		[Spatial Index - R*Tree]
	- 'MbrCache'		[Spatial Index - MBR cache]
	- 'VirtualSpatialIndex'	[R*Tree metahandler]
	- 'VirtualFDO'		[FDO-OGR interoperability]
	- 'SpatiaLite'		[Spatial SQL - OGC]
PROJ.4 version ......: Rel. 4.7.1, 23 September 2009
GEOS version ........: 3.2.0-CAPI-1.6.0

2012-11-10 00:03:52|Loading the StudyArea SHP
2012-11-10 00:03:52|Loading the Contours SHP
2012-11-10 00:04:54|Clipping ... please wait
2012-11-10 00:07:31|deleting NULL Geometries
2012-11-10 00:07:33|recovering a genuine Geometry column
2012-11-10 00:07:34|creating the Spatial Index
2012-11-10 00:07:36|all done ... quitting
#

Final considerations

• The whole process took less than 4 minutes
• About 1 minute was spent loading the Shapefiles and building the supporting Spatial Index
• Less than 10 seconds were spent performing post-processing steps.
• The hard-core problem itself (generating a Clipped set of Contour Lines) took about 2 minutes and half.

• the CPUs used to actually run the tests are of different generations, and obviously have different intrinsic speeds.
• it's not at all clearly stated if the Desktop GIS apps permanently saved the Clipped Contour Lines somewhere or not. And it's not at all clear which kind of data storage they used (directly accessing the disk-based Shapefiles ? loading all the stuff in memory ? some other kind of properly structured disk-based storage, may be temporary and transient ?)
• the amount of available RAM is absolutely not relevant; this Contest could eventually benefit from a faster CPU, but the required memory footprint is minimal (just few hundredth MB in the SpatiaLite's case). Consuming huge amounts of RAM doesn't looks at all a wise solution, and isn't at all strictly required in this specific case. When such a condition emerges this will probably mean that some barbaric brute-force data access strategy has been badly adopted.
• adopting some kind of parallel processing will obviously introduce a noticeable performance boost (SpatiaLite is single threaded; so using a quad-core processor had absolutely no effect at all).

What are we measuring, really ?

• many GFOSS implementations (QGIS, PostGIS, SpatiaLite) are based on GEOS; so any measured time effectively represents the intrinsic efficiency of GEOS much more than the efficiency of the top-level application itself. If a difference exists between different GEOS-based apps, this is simply when the top-level apps badly introduces some further overhead thus reducing the overall efficiency.
• other GFOSS Java implementations (gvSIG, OpenJump, uDIG) are based on JTS; but GEOS simply is a transposition in C++ of the original Java-based JTS, the underlaying algorithms are exactly the same for both libraries. So, roughly speaking, all GFOSS applications (both C, C++ and Java) after all are based on the top of the same base-level algorithms. And not at all surprisingly all measured timings are quite exactly the same.
• with the remarkable exception of GRASS GIS; this one adopts an original approach of its own, being based on a conceptually different Geometry model (topology vs simple features). Anyway, in this case too timings are very similar to the others.
• IMHO it's a good demonstration that we have complessively reached the best possible efficiency for this kind of problems. And that openness combined with collaboration/competition between many different independent projects always leads to good, robust and efficient software.
• many proprietary implementations looks to be embarrassangly less efficient. A sad demonstration that a development policy based on closedness and secrecy very difficultly pays on long term periods.