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

[Sufficool, Stanley]

Read the EULA on MSSQL first.

5.  BENCHMARK TESTING.  You must obtain Microsoft's prior written approval to disclose to a third party the results of any benchmark test of the software.  However, this does not apply to the Microsoft .NET Framework (see below).


> -----Original Message-----
> From: postgis-users-bounces at postgis.refractions.net 
> [mailto:postgis-users-bounces at postgis.refractions.net] On 
> Behalf Of Paul Ramsey
> Sent: Monday, November 09, 2009 3:08 PM
> To: PostGIS Users Discussion
> Subject: Re: [postgis-users] Small benchmark with PostGIS and 
> MS SQL-ServerSpatial: Any suggestions?
> 
> 
> 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.
> _______________________________________________
> postgis-users mailing list postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>