[postgis-users] Small benchmark with PostGIS and MS SQL-Server Spatial: Any suggestions?

[Stefan Keller]

We're doing a small performance benchmark with PostGIS and Microsoft
SQL-Server Spatial.

We took the Tiger data from FOSS4G's "WMS Performance: Mapserver vs.
Geoserver" and are doing queries like shown below.

Any comments about these and/or suggestions about additional queries?

- Stefan

PostgreSQL syntax with curly brackets as table placeholders:

1. Loading data (SELECT * INTO...) and generating indices (CREATE
SPATIAL INDEX...)

2. Non-spatial selection query (inc. filling table):
   SELECT * INTO query2_result FROM {dataset} t
     WHERE t.Name = n;

3. Spatial query (inc. filling table):
   SELECT * FROM {dataset} t  INTO query3_result
     WHERE t.geom.STIntersects(@poly) = 1

4. Combining two spatial functions (inc. filling table):
   SELECT * FROM {polygons} t INTO query3_result
     WHERE STArea(t.geom)>x AND t.geom.STIntersects(@poly) = 1

5. Join of a linestring and a point table  (inc. filling table):
   SELECT s.* FROM {linestrings} t
     INNER JOIN {points} s ON STDistance(t.geom, s.geom) <= x INTO query5_result
     WHERE t.Type = ‘Autobahn’