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