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

[Paul Ramsey]

On Mon, Nov 9, 2009 at 11:55 AM, Stefan Keller <sfkeller at gmail.com> wrote:

> 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;

Don't do "SELECT INTO" as you'll muddy your query performance with
insert performance.

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

I try to do these kinds of queries with a "SELECT count(*)" so that
there is no penalty converting the rows returned into some kind of
output format.

> 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’

For PostGIS, you have to use ST_DWithin() to get an equivalent query
to the STDistance query above.

Try some full table joins

select sum(length(p.geom)) from polys p join lines l on
st_contains(p.geom, l.geom) where l.type = 'Autobahn'

Mix up the typologies, do st_contains/st_intersects of poly/poly,
poly/line, poly/poing
Mix up the geometry sizes, do the above on polygon tables of counties
and of tracts

Publish all the SQL and the data tables so people can critique your
work before you start publishing numbers.

P.