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
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
