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

[Sufficool, Stanley]

#2 will always be faster on SQL Server because it is not *entirely* ACID
compliant. It has a cache rather than a full table scan for row counts
IIRC.

> -----Original Message-----
> From: postgis-users-bounces at postgis.refractions.net 
> [mailto:postgis-users-bounces at postgis.refractions.net] On 
> Behalf Of rkrummen at hsr.ch
> Sent: Monday, November 23, 2009 9:46 AM
> To: postgis-users at postgis.refractions.net
> Subject: [postgis-users] Small benchmark with PostGIS and MS 
> SQL-ServerSpatial: Adjustment
> 
> 
> 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' 
> _______________________________________________
> postgis-users mailing list postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>