[postgis-users] Small benchmark with PostGIS and MS SQL-Server Spatial: Adjustment

[rkrummen at hsr.ch]

Two weeks ago, my advisor Stefan Keller posted some queries about database benchmarking. We would like to thank you for the useful suggestions. Wherever possible we adapted our queries. Due to restrictions in time, data and the limited spatial functionality of MS SQL Server, we could not implement any proposed refinement, but we like to show you what we got now. Feel free to comment.

- Roland

1. Loading data and generating indices

SELECT * INTO {dataset} FROM {original dataset}
WHERE ST_Intersects(@geom, the_geom) ;

ALTER TABLE {dataset} ADD PRIMARY KEY (gid);

CREATE INDEX gidx ON {dataset} USING gist  (the_geom);

2. Non-spatial selection query

SELECT Count(*) FROM {dataset lines} l
WHERE l.roadflg='Y';

3. Spatial query I:

a)
SELECT Count(*) FROM {dataset points} p
WHERE ST_Intersects(@poly, p.the_geom) ;

b)
SELECT Count(*) FROM {dataset polygons} pg
WHERE ST_Intersects(@geom, pg.the_geom) ;

c)
SELECT Count(*) FROM {dataset lines} l
WHERE ST_Intersects(@geom, l.the_geom) ;

4. Spatial query II:

a)
SELECT Count(*) FROM {dataset points} p
WHERE ST_DWITHIN(@point, p.the_geom, x)

b)
SELECT Count(*) FROM {dataset polygons} pg
WHERE ST_DWITHIN(@point, pg.the_geom, x)

c)
SELECT Count(*) FROM {dataset lines} l
WHERE ST_DWITHIN(@point, l.the_geom, x)

5. Join of a linestring and a polygon table

SELECT SUM(ST_Length(pg.the_geom)) FROM {dataset lines} l
JOIN {dataset polygons} pg ON ST_DWITHIN(pg.the_geom, l.the_geom, 10)
WHERE l.railflg = 'Y'