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

[Kevin Neufeld]

Right.  You probably won't be able to actually publish the numbers as 
Stanley pointed out, but for your own knowledge, I agree with Paul - 
round out your testing with
different spatial joins, like STContains, STTouches, STCrosses, etc. 
with different geometry types (point/point, point/line, point/poly, 
line/line, line/poly, poly/poly).  Test with different geometry sizes.

Test the spatial index by performing queries like "SELECT count(*) FROM 
mytable WHERE geom.Filter(@geom)" (the && equivalent operator in 
PostGIS) over large tables, each of a different geometry type.

Personally, I would limit the number of tests like you have in #4.  
Although interesting, you won't know how STArea and STIntersects 
comparatively perform using such a query (since they're both in the same 
query).  Rather, test raw computation speed by computing the buffer, 
area, centroid, boundary, isvalid, pointonsurface, etc (in different 
tests of course) of several large polygonal tables (one with small 
polygons, one with large polygons, one mixed, etc). 

Try STUnion-ing an entire table of polygons and again of lines.  If you 
really want to send shivers up your spine, I highly recommend you throw 
in some simple point-in-polygon tests (ie. 100's of thousands of points 
in a single large polygon of several 10's of thousands of vertices).  I 
know Lion King is an old show, but I can't help but think "Mufasa".

Cheers,
Kevin

Sufficool, Stanley wrote:
> 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
>>
>>     
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>