Geometa Lab OST - Benutzerbeiträge [de-ch]

HSR Texas Geo Database Benchmark

2010-01-12T09:34:03Z

Stefan2: /* Datasets */

'''The HSR Texas Spatial Database Benchmark''' - A Proposal

Date of first proposal: December 21, 2009.
Status: Call for Comments and Call for Participation.

== Introduction ==

[[Bild:HSR_Spatial_Benchmark_Fig1.png|thumb|200px|right|Draft of the benchmark.]]

This is a proposal for a spatial database benchmark from the University of Applied Sciences Rapperswil (HSR). A study of existing database benchmarks revealed that there exists no information publicly available which compares spatial database systems regarding their performance. Spatial 'database management systems' (DMBS) typically form the persistence layer of a geographic information system (GIS).

Thus the Institute for Software at the University of Applied Sciences Rapperswil (HSR) decided to propose such a benchmark. This benchmark is being called 'The HSR Texas Spatial Database Benchmark' because it was defined from a HSR institute and because data comes from Texas USA.

The benchmark is based on a predefined set of queries. These queries consist of simple spatial queries, defined in the OpenGIS(tm) 'Simple Features Interface Standard (SFS)'. The queries are performed on different-sized data sets for monitoring the behavior on various loads as well as on different hardware systems.

In the following sections the methodology, the queries are explained and the used datasets are defined. This proposal concludes with a Call for Comments about the benchmark as well as a Call for Participation to apply and test this benchmark on existing DBMS software.

== Methodology ==

The benchmark follows the below defined rules of engagement.
* Each test runs three times in a row, the results of the third run are used for the comparison: this benchmark assumes full system caches ('hot' benchmark).
* Each test takes place on the same machine.
* All other DBMS are shut down while the tests are running.
* Each DBMS has the same data which comes from real world data sets (as indicated below).
* The coordinate reference system used is spherical (geographical).
* Each test uses bounding box or point variables which are the same for the respective systems in the test. These variables are chosen at random from within the subset space.

Any hardware used needs to be specified according to following points:
* System Type
* Model
* Processor
* RAM
* Hard drive
* Operating system

There are two variants of the test: "No tuning/initial" and "Tuned".
# The variant "No tuning/initial" is mandatory in order to make benchmarks more widely applicable. The respective default installation of one DBMS is used.
# The variant "Tuned" is optional and needs proper documentation of all tuned parameters and activities.

== Queries ==

The following queries are selected from the SQL functions defined by the SFS. The placeholder '{dataset}' will be substituted by the respective table names. The variable @bbox complies with randomly selected rectangular polygons in the geographical range of each data set, but which are the same for every system. The variable @point corresponds to a random point which is located in the area of the subsets. "geo" identifies the table column with geographical data. The following statements are given in pseudo SQL code.

Variable(s) '''@bbox''' (tbd.):
* 'Aligned with grid': <code>POLYGON ((-101.3135 32.026, -101.3135 29.974, -98.7865 29.974, -98.7865 32.026, -101.3135 32.026))</code>.

Variable(s) '''@point''' (tbd.):
* <code>POINT(-101.3135 32.026)</code>.

=== Query 1. Loading the data ===
Create tiered data subsets by sub-dividing with a given bounding box '@bbox', creating the indices.

SELECT * INTO {dataset}
FROM {original dataset}
WHERE ST_Intersects(@bbox, geo);
CREATE SPATIAL INDEX idx ON {dataset} ([geo]);

=== Query 2. A Non-spatial Selection: Count ===
Count all railroads.

SELECT Count(*)
FROM {dataset lines} l
WHERE l.roadflg='Y';

=== Query 3. Spatial Selection I: Intersect Point, Line and Polygons ===
Count all a) points, b) lines, c) polygons that intersect with a given bounding box '@bbox'.

a)
SELECT Count(*)
FROM {dataset points} p
WHERE ST_Intersects(@bbox, p.geo);

b)
SELECT Count(*)
FROM {dataset lines} l
WHERE ST_Intersects(@bbox, l.geo);

c)
SELECT Count(*)
FROM {dataset polygons} pg
WHERE ST_Intersects(@bbox, pg.geo);

=== Query 4. Spatial Selection II: Distance within ===
Count all a) points, b) lines, c) polygons that are within 1000 meters from a given point '@point'.

a)
SELECT COUNT(*)
FROM {dataset points} p
WHERE ST_Distance(@point, geo) <= 1000;

b)
SELECT COUNT(*)
FROM {dataset lines} l
WHERE ST_Distance(@point, geo) <= 1000;

c)
SELECT COUNT(*)
FROM {dataset polygons} pg
WHERE ST_Distance(@point, geo) <= 1000;

=== Query 5. Spatial Selection III: Intersect/Join Lines and Polygons ===
Count all railroads that intersect with a water area.

SELECT COUNT(*)
FROM {dataset lines} l, areawater_full pg
WHERE ST_Intersects(pg.geom, l.geom) = 1 AND l.railflg = 'Y';

== Datasets ==

[[Bild:HSR_Spatial_Benchmark_Fig2.png|thumb|400px|right|Visualization of the Point (left) and polygon (right) data set.]]

At the Free and Open Source Software for Geospatial Conference (FOSS4G) 2009, a 'Web Mapping Performance Shoot-out' was performed which compared the open source GIS software products GeoServer and MapServer ([http://2009.foss4g.org/presentations/#presentation_109]). There, PostgreSQL/PostGIS and Oracle have been tested too. Several sets from the TIGER shapefiles of Texas that can be downloaded from the U.S. Census Bureau where used as data basis. D

Download the data from the original site: ftp://ftp2.census.gov/geo/tiger/TIGER2008/ ([http://www.census.gov/geo/www/tiger/]). Note taht there are similar data sets around like [http://wiki.osgeo.org/wiki/Benchmarking_2009#Data here].

This data is proposed to use within this benchmark containing rivers, roads, railroads, Points-of-interest and water areas. The data originate from the following shape files:

{| class="prettytable"
|+ style="padding-bottom:1em;" | '''Table: Data sets from the TIGER shapefiles'''
|- bgcolor="#e0e0e0"
! Shape file name || gnis_names09 || edges_merge || areawater_merge
|- bgcolor="#eeeeee"
| Type || Point || Multilinestring || Multipolygon
|- bgcolor="#eeeeee"
| Number of records || 103,000 || Over 5 M || 380,000
|- bgcolor="#eeeeee"
| SRID || EPSG:4326 || EPSG:4326 || EPSG:4326
|- bgcolor="#eeeeee"
| Source || GNIS database || TIGER 2008 || TIGER 2008
|- bgcolor="#eeeeee"
| Description || All line elements (rivers and roads) from the TIGER 2008 dataset for the state of Texas. || All locations and Point of interest for the state of Texas. || The TIGER set of polygons describing water surface for the state of Texas.
|-
|}

These data sets are divided by a bounding-box procedure to subsets. Using these subsets the behavior of the respective DBMS can be observed in different-sized data sets. The figure to the right shows the gnis_names09 data set (left) and the areawater_merge data set (right).

It follows the definition of the (sub-)datasets (coordinates in [[WGS94]]):

=== Point data gnis_names09 ===

{| class="prettytable"
|+ style="padding-bottom:1em;" | '''Table: Shapefile gnis_names09'''
|- bgcolor="#e0e0e0"
! style="width:12em"| Subset || Number of records || Bounding box
|- bgcolor="#eeeeee"
| gnis_names_40000 ||style="text-align:right"| 40,744 || -103.208 33.565,-103.208 28.435,-96.891 28.435,-96.891 33.565
|- bgcolor="#eeeeee"
| gnis_names_60000 ||style="text-align:right"| 60,051 || -104.006 34.213,-104.006 27.787,-96.093 27.787,-96.093 34.213
|- bgcolor="#eeeeee"
| gnis_names_80000 ||style="text-align:right"| 80,174 || -104.904 34.942,-104.904 27.058,-95.195 27.058,-95.195 34.942
|- bgcolor="#eeeeee"
| gnis_names_100000 ||style="text-align:right"| 100,498 || -106.168 35.968,-106.168 26.032,-93.932 26.032,-93.932 35.968
|-
|}

=== Multilinestring data edges_merge ===

{| class="prettytable"
|+ style="padding-bottom:1em;" | '''Table: Shapefile edges_merge'''
|- bgcolor="#e0e0e0"
! style="width:12em"| Subset || Number of records || Bounding box
|- bgcolor="#eeeeee"
| edges_500000 ||style="text-align:right"| 500,282 || -102.169 32.720,-102.169 29.279,-97.930 29.279,-97.930 32.720
|- bgcolor="#eeeeee"
| edges_1000000 ||style="text-align:right"| 1,001,665 || -103.109 33.484,-103.109 28.516,-96.991 28.516,-96.991 33.484
|- bgcolor="#eeeeee"
| edges_1500000 ||style="text-align:right"| 1,498,936 || -103.518 33.816,-103.518 28.183,-96.581 28.183,-96.581 33.816
|- bgcolor="#eeeeee"
| edges_2000000 ||style="text-align:right"| 2,007,473 || -104.605 34.699,-104.605 27.301,-95.494 27.301,-95.494 34.699
|- bgcolor="#eeeeee"
| edges_2500000 ||style="text-align:right"| 2,498,941 || -106.041 35.865,-106.041 26.134,-94.058 26.134,-94.058 35.865
|-
|}

=== Multipolygon data areawater_merge ===

{| class="prettytable"
|+ style="padding-bottom:1em;" | '''Table: Shapefile areawater_merge'''
|- bgcolor="#e0e0e0"
! style="width:12em"| Subset || Number of records || Bounding box
|- bgcolor="#eeeeee"
| areawater_100000 ||style="text-align:right"| 100,860 || -102.789 33.224,-102.789 28.775,-97.310 28.775,-97.3102 33.224
|- bgcolor="#eeeeee"
| areawater_150000 ||style="text-align:right"| 150,655 || -103.261 33.608,-103.261 28.391,-96.838 28.391,-96.8380 33.608
|- bgcolor="#eeeeee"
| areawater_200000 ||style="text-align:right"| 200,814 || -103.674 33.943,-103.674 28.057,-96.425 28.057,-96.4257 33.943
|- bgcolor="#eeeeee"
| areawater_250000 ||style="text-align:right"| 249,685 || -104.272 34.429,-104.272 27.571,-95.827 27.571,-95.8272 34.429
|- bgcolor="#eeeeee"
| areawater_300000 ||style="text-align:right"| 299,965 || -104.691 34.769,-104.691 27.230,-95.408 27.230,-95.4083 34.769
|- bgcolor="#eeeeee"
| areawater_350000 ||style="text-align:right"| 350,449 || -105.536 35.455,-105.536 26.545,-94.563 26.545,-94.5637 35.455
|-
|}

== Call for Comments and Call for Participation ==

The 'HSR Texas Spatial Database Benchmark' was conducted and verified by a first time in December 2009 during a Master Seminar by the Institute for Software at HSR. Two database management systems software (DBMS), one commercially available (Microsoft SQL Server 2008 Spatial) an one under an Open Source license (PostgreSQL 8.4.1/PostGIS 1.4.0), have been chosen. The results are currently evaluated and will probably be published in 2010.

Two actions from researchers and volunteers may complement this research:
* Submit comments on the 'HSR Texas Spatial Database Benchmark' itself.
* Do further experiments on existing DBMS.

We are looking forward for a fruitful discussion!

== Feedback, Discussion and Contact ==

Please direct any comments either to the discussion page, to the [http://lists.osgeo.org/mailman/listinfo/benchmarking OSGEOs benchmarking mailing list] - or directly to [[Benutzer:Stefan|Prof. S. Keller from Institute for Software at HSR]].

== Weblinks ==
* [http://wiki.osgeo.org/wiki/Benchmarking_2009#Data GeoServer vs. MapServer Benchmark 2009]
* List of benchmarks and performance tests: http://delicious.com/sfkeller/database+benchmark

HSR Texas Geo Database Benchmark

2010-01-12T09:27:12Z

Stefan2: /* Queries */

'''The HSR Texas Spatial Database Benchmark''' - A Proposal

Date of first proposal: December 21, 2009.
Status: Call for Comments and Call for Participation.

== Introduction ==

[[Bild:HSR_Spatial_Benchmark_Fig1.png|thumb|200px|right|Draft of the benchmark.]]

This is a proposal for a spatial database benchmark from the University of Applied Sciences Rapperswil (HSR). A study of existing database benchmarks revealed that there exists no information publicly available which compares spatial database systems regarding their performance. Spatial 'database management systems' (DMBS) typically form the persistence layer of a geographic information system (GIS).

Thus the Institute for Software at the University of Applied Sciences Rapperswil (HSR) decided to propose such a benchmark. This benchmark is being called 'The HSR Texas Spatial Database Benchmark' because it was defined from a HSR institute and because data comes from Texas USA.

The benchmark is based on a predefined set of queries. These queries consist of simple spatial queries, defined in the OpenGIS(tm) 'Simple Features Interface Standard (SFS)'. The queries are performed on different-sized data sets for monitoring the behavior on various loads as well as on different hardware systems.

In the following sections the methodology, the queries are explained and the used datasets are defined. This proposal concludes with a Call for Comments about the benchmark as well as a Call for Participation to apply and test this benchmark on existing DBMS software.

== Methodology ==

The benchmark follows the below defined rules of engagement.
* Each test runs three times in a row, the results of the third run are used for the comparison: this benchmark assumes full system caches ('hot' benchmark).
* Each test takes place on the same machine.
* All other DBMS are shut down while the tests are running.
* Each DBMS has the same data which comes from real world data sets (as indicated below).
* The coordinate reference system used is spherical (geographical).
* Each test uses bounding box or point variables which are the same for the respective systems in the test. These variables are chosen at random from within the subset space.

Any hardware used needs to be specified according to following points:
* System Type
* Model
* Processor
* RAM
* Hard drive
* Operating system

There are two variants of the test: "No tuning/initial" and "Tuned".
# The variant "No tuning/initial" is mandatory in order to make benchmarks more widely applicable. The respective default installation of one DBMS is used.
# The variant "Tuned" is optional and needs proper documentation of all tuned parameters and activities.

== Queries ==

The following queries are selected from the SQL functions defined by the SFS. The placeholder '{dataset}' will be substituted by the respective table names. The variable @bbox complies with randomly selected rectangular polygons in the geographical range of each data set, but which are the same for every system. The variable @point corresponds to a random point which is located in the area of the subsets. "geo" identifies the table column with geographical data. The following statements are given in pseudo SQL code.

Variable(s) '''@bbox''' (tbd.):
* 'Aligned with grid': <code>POLYGON ((-101.3135 32.026, -101.3135 29.974, -98.7865 29.974, -98.7865 32.026, -101.3135 32.026))</code>.

Variable(s) '''@point''' (tbd.):
* <code>POINT(-101.3135 32.026)</code>.

=== Query 1. Loading the data ===
Create tiered data subsets by sub-dividing with a given bounding box '@bbox', creating the indices.

SELECT * INTO {dataset}
FROM {original dataset}
WHERE ST_Intersects(@bbox, geo);
CREATE SPATIAL INDEX idx ON {dataset} ([geo]);

=== Query 2. A Non-spatial Selection: Count ===
Count all railroads.

SELECT Count(*)
FROM {dataset lines} l
WHERE l.roadflg='Y';

=== Query 3. Spatial Selection I: Intersect Point, Line and Polygons ===
Count all a) points, b) lines, c) polygons that intersect with a given bounding box '@bbox'.

a)
SELECT Count(*)
FROM {dataset points} p
WHERE ST_Intersects(@bbox, p.geo);

b)
SELECT Count(*)
FROM {dataset lines} l
WHERE ST_Intersects(@bbox, l.geo);

c)
SELECT Count(*)
FROM {dataset polygons} pg
WHERE ST_Intersects(@bbox, pg.geo);

=== Query 4. Spatial Selection II: Distance within ===
Count all a) points, b) lines, c) polygons that are within 1000 meters from a given point '@point'.

a)
SELECT COUNT(*)
FROM {dataset points} p
WHERE ST_Distance(@point, geo) <= 1000;

b)
SELECT COUNT(*)
FROM {dataset lines} l
WHERE ST_Distance(@point, geo) <= 1000;

c)
SELECT COUNT(*)
FROM {dataset polygons} pg
WHERE ST_Distance(@point, geo) <= 1000;

=== Query 5. Spatial Selection III: Intersect/Join Lines and Polygons ===
Count all railroads that intersect with a water area.

SELECT COUNT(*)
FROM {dataset lines} l, areawater_full pg
WHERE ST_Intersects(pg.geom, l.geom) = 1 AND l.railflg = 'Y';

== Datasets ==

[[Bild:HSR_Spatial_Benchmark_Fig2.png|thumb|400px|right|Visualization of the Point (left) and polygon (right) data set.]]

At the Free and Open Source Software for Geospatial Conference (FOSS4G) 2009, a 'Web Mapping Performance Shoot-out' was performed which compared the open source GIS software products GeoServer and MapServer ([http://2009.foss4g.org/presentations/#presentation_109]). There, PostgreSQL/PostGIS and Oracle have been tested too. Several sets from the TIGER shapefiles of Texas that can be downloaded from the U.S. Census Bureau where used as data basis. Download the data from [http://wiki.osgeo.org/wiki/Benchmarking_2009#Data here]or from the original site ([http://www.census.gov/geo/www/tiger/]).

This data is proposed to use within this benchmark containing rivers, roads, railroads, Points-of-interest and water areas. The data originate from the following shape files:

{| class="prettytable"
|+ style="padding-bottom:1em;" | '''Table: Data sets from the TIGER shapefiles'''
|- bgcolor="#e0e0e0"
! Shape file name || gnis_names09 || edges_merge || areawater_merge
|- bgcolor="#eeeeee"
| Type || Point || Multilinestring || Multipolygon
|- bgcolor="#eeeeee"
| Number of records || 103,000 || Over 5 M || 380,000
|- bgcolor="#eeeeee"
| SRID || EPSG:4326 || EPSG:4326 || EPSG:4326
|- bgcolor="#eeeeee"
| Source || GNIS database || TIGER 2008 || TIGER 2008
|- bgcolor="#eeeeee"
| Description || All line elements (rivers and roads) from the TIGER 2008 dataset for the state of Texas. || All locations and Point of interest for the state of Texas. || The TIGER set of polygons describing water surface for the state of Texas.
|-
|}

These data sets are divided by a bounding-box procedure to subsets. Using these subsets the behavior of the respective DBMS can be observed in different-sized data sets. The figure to the right shows the gnis_names09 data set (left) and the areawater_merge data set (right).

It follows the definition of the (sub-)datasets (coordinates in [[WGS94]]):

=== Point data gnis_names09 ===

{| class="prettytable"
|+ style="padding-bottom:1em;" | '''Table: Shapefile gnis_names09'''
|- bgcolor="#e0e0e0"
! style="width:12em"| Subset || Number of records || Bounding box
|- bgcolor="#eeeeee"
| gnis_names_40000 ||style="text-align:right"| 40,744 || -103.208 33.565,-103.208 28.435,-96.891 28.435,-96.891 33.565
|- bgcolor="#eeeeee"
| gnis_names_60000 ||style="text-align:right"| 60,051 || -104.006 34.213,-104.006 27.787,-96.093 27.787,-96.093 34.213
|- bgcolor="#eeeeee"
| gnis_names_80000 ||style="text-align:right"| 80,174 || -104.904 34.942,-104.904 27.058,-95.195 27.058,-95.195 34.942
|- bgcolor="#eeeeee"
| gnis_names_100000 ||style="text-align:right"| 100,498 || -106.168 35.968,-106.168 26.032,-93.932 26.032,-93.932 35.968
|-
|}

=== Multilinestring data edges_merge ===

{| class="prettytable"
|+ style="padding-bottom:1em;" | '''Table: Shapefile edges_merge'''
|- bgcolor="#e0e0e0"
! style="width:12em"| Subset || Number of records || Bounding box
|- bgcolor="#eeeeee"
| edges_500000 ||style="text-align:right"| 500,282 || -102.169 32.720,-102.169 29.279,-97.930 29.279,-97.930 32.720
|- bgcolor="#eeeeee"
| edges_1000000 ||style="text-align:right"| 1,001,665 || -103.109 33.484,-103.109 28.516,-96.991 28.516,-96.991 33.484
|- bgcolor="#eeeeee"
| edges_1500000 ||style="text-align:right"| 1,498,936 || -103.518 33.816,-103.518 28.183,-96.581 28.183,-96.581 33.816
|- bgcolor="#eeeeee"
| edges_2000000 ||style="text-align:right"| 2,007,473 || -104.605 34.699,-104.605 27.301,-95.494 27.301,-95.494 34.699
|- bgcolor="#eeeeee"
| edges_2500000 ||style="text-align:right"| 2,498,941 || -106.041 35.865,-106.041 26.134,-94.058 26.134,-94.058 35.865
|-
|}

=== Multipolygon data areawater_merge ===

{| class="prettytable"
|+ style="padding-bottom:1em;" | '''Table: Shapefile areawater_merge'''
|- bgcolor="#e0e0e0"
! style="width:12em"| Subset || Number of records || Bounding box
|- bgcolor="#eeeeee"
| areawater_100000 ||style="text-align:right"| 100,860 || -102.789 33.224,-102.789 28.775,-97.310 28.775,-97.3102 33.224
|- bgcolor="#eeeeee"
| areawater_150000 ||style="text-align:right"| 150,655 || -103.261 33.608,-103.261 28.391,-96.838 28.391,-96.8380 33.608
|- bgcolor="#eeeeee"
| areawater_200000 ||style="text-align:right"| 200,814 || -103.674 33.943,-103.674 28.057,-96.425 28.057,-96.4257 33.943
|- bgcolor="#eeeeee"
| areawater_250000 ||style="text-align:right"| 249,685 || -104.272 34.429,-104.272 27.571,-95.827 27.571,-95.8272 34.429
|- bgcolor="#eeeeee"
| areawater_300000 ||style="text-align:right"| 299,965 || -104.691 34.769,-104.691 27.230,-95.408 27.230,-95.4083 34.769
|- bgcolor="#eeeeee"
| areawater_350000 ||style="text-align:right"| 350,449 || -105.536 35.455,-105.536 26.545,-94.563 26.545,-94.5637 35.455
|-
|}

== Call for Comments and Call for Participation ==

The 'HSR Texas Spatial Database Benchmark' was conducted and verified by a first time in December 2009 during a Master Seminar by the Institute for Software at HSR. Two database management systems software (DBMS), one commercially available (Microsoft SQL Server 2008 Spatial) an one under an Open Source license (PostgreSQL 8.4.1/PostGIS 1.4.0), have been chosen. The results are currently evaluated and will probably be published in 2010.

Two actions from researchers and volunteers may complement this research:
* Submit comments on the 'HSR Texas Spatial Database Benchmark' itself.
* Do further experiments on existing DBMS.

We are looking forward for a fruitful discussion!

== Feedback, Discussion and Contact ==

Please direct any comments either to the discussion page, to the [http://lists.osgeo.org/mailman/listinfo/benchmarking OSGEOs benchmarking mailing list] - or directly to [[Benutzer:Stefan|Prof. S. Keller from Institute for Software at HSR]].

== Weblinks ==
* [http://wiki.osgeo.org/wiki/Benchmarking_2009#Data GeoServer vs. MapServer Benchmark 2009]
* List of benchmarks and performance tests: http://delicious.com/sfkeller/database+benchmark

HSR Texas Geo Database Benchmark

2010-01-12T09:26:37Z

Stefan2: /* Queries */

'''The HSR Texas Spatial Database Benchmark''' - A Proposal

Date of first proposal: December 21, 2009.
Status: Call for Comments and Call for Participation.

== Introduction ==

[[Bild:HSR_Spatial_Benchmark_Fig1.png|thumb|200px|right|Draft of the benchmark.]]

This is a proposal for a spatial database benchmark from the University of Applied Sciences Rapperswil (HSR). A study of existing database benchmarks revealed that there exists no information publicly available which compares spatial database systems regarding their performance. Spatial 'database management systems' (DMBS) typically form the persistence layer of a geographic information system (GIS).

Thus the Institute for Software at the University of Applied Sciences Rapperswil (HSR) decided to propose such a benchmark. This benchmark is being called 'The HSR Texas Spatial Database Benchmark' because it was defined from a HSR institute and because data comes from Texas USA.

The benchmark is based on a predefined set of queries. These queries consist of simple spatial queries, defined in the OpenGIS(tm) 'Simple Features Interface Standard (SFS)'. The queries are performed on different-sized data sets for monitoring the behavior on various loads as well as on different hardware systems.

In the following sections the methodology, the queries are explained and the used datasets are defined. This proposal concludes with a Call for Comments about the benchmark as well as a Call for Participation to apply and test this benchmark on existing DBMS software.

== Methodology ==

The benchmark follows the below defined rules of engagement.
* Each test runs three times in a row, the results of the third run are used for the comparison: this benchmark assumes full system caches ('hot' benchmark).
* Each test takes place on the same machine.
* All other DBMS are shut down while the tests are running.
* Each DBMS has the same data which comes from real world data sets (as indicated below).
* The coordinate reference system used is spherical (geographical).
* Each test uses bounding box or point variables which are the same for the respective systems in the test. These variables are chosen at random from within the subset space.

Any hardware used needs to be specified according to following points:
* System Type
* Model
* Processor
* RAM
* Hard drive
* Operating system

There are two variants of the test: "No tuning/initial" and "Tuned".
# The variant "No tuning/initial" is mandatory in order to make benchmarks more widely applicable. The respective default installation of one DBMS is used.
# The variant "Tuned" is optional and needs proper documentation of all tuned parameters and activities.

== Queries ==

The following queries are selected from the SQL functions defined by the SFS. The placeholder '{dataset}' will be substituted by the respective table names. The variable @bbox complies with randomly selected rectangular polygons in the geographical range of each data set, but which are the same for every system. The variable @point corresponds to a random point which is located in the area of the subsets. "geo" identifies the table column with geographical data. The following statements are given in pseudo SQL code.

Variable(s) @bbox (tbd.):
* 'Aligned with grid': <code>POLYGON ((-101.3135 32.026, -101.3135 29.974, -98.7865 29.974, -98.7865 32.026, -101.3135 32.026))</code>.

Variable(s) @point (tbd.):
* <code>POINT(-101.3135 32.026)</code>.

=== Query 1. Loading the data ===
Create tiered data subsets by sub-dividing with a given bounding box '@bbox', creating the indices.

SELECT * INTO {dataset}
FROM {original dataset}
WHERE ST_Intersects(@bbox, geo);
CREATE SPATIAL INDEX idx ON {dataset} ([geo]);

=== Query 2. A Non-spatial Selection: Count ===
Count all railroads.

SELECT Count(*)
FROM {dataset lines} l
WHERE l.roadflg='Y';

=== Query 3. Spatial Selection I: Intersect Point, Line and Polygons ===
Count all a) points, b) lines, c) polygons that intersect with a given bounding box '@bbox'.

a)
SELECT Count(*)
FROM {dataset points} p
WHERE ST_Intersects(@bbox, p.geo);

b)
SELECT Count(*)
FROM {dataset lines} l
WHERE ST_Intersects(@bbox, l.geo);

c)
SELECT Count(*)
FROM {dataset polygons} pg
WHERE ST_Intersects(@bbox, pg.geo);

=== Query 4. Spatial Selection II: Distance within ===
Count all a) points, b) lines, c) polygons that are within 1000 meters from a given point '@point'.

a)
SELECT COUNT(*)
FROM {dataset points} p
WHERE ST_Distance(@point, geo) <= 1000;

b)
SELECT COUNT(*)
FROM {dataset lines} l
WHERE ST_Distance(@point, geo) <= 1000;

c)
SELECT COUNT(*)
FROM {dataset polygons} pg
WHERE ST_Distance(@point, geo) <= 1000;

=== Query 5. Spatial Selection III: Intersect/Join Lines and Polygons ===
Count all railroads that intersect with a water area.

SELECT COUNT(*)
FROM {dataset lines} l, areawater_full pg
WHERE ST_Intersects(pg.geom, l.geom) = 1 AND l.railflg = 'Y';

== Datasets ==

[[Bild:HSR_Spatial_Benchmark_Fig2.png|thumb|400px|right|Visualization of the Point (left) and polygon (right) data set.]]

At the Free and Open Source Software for Geospatial Conference (FOSS4G) 2009, a 'Web Mapping Performance Shoot-out' was performed which compared the open source GIS software products GeoServer and MapServer ([http://2009.foss4g.org/presentations/#presentation_109]). There, PostgreSQL/PostGIS and Oracle have been tested too. Several sets from the TIGER shapefiles of Texas that can be downloaded from the U.S. Census Bureau where used as data basis. Download the data from [http://wiki.osgeo.org/wiki/Benchmarking_2009#Data here]or from the original site ([http://www.census.gov/geo/www/tiger/]).

This data is proposed to use within this benchmark containing rivers, roads, railroads, Points-of-interest and water areas. The data originate from the following shape files:

{| class="prettytable"
|+ style="padding-bottom:1em;" | '''Table: Data sets from the TIGER shapefiles'''
|- bgcolor="#e0e0e0"
! Shape file name || gnis_names09 || edges_merge || areawater_merge
|- bgcolor="#eeeeee"
| Type || Point || Multilinestring || Multipolygon
|- bgcolor="#eeeeee"
| Number of records || 103,000 || Over 5 M || 380,000
|- bgcolor="#eeeeee"
| SRID || EPSG:4326 || EPSG:4326 || EPSG:4326
|- bgcolor="#eeeeee"
| Source || GNIS database || TIGER 2008 || TIGER 2008
|- bgcolor="#eeeeee"
| Description || All line elements (rivers and roads) from the TIGER 2008 dataset for the state of Texas. || All locations and Point of interest for the state of Texas. || The TIGER set of polygons describing water surface for the state of Texas.
|-
|}

These data sets are divided by a bounding-box procedure to subsets. Using these subsets the behavior of the respective DBMS can be observed in different-sized data sets. The figure to the right shows the gnis_names09 data set (left) and the areawater_merge data set (right).

It follows the definition of the (sub-)datasets (coordinates in [[WGS94]]):

=== Point data gnis_names09 ===

{| class="prettytable"
|+ style="padding-bottom:1em;" | '''Table: Shapefile gnis_names09'''
|- bgcolor="#e0e0e0"
! style="width:12em"| Subset || Number of records || Bounding box
|- bgcolor="#eeeeee"
| gnis_names_40000 ||style="text-align:right"| 40,744 || -103.208 33.565,-103.208 28.435,-96.891 28.435,-96.891 33.565
|- bgcolor="#eeeeee"
| gnis_names_60000 ||style="text-align:right"| 60,051 || -104.006 34.213,-104.006 27.787,-96.093 27.787,-96.093 34.213
|- bgcolor="#eeeeee"
| gnis_names_80000 ||style="text-align:right"| 80,174 || -104.904 34.942,-104.904 27.058,-95.195 27.058,-95.195 34.942
|- bgcolor="#eeeeee"
| gnis_names_100000 ||style="text-align:right"| 100,498 || -106.168 35.968,-106.168 26.032,-93.932 26.032,-93.932 35.968
|-
|}

=== Multilinestring data edges_merge ===

{| class="prettytable"
|+ style="padding-bottom:1em;" | '''Table: Shapefile edges_merge'''
|- bgcolor="#e0e0e0"
! style="width:12em"| Subset || Number of records || Bounding box
|- bgcolor="#eeeeee"
| edges_500000 ||style="text-align:right"| 500,282 || -102.169 32.720,-102.169 29.279,-97.930 29.279,-97.930 32.720
|- bgcolor="#eeeeee"
| edges_1000000 ||style="text-align:right"| 1,001,665 || -103.109 33.484,-103.109 28.516,-96.991 28.516,-96.991 33.484
|- bgcolor="#eeeeee"
| edges_1500000 ||style="text-align:right"| 1,498,936 || -103.518 33.816,-103.518 28.183,-96.581 28.183,-96.581 33.816
|- bgcolor="#eeeeee"
| edges_2000000 ||style="text-align:right"| 2,007,473 || -104.605 34.699,-104.605 27.301,-95.494 27.301,-95.494 34.699
|- bgcolor="#eeeeee"
| edges_2500000 ||style="text-align:right"| 2,498,941 || -106.041 35.865,-106.041 26.134,-94.058 26.134,-94.058 35.865
|-
|}

=== Multipolygon data areawater_merge ===

{| class="prettytable"
|+ style="padding-bottom:1em;" | '''Table: Shapefile areawater_merge'''
|- bgcolor="#e0e0e0"
! style="width:12em"| Subset || Number of records || Bounding box
|- bgcolor="#eeeeee"
| areawater_100000 ||style="text-align:right"| 100,860 || -102.789 33.224,-102.789 28.775,-97.310 28.775,-97.3102 33.224
|- bgcolor="#eeeeee"
| areawater_150000 ||style="text-align:right"| 150,655 || -103.261 33.608,-103.261 28.391,-96.838 28.391,-96.8380 33.608
|- bgcolor="#eeeeee"
| areawater_200000 ||style="text-align:right"| 200,814 || -103.674 33.943,-103.674 28.057,-96.425 28.057,-96.4257 33.943
|- bgcolor="#eeeeee"
| areawater_250000 ||style="text-align:right"| 249,685 || -104.272 34.429,-104.272 27.571,-95.827 27.571,-95.8272 34.429
|- bgcolor="#eeeeee"
| areawater_300000 ||style="text-align:right"| 299,965 || -104.691 34.769,-104.691 27.230,-95.408 27.230,-95.4083 34.769
|- bgcolor="#eeeeee"
| areawater_350000 ||style="text-align:right"| 350,449 || -105.536 35.455,-105.536 26.545,-94.563 26.545,-94.5637 35.455
|-
|}

== Call for Comments and Call for Participation ==

The 'HSR Texas Spatial Database Benchmark' was conducted and verified by a first time in December 2009 during a Master Seminar by the Institute for Software at HSR. Two database management systems software (DBMS), one commercially available (Microsoft SQL Server 2008 Spatial) an one under an Open Source license (PostgreSQL 8.4.1/PostGIS 1.4.0), have been chosen. The results are currently evaluated and will probably be published in 2010.

Two actions from researchers and volunteers may complement this research:
* Submit comments on the 'HSR Texas Spatial Database Benchmark' itself.
* Do further experiments on existing DBMS.

We are looking forward for a fruitful discussion!

== Feedback, Discussion and Contact ==

Please direct any comments either to the discussion page, to the [http://lists.osgeo.org/mailman/listinfo/benchmarking OSGEOs benchmarking mailing list] - or directly to [[Benutzer:Stefan|Prof. S. Keller from Institute for Software at HSR]].

== Weblinks ==
* [http://wiki.osgeo.org/wiki/Benchmarking_2009#Data GeoServer vs. MapServer Benchmark 2009]
* List of benchmarks and performance tests: http://delicious.com/sfkeller/database+benchmark

HSR Texas Geo Database Benchmark

2010-01-12T09:20:53Z

Stefan2: /* Queries */

'''The HSR Texas Spatial Database Benchmark''' - A Proposal

Date of first proposal: December 21, 2009.
Status: Call for Comments and Call for Participation.

== Introduction ==

[[Bild:HSR_Spatial_Benchmark_Fig1.png|thumb|200px|right|Draft of the benchmark.]]

This is a proposal for a spatial database benchmark from the University of Applied Sciences Rapperswil (HSR). A study of existing database benchmarks revealed that there exists no information publicly available which compares spatial database systems regarding their performance. Spatial 'database management systems' (DMBS) typically form the persistence layer of a geographic information system (GIS).

Thus the Institute for Software at the University of Applied Sciences Rapperswil (HSR) decided to propose such a benchmark. This benchmark is being called 'The HSR Texas Spatial Database Benchmark' because it was defined from a HSR institute and because data comes from Texas USA.

The benchmark is based on a predefined set of queries. These queries consist of simple spatial queries, defined in the OpenGIS(tm) 'Simple Features Interface Standard (SFS)'. The queries are performed on different-sized data sets for monitoring the behavior on various loads as well as on different hardware systems.

In the following sections the methodology, the queries are explained and the used datasets are defined. This proposal concludes with a Call for Comments about the benchmark as well as a Call for Participation to apply and test this benchmark on existing DBMS software.

== Methodology ==

The benchmark follows the below defined rules of engagement.
* Each test runs three times in a row, the results of the third run are used for the comparison: this benchmark assumes full system caches ('hot' benchmark).
* Each test takes place on the same machine.
* All other DBMS are shut down while the tests are running.
* Each DBMS has the same data which comes from real world data sets (as indicated below).
* The coordinate reference system used is spherical (geographical).
* Each test uses bounding box or point variables which are the same for the respective systems in the test. These variables are chosen at random from within the subset space.

Any hardware used needs to be specified according to following points:
* System Type
* Model
* Processor
* RAM
* Hard drive
* Operating system

There are two variants of the test: "No tuning/initial" and "Tuned".
# The variant "No tuning/initial" is mandatory in order to make benchmarks more widely applicable. The respective default installation of one DBMS is used.
# The variant "Tuned" is optional and needs proper documentation of all tuned parameters and activities.

== Queries ==

The following queries are selected from the SQL functions defined by the SFS. The placeholder '{dataset}' will be substituted by the respective table names. The variable @bbox complies with randomly selected rectangular polygons in the geographical range of each data set, but which are the same for every system. The variable @point corresponds to a random point which is located in the area of the subsets. "geo" identifies the table column with geographical data. The following statements are given in pseudo SQL code.

* Variable @bbox (tbd.): <code>POLYGON ((-101.3135 32.026, -101.3135 29.974, -98.7865 29.974, -98.7865 32.026, -101.3135 32.026))</code>.
* Variable @point (tbd.): <code>POINT(...)</code>.

=== Query 1. Loading the data ===
Create tiered data subsets by sub-dividing with a given bounding box '@bbox', creating the indices.

SELECT * INTO {dataset}
FROM {original dataset}
WHERE ST_Intersects(@bbox, geo);
CREATE SPATIAL INDEX idx ON {dataset} ([geo]);

=== Query 2. A Non-spatial Selection: Count ===
Count all railroads.

SELECT Count(*)
FROM {dataset lines} l
WHERE l.roadflg='Y';

=== Query 3. Spatial Selection I: Intersect Point, Line and Polygons ===
Count all a) points, b) lines, c) polygons that intersect with a given bounding box '@bbox'.

a)
SELECT Count(*)
FROM {dataset points} p
WHERE ST_Intersects(@bbox, p.geo);

b)
SELECT Count(*)
FROM {dataset lines} l
WHERE ST_Intersects(@bbox, l.geo);

c)
SELECT Count(*)
FROM {dataset polygons} pg
WHERE ST_Intersects(@bbox, pg.geo);

=== Query 4. Spatial Selection II: Distance within ===
Count all a) points, b) lines, c) polygons that are within 1000 meters from a given point '@point'.

a)
SELECT COUNT(*)
FROM {dataset points} p
WHERE ST_Distance(@point, geo) <= 1000;

b)
SELECT COUNT(*)
FROM {dataset lines} l
WHERE ST_Distance(@point, geo) <= 1000;

c)
SELECT COUNT(*)
FROM {dataset polygons} pg
WHERE ST_Distance(@point, geo) <= 1000;

=== Query 5. Spatial Selection III: Intersect/Join Lines and Polygons ===
Count all railroads that intersect with a water area.

SELECT COUNT(*)
FROM {dataset lines} l, areawater_full pg
WHERE ST_Intersects(pg.geom, l.geom) = 1 AND l.railflg = 'Y';

== Datasets ==

[[Bild:HSR_Spatial_Benchmark_Fig2.png|thumb|400px|right|Visualization of the Point (left) and polygon (right) data set.]]

At the Free and Open Source Software for Geospatial Conference (FOSS4G) 2009, a 'Web Mapping Performance Shoot-out' was performed which compared the open source GIS software products GeoServer and MapServer ([http://2009.foss4g.org/presentations/#presentation_109]). There, PostgreSQL/PostGIS and Oracle have been tested too. Several sets from the TIGER shapefiles of Texas that can be downloaded from the U.S. Census Bureau where used as data basis. Download the data from [http://wiki.osgeo.org/wiki/Benchmarking_2009#Data here]or from the original site ([http://www.census.gov/geo/www/tiger/]).

This data is proposed to use within this benchmark containing rivers, roads, railroads, Points-of-interest and water areas. The data originate from the following shape files:

{| class="prettytable"
|+ style="padding-bottom:1em;" | '''Table: Data sets from the TIGER shapefiles'''
|- bgcolor="#e0e0e0"
! Shape file name || gnis_names09 || edges_merge || areawater_merge
|- bgcolor="#eeeeee"
| Type || Point || Multilinestring || Multipolygon
|- bgcolor="#eeeeee"
| Number of records || 103,000 || Over 5 M || 380,000
|- bgcolor="#eeeeee"
| SRID || EPSG:4326 || EPSG:4326 || EPSG:4326
|- bgcolor="#eeeeee"
| Source || GNIS database || TIGER 2008 || TIGER 2008
|- bgcolor="#eeeeee"
| Description || All line elements (rivers and roads) from the TIGER 2008 dataset for the state of Texas. || All locations and Point of interest for the state of Texas. || The TIGER set of polygons describing water surface for the state of Texas.
|-
|}

These data sets are divided by a bounding-box procedure to subsets. Using these subsets the behavior of the respective DBMS can be observed in different-sized data sets. The figure to the right shows the gnis_names09 data set (left) and the areawater_merge data set (right).

It follows the definition of the (sub-)datasets (coordinates in [[WGS94]]):

=== Point data gnis_names09 ===

{| class="prettytable"
|+ style="padding-bottom:1em;" | '''Table: Shapefile gnis_names09'''
|- bgcolor="#e0e0e0"
! style="width:12em"| Subset || Number of records || Bounding box
|- bgcolor="#eeeeee"
| gnis_names_40000 ||style="text-align:right"| 40,744 || -103.208 33.565,-103.208 28.435,-96.891 28.435,-96.891 33.565
|- bgcolor="#eeeeee"
| gnis_names_60000 ||style="text-align:right"| 60,051 || -104.006 34.213,-104.006 27.787,-96.093 27.787,-96.093 34.213
|- bgcolor="#eeeeee"
| gnis_names_80000 ||style="text-align:right"| 80,174 || -104.904 34.942,-104.904 27.058,-95.195 27.058,-95.195 34.942
|- bgcolor="#eeeeee"
| gnis_names_100000 ||style="text-align:right"| 100,498 || -106.168 35.968,-106.168 26.032,-93.932 26.032,-93.932 35.968
|-
|}

=== Multilinestring data edges_merge ===

{| class="prettytable"
|+ style="padding-bottom:1em;" | '''Table: Shapefile edges_merge'''
|- bgcolor="#e0e0e0"
! style="width:12em"| Subset || Number of records || Bounding box
|- bgcolor="#eeeeee"
| edges_500000 ||style="text-align:right"| 500,282 || -102.169 32.720,-102.169 29.279,-97.930 29.279,-97.930 32.720
|- bgcolor="#eeeeee"
| edges_1000000 ||style="text-align:right"| 1,001,665 || -103.109 33.484,-103.109 28.516,-96.991 28.516,-96.991 33.484
|- bgcolor="#eeeeee"
| edges_1500000 ||style="text-align:right"| 1,498,936 || -103.518 33.816,-103.518 28.183,-96.581 28.183,-96.581 33.816
|- bgcolor="#eeeeee"
| edges_2000000 ||style="text-align:right"| 2,007,473 || -104.605 34.699,-104.605 27.301,-95.494 27.301,-95.494 34.699
|- bgcolor="#eeeeee"
| edges_2500000 ||style="text-align:right"| 2,498,941 || -106.041 35.865,-106.041 26.134,-94.058 26.134,-94.058 35.865
|-
|}

=== Multipolygon data areawater_merge ===

{| class="prettytable"
|+ style="padding-bottom:1em;" | '''Table: Shapefile areawater_merge'''
|- bgcolor="#e0e0e0"
! style="width:12em"| Subset || Number of records || Bounding box
|- bgcolor="#eeeeee"
| areawater_100000 ||style="text-align:right"| 100,860 || -102.789 33.224,-102.789 28.775,-97.310 28.775,-97.3102 33.224
|- bgcolor="#eeeeee"
| areawater_150000 ||style="text-align:right"| 150,655 || -103.261 33.608,-103.261 28.391,-96.838 28.391,-96.8380 33.608
|- bgcolor="#eeeeee"
| areawater_200000 ||style="text-align:right"| 200,814 || -103.674 33.943,-103.674 28.057,-96.425 28.057,-96.4257 33.943
|- bgcolor="#eeeeee"
| areawater_250000 ||style="text-align:right"| 249,685 || -104.272 34.429,-104.272 27.571,-95.827 27.571,-95.8272 34.429
|- bgcolor="#eeeeee"
| areawater_300000 ||style="text-align:right"| 299,965 || -104.691 34.769,-104.691 27.230,-95.408 27.230,-95.4083 34.769
|- bgcolor="#eeeeee"
| areawater_350000 ||style="text-align:right"| 350,449 || -105.536 35.455,-105.536 26.545,-94.563 26.545,-94.5637 35.455
|-
|}

== Call for Comments and Call for Participation ==

The 'HSR Texas Spatial Database Benchmark' was conducted and verified by a first time in December 2009 during a Master Seminar by the Institute for Software at HSR. Two database management systems software (DBMS), one commercially available (Microsoft SQL Server 2008 Spatial) an one under an Open Source license (PostgreSQL 8.4.1/PostGIS 1.4.0), have been chosen. The results are currently evaluated and will probably be published in 2010.

Two actions from researchers and volunteers may complement this research:
* Submit comments on the 'HSR Texas Spatial Database Benchmark' itself.
* Do further experiments on existing DBMS.

We are looking forward for a fruitful discussion!

== Feedback, Discussion and Contact ==

Please direct any comments either to the discussion page, to the [http://lists.osgeo.org/mailman/listinfo/benchmarking OSGEOs benchmarking mailing list] - or directly to [[Benutzer:Stefan|Prof. S. Keller from Institute for Software at HSR]].

== Weblinks ==
* [http://wiki.osgeo.org/wiki/Benchmarking_2009#Data GeoServer vs. MapServer Benchmark 2009]
* List of benchmarks and performance tests: http://delicious.com/sfkeller/database+benchmark

HSR Texas Geo Database Benchmark

2010-01-12T09:12:31Z

Stefan2: /* Queries */

'''The HSR Texas Spatial Database Benchmark''' - A Proposal

Date of first proposal: December 21, 2009.
Status: Call for Comments and Call for Participation.

== Introduction ==

[[Bild:HSR_Spatial_Benchmark_Fig1.png|thumb|200px|right|Draft of the benchmark.]]

This is a proposal for a spatial database benchmark from the University of Applied Sciences Rapperswil (HSR). A study of existing database benchmarks revealed that there exists no information publicly available which compares spatial database systems regarding their performance. Spatial 'database management systems' (DMBS) typically form the persistence layer of a geographic information system (GIS).

Thus the Institute for Software at the University of Applied Sciences Rapperswil (HSR) decided to propose such a benchmark. This benchmark is being called 'The HSR Texas Spatial Database Benchmark' because it was defined from a HSR institute and because data comes from Texas USA.

The benchmark is based on a predefined set of queries. These queries consist of simple spatial queries, defined in the OpenGIS(tm) 'Simple Features Interface Standard (SFS)'. The queries are performed on different-sized data sets for monitoring the behavior on various loads as well as on different hardware systems.

In the following sections the methodology, the queries are explained and the used datasets are defined. This proposal concludes with a Call for Comments about the benchmark as well as a Call for Participation to apply and test this benchmark on existing DBMS software.

== Methodology ==

The benchmark follows the below defined rules of engagement.
* Each test runs three times in a row, the results of the third run are used for the comparison: this benchmark assumes full system caches ('hot' benchmark).
* Each test takes place on the same machine.
* All other DBMS are shut down while the tests are running.
* Each DBMS has the same data which comes from real world data sets (as indicated below).
* The coordinate reference system used is spherical (geographical).
* Each test uses bounding box or point variables which are the same for the respective systems in the test. These variables are chosen at random from within the subset space.

Any hardware used needs to be specified according to following points:
* System Type
* Model
* Processor
* RAM
* Hard drive
* Operating system

There are two variants of the test: "No tuning/initial" and "Tuned".
# The variant "No tuning/initial" is mandatory in order to make benchmarks more widely applicable. The respective default installation of one DBMS is used.
# The variant "Tuned" is optional and needs proper documentation of all tuned parameters and activities.

== Queries ==

The following queries are selected from the SQL functions defined by the SFS. The placeholder '{dataset}' will be substituted by the respective table names. The variable @bbox complies with randomly selected rectangular polygons in the geographical range of each data set, but which are the same for every system. The variable @point corresponds to a random point which is located in the area of the subsets. "geo" identifies the table column with geographical data. The following statements are given in pseudo SQL code.

* Variable @bbox (tbd.): <code>POLYGON(...)<code>.
* Variable @point (tbd.): <code>POINT(...)<code>.

=== Query 1. Loading the data ===
Create tiered data subsets by sub-dividing with a given bounding box '@bbox', creating the indices.

SELECT * INTO {dataset}
FROM {original dataset}
WHERE ST_Intersects(@bbox, geo);
CREATE SPATIAL INDEX idx ON {dataset} ([geo]);

=== Query 2. A Non-spatial Selection: Count ===
Count all railroads.

SELECT Count(*)
FROM {dataset lines} l
WHERE l.roadflg='Y';

=== Query 3. Spatial Selection I: Intersect Point, Line and Polygons ===
Count all a) points, b) lines, c) polygons that intersect with a given bounding box '@bbox'.

a)
SELECT Count(*)
FROM {dataset points} p
WHERE ST_Intersects(@bbox, p.geo);

b)
SELECT Count(*)
FROM {dataset lines} l
WHERE ST_Intersects(@bbox, l.geo);

c)
SELECT Count(*)
FROM {dataset polygons} pg
WHERE ST_Intersects(@bbox, pg.geo);

=== Query 4. Spatial Selection II: Distance within ===
Count all a) points, b) lines, c) polygons that are within 1000 meters from a given point '@point'.

a)
SELECT COUNT(*)
FROM {dataset points} p
WHERE ST_Distance(@point, geo) <= 1000;

b)
SELECT COUNT(*)
FROM {dataset lines} l
WHERE ST_Distance(@point, geo) <= 1000;

c)
SELECT COUNT(*)
FROM {dataset polygons} pg
WHERE ST_Distance(@point, geo) <= 1000;

=== Query 5. Spatial Selection III: Intersect/Join Lines and Polygons ===
Count all railroads that intersect with a water area.

SELECT COUNT(*)
FROM {dataset lines} l, areawater_full pg
WHERE ST_Intersects(pg.geom, l.geom) = 1 AND l.railflg = 'Y';

== Datasets ==

[[Bild:HSR_Spatial_Benchmark_Fig2.png|thumb|400px|right|Visualization of the Point (left) and polygon (right) data set.]]

At the Free and Open Source Software for Geospatial Conference (FOSS4G) 2009, a 'Web Mapping Performance Shoot-out' was performed which compared the open source GIS software products GeoServer and MapServer ([http://2009.foss4g.org/presentations/#presentation_109]). There, PostgreSQL/PostGIS and Oracle have been tested too. Several sets from the TIGER shapefiles of Texas that can be downloaded from the U.S. Census Bureau where used as data basis. Download the data from [http://wiki.osgeo.org/wiki/Benchmarking_2009#Data here]or from the original site ([http://www.census.gov/geo/www/tiger/]).

This data is proposed to use within this benchmark containing rivers, roads, railroads, Points-of-interest and water areas. The data originate from the following shape files:

{| class="prettytable"
|+ style="padding-bottom:1em;" | '''Table: Data sets from the TIGER shapefiles'''
|- bgcolor="#e0e0e0"
! Shape file name || gnis_names09 || edges_merge || areawater_merge
|- bgcolor="#eeeeee"
| Type || Point || Multilinestring || Multipolygon
|- bgcolor="#eeeeee"
| Number of records || 103,000 || Over 5 M || 380,000
|- bgcolor="#eeeeee"
| SRID || EPSG:4326 || EPSG:4326 || EPSG:4326
|- bgcolor="#eeeeee"
| Source || GNIS database || TIGER 2008 || TIGER 2008
|- bgcolor="#eeeeee"
| Description || All line elements (rivers and roads) from the TIGER 2008 dataset for the state of Texas. || All locations and Point of interest for the state of Texas. || The TIGER set of polygons describing water surface for the state of Texas.
|-
|}

These data sets are divided by a bounding-box procedure to subsets. Using these subsets the behavior of the respective DBMS can be observed in different-sized data sets. The figure to the right shows the gnis_names09 data set (left) and the areawater_merge data set (right).

It follows the definition of the (sub-)datasets (coordinates in [[WGS94]]):

=== Point data gnis_names09 ===

{| class="prettytable"
|+ style="padding-bottom:1em;" | '''Table: Shapefile gnis_names09'''
|- bgcolor="#e0e0e0"
! style="width:12em"| Subset || Number of records || Bounding box
|- bgcolor="#eeeeee"
| gnis_names_40000 ||style="text-align:right"| 40,744 || -103.208 33.565,-103.208 28.435,-96.891 28.435,-96.891 33.565
|- bgcolor="#eeeeee"
| gnis_names_60000 ||style="text-align:right"| 60,051 || -104.006 34.213,-104.006 27.787,-96.093 27.787,-96.093 34.213
|- bgcolor="#eeeeee"
| gnis_names_80000 ||style="text-align:right"| 80,174 || -104.904 34.942,-104.904 27.058,-95.195 27.058,-95.195 34.942
|- bgcolor="#eeeeee"
| gnis_names_100000 ||style="text-align:right"| 100,498 || -106.168 35.968,-106.168 26.032,-93.932 26.032,-93.932 35.968
|-
|}

=== Multilinestring data edges_merge ===

{| class="prettytable"
|+ style="padding-bottom:1em;" | '''Table: Shapefile edges_merge'''
|- bgcolor="#e0e0e0"
! style="width:12em"| Subset || Number of records || Bounding box
|- bgcolor="#eeeeee"
| edges_500000 ||style="text-align:right"| 500,282 || -102.169 32.720,-102.169 29.279,-97.930 29.279,-97.930 32.720
|- bgcolor="#eeeeee"
| edges_1000000 ||style="text-align:right"| 1,001,665 || -103.109 33.484,-103.109 28.516,-96.991 28.516,-96.991 33.484
|- bgcolor="#eeeeee"
| edges_1500000 ||style="text-align:right"| 1,498,936 || -103.518 33.816,-103.518 28.183,-96.581 28.183,-96.581 33.816
|- bgcolor="#eeeeee"
| edges_2000000 ||style="text-align:right"| 2,007,473 || -104.605 34.699,-104.605 27.301,-95.494 27.301,-95.494 34.699
|- bgcolor="#eeeeee"
| edges_2500000 ||style="text-align:right"| 2,498,941 || -106.041 35.865,-106.041 26.134,-94.058 26.134,-94.058 35.865
|-
|}

=== Multipolygon data areawater_merge ===

{| class="prettytable"
|+ style="padding-bottom:1em;" | '''Table: Shapefile areawater_merge'''
|- bgcolor="#e0e0e0"
! style="width:12em"| Subset || Number of records || Bounding box
|- bgcolor="#eeeeee"
| areawater_100000 ||style="text-align:right"| 100,860 || -102.789 33.224,-102.789 28.775,-97.310 28.775,-97.3102 33.224
|- bgcolor="#eeeeee"
| areawater_150000 ||style="text-align:right"| 150,655 || -103.261 33.608,-103.261 28.391,-96.838 28.391,-96.8380 33.608
|- bgcolor="#eeeeee"
| areawater_200000 ||style="text-align:right"| 200,814 || -103.674 33.943,-103.674 28.057,-96.425 28.057,-96.4257 33.943
|- bgcolor="#eeeeee"
| areawater_250000 ||style="text-align:right"| 249,685 || -104.272 34.429,-104.272 27.571,-95.827 27.571,-95.8272 34.429
|- bgcolor="#eeeeee"
| areawater_300000 ||style="text-align:right"| 299,965 || -104.691 34.769,-104.691 27.230,-95.408 27.230,-95.4083 34.769
|- bgcolor="#eeeeee"
| areawater_350000 ||style="text-align:right"| 350,449 || -105.536 35.455,-105.536 26.545,-94.563 26.545,-94.5637 35.455
|-
|}

== Call for Comments and Call for Participation ==

The 'HSR Texas Spatial Database Benchmark' was conducted and verified by a first time in December 2009 during a Master Seminar by the Institute for Software at HSR. Two database management systems software (DBMS), one commercially available (Microsoft SQL Server 2008 Spatial) an one under an Open Source license (PostgreSQL 8.4.1/PostGIS 1.4.0), have been chosen. The results are currently evaluated and will probably be published in 2010.

Two actions from researchers and volunteers may complement this research:
* Submit comments on the 'HSR Texas Spatial Database Benchmark' itself.
* Do further experiments on existing DBMS.

We are looking forward for a fruitful discussion!

== Feedback, Discussion and Contact ==

Please direct any comments either to the discussion page, to the [http://lists.osgeo.org/mailman/listinfo/benchmarking OSGEOs benchmarking mailing list] - or directly to [[Benutzer:Stefan|Prof. S. Keller from Institute for Software at HSR]].

== Weblinks ==
* [http://wiki.osgeo.org/wiki/Benchmarking_2009#Data GeoServer vs. MapServer Benchmark 2009]
* List of benchmarks and performance tests: http://delicious.com/sfkeller/database+benchmark

HSR Texas Geo Database Benchmark

2010-01-12T09:11:07Z

Stefan2: /* Queries */

'''The HSR Texas Spatial Database Benchmark''' - A Proposal

Date of first proposal: December 21, 2009.
Status: Call for Comments and Call for Participation.

== Introduction ==

[[Bild:HSR_Spatial_Benchmark_Fig1.png|thumb|200px|right|Draft of the benchmark.]]

This is a proposal for a spatial database benchmark from the University of Applied Sciences Rapperswil (HSR). A study of existing database benchmarks revealed that there exists no information publicly available which compares spatial database systems regarding their performance. Spatial 'database management systems' (DMBS) typically form the persistence layer of a geographic information system (GIS).

Thus the Institute for Software at the University of Applied Sciences Rapperswil (HSR) decided to propose such a benchmark. This benchmark is being called 'The HSR Texas Spatial Database Benchmark' because it was defined from a HSR institute and because data comes from Texas USA.

The benchmark is based on a predefined set of queries. These queries consist of simple spatial queries, defined in the OpenGIS(tm) 'Simple Features Interface Standard (SFS)'. The queries are performed on different-sized data sets for monitoring the behavior on various loads as well as on different hardware systems.

In the following sections the methodology, the queries are explained and the used datasets are defined. This proposal concludes with a Call for Comments about the benchmark as well as a Call for Participation to apply and test this benchmark on existing DBMS software.

== Methodology ==

The benchmark follows the below defined rules of engagement.
* Each test runs three times in a row, the results of the third run are used for the comparison: this benchmark assumes full system caches ('hot' benchmark).
* Each test takes place on the same machine.
* All other DBMS are shut down while the tests are running.
* Each DBMS has the same data which comes from real world data sets (as indicated below).
* The coordinate reference system used is spherical (geographical).
* Each test uses bounding box or point variables which are the same for the respective systems in the test. These variables are chosen at random from within the subset space.

Any hardware used needs to be specified according to following points:
* System Type
* Model
* Processor
* RAM
* Hard drive
* Operating system

There are two variants of the test: "No tuning/initial" and "Tuned".
# The variant "No tuning/initial" is mandatory in order to make benchmarks more widely applicable. The respective default installation of one DBMS is used.
# The variant "Tuned" is optional and needs proper documentation of all tuned parameters and activities.

== Queries ==

The following queries are selected from the SQL functions defined by the SFS. The placeholder '{dataset}' will be substituted by the respective table names. The variable @bbox complies with randomly selected rectangular polygons in the geographical range of each data set, but which are the same for every system. The variable @point corresponds to a random point which is located in the area of the subsets. "geo" identifies the table column with geographical data. The following statements are given in pseudo SQL code.

Variable @bbox (tbd.):
nnn.nnn
nnn.nnn

Variable @point (tbd.):
nnn.nnn
nnn.nnn

=== Query 1. Loading the data ===
Create tiered data subsets by sub-dividing with a given bounding box '@bbox', creating the indices.

SELECT * INTO {dataset}
FROM {original dataset}
WHERE ST_Intersects(@bbox, geo);
CREATE SPATIAL INDEX idx ON {dataset} ([geo]);

=== Query 2. A Non-spatial Selection: Count ===
Count all railroads.

SELECT Count(*)
FROM {dataset lines} l
WHERE l.roadflg='Y';

=== Query 3. Spatial Selection I: Intersect Point, Line and Polygons ===
Count all a) points, b) lines, c) polygons that intersect with a given bounding box '@bbox'.

a)
SELECT Count(*)
FROM {dataset points} p
WHERE ST_Intersects(@bbox, p.geo);

b)
SELECT Count(*)
FROM {dataset lines} l
WHERE ST_Intersects(@bbox, l.geo);

c)
SELECT Count(*)
FROM {dataset polygons} pg
WHERE ST_Intersects(@bbox, pg.geo);

=== Query 4. Spatial Selection II: Distance within ===
Count all a) points, b) lines, c) polygons that are within 1000 meters from a given point '@point'.

a)
SELECT COUNT(*)
FROM {dataset points} p
WHERE ST_Distance(@point, geo) <= 1000;

b)
SELECT COUNT(*)
FROM {dataset lines} l
WHERE ST_Distance(@point, geo) <= 1000;

c)
SELECT COUNT(*)
FROM {dataset polygons} pg
WHERE ST_Distance(@point, geo) <= 1000;

=== Query 5. Spatial Selection III: Intersect/Join Lines and Polygons ===
Count all railroads that intersect with a water area.

SELECT COUNT(*)
FROM {dataset lines} l, areawater_full pg
WHERE ST_Intersects(pg.geom, l.geom) = 1 AND l.railflg = 'Y';

== Datasets ==

[[Bild:HSR_Spatial_Benchmark_Fig2.png|thumb|400px|right|Visualization of the Point (left) and polygon (right) data set.]]

At the Free and Open Source Software for Geospatial Conference (FOSS4G) 2009, a 'Web Mapping Performance Shoot-out' was performed which compared the open source GIS software products GeoServer and MapServer ([http://2009.foss4g.org/presentations/#presentation_109]). There, PostgreSQL/PostGIS and Oracle have been tested too. Several sets from the TIGER shapefiles of Texas that can be downloaded from the U.S. Census Bureau where used as data basis. Download the data from [http://wiki.osgeo.org/wiki/Benchmarking_2009#Data here]or from the original site ([http://www.census.gov/geo/www/tiger/]).

This data is proposed to use within this benchmark containing rivers, roads, railroads, Points-of-interest and water areas. The data originate from the following shape files:

{| class="prettytable"
|+ style="padding-bottom:1em;" | '''Table: Data sets from the TIGER shapefiles'''
|- bgcolor="#e0e0e0"
! Shape file name || gnis_names09 || edges_merge || areawater_merge
|- bgcolor="#eeeeee"
| Type || Point || Multilinestring || Multipolygon
|- bgcolor="#eeeeee"
| Number of records || 103,000 || Over 5 M || 380,000
|- bgcolor="#eeeeee"
| SRID || EPSG:4326 || EPSG:4326 || EPSG:4326
|- bgcolor="#eeeeee"
| Source || GNIS database || TIGER 2008 || TIGER 2008
|- bgcolor="#eeeeee"
| Description || All line elements (rivers and roads) from the TIGER 2008 dataset for the state of Texas. || All locations and Point of interest for the state of Texas. || The TIGER set of polygons describing water surface for the state of Texas.
|-
|}

These data sets are divided by a bounding-box procedure to subsets. Using these subsets the behavior of the respective DBMS can be observed in different-sized data sets. The figure to the right shows the gnis_names09 data set (left) and the areawater_merge data set (right).

It follows the definition of the (sub-)datasets (coordinates in [[WGS94]]):

=== Point data gnis_names09 ===

{| class="prettytable"
|+ style="padding-bottom:1em;" | '''Table: Shapefile gnis_names09'''
|- bgcolor="#e0e0e0"
! style="width:12em"| Subset || Number of records || Bounding box
|- bgcolor="#eeeeee"
| gnis_names_40000 ||style="text-align:right"| 40,744 || -103.208 33.565,-103.208 28.435,-96.891 28.435,-96.891 33.565
|- bgcolor="#eeeeee"
| gnis_names_60000 ||style="text-align:right"| 60,051 || -104.006 34.213,-104.006 27.787,-96.093 27.787,-96.093 34.213
|- bgcolor="#eeeeee"
| gnis_names_80000 ||style="text-align:right"| 80,174 || -104.904 34.942,-104.904 27.058,-95.195 27.058,-95.195 34.942
|- bgcolor="#eeeeee"
| gnis_names_100000 ||style="text-align:right"| 100,498 || -106.168 35.968,-106.168 26.032,-93.932 26.032,-93.932 35.968
|-
|}

=== Multilinestring data edges_merge ===

{| class="prettytable"
|+ style="padding-bottom:1em;" | '''Table: Shapefile edges_merge'''
|- bgcolor="#e0e0e0"
! style="width:12em"| Subset || Number of records || Bounding box
|- bgcolor="#eeeeee"
| edges_500000 ||style="text-align:right"| 500,282 || -102.169 32.720,-102.169 29.279,-97.930 29.279,-97.930 32.720
|- bgcolor="#eeeeee"
| edges_1000000 ||style="text-align:right"| 1,001,665 || -103.109 33.484,-103.109 28.516,-96.991 28.516,-96.991 33.484
|- bgcolor="#eeeeee"
| edges_1500000 ||style="text-align:right"| 1,498,936 || -103.518 33.816,-103.518 28.183,-96.581 28.183,-96.581 33.816
|- bgcolor="#eeeeee"
| edges_2000000 ||style="text-align:right"| 2,007,473 || -104.605 34.699,-104.605 27.301,-95.494 27.301,-95.494 34.699
|- bgcolor="#eeeeee"
| edges_2500000 ||style="text-align:right"| 2,498,941 || -106.041 35.865,-106.041 26.134,-94.058 26.134,-94.058 35.865
|-
|}

=== Multipolygon data areawater_merge ===

{| class="prettytable"
|+ style="padding-bottom:1em;" | '''Table: Shapefile areawater_merge'''
|- bgcolor="#e0e0e0"
! style="width:12em"| Subset || Number of records || Bounding box
|- bgcolor="#eeeeee"
| areawater_100000 ||style="text-align:right"| 100,860 || -102.789 33.224,-102.789 28.775,-97.310 28.775,-97.3102 33.224
|- bgcolor="#eeeeee"
| areawater_150000 ||style="text-align:right"| 150,655 || -103.261 33.608,-103.261 28.391,-96.838 28.391,-96.8380 33.608
|- bgcolor="#eeeeee"
| areawater_200000 ||style="text-align:right"| 200,814 || -103.674 33.943,-103.674 28.057,-96.425 28.057,-96.4257 33.943
|- bgcolor="#eeeeee"
| areawater_250000 ||style="text-align:right"| 249,685 || -104.272 34.429,-104.272 27.571,-95.827 27.571,-95.8272 34.429
|- bgcolor="#eeeeee"
| areawater_300000 ||style="text-align:right"| 299,965 || -104.691 34.769,-104.691 27.230,-95.408 27.230,-95.4083 34.769
|- bgcolor="#eeeeee"
| areawater_350000 ||style="text-align:right"| 350,449 || -105.536 35.455,-105.536 26.545,-94.563 26.545,-94.5637 35.455
|-
|}

== Call for Comments and Call for Participation ==

The 'HSR Texas Spatial Database Benchmark' was conducted and verified by a first time in December 2009 during a Master Seminar by the Institute for Software at HSR. Two database management systems software (DBMS), one commercially available (Microsoft SQL Server 2008 Spatial) an one under an Open Source license (PostgreSQL 8.4.1/PostGIS 1.4.0), have been chosen. The results are currently evaluated and will probably be published in 2010.

Two actions from researchers and volunteers may complement this research:
* Submit comments on the 'HSR Texas Spatial Database Benchmark' itself.
* Do further experiments on existing DBMS.

We are looking forward for a fruitful discussion!

== Feedback, Discussion and Contact ==

Please direct any comments either to the discussion page, to the [http://lists.osgeo.org/mailman/listinfo/benchmarking OSGEOs benchmarking mailing list] - or directly to [[Benutzer:Stefan|Prof. S. Keller from Institute for Software at HSR]].

== Weblinks ==
* [http://wiki.osgeo.org/wiki/Benchmarking_2009#Data GeoServer vs. MapServer Benchmark 2009]
* List of benchmarks and performance tests: http://delicious.com/sfkeller/database+benchmark

HSR Texas Geo Database Benchmark

2010-01-12T09:05:05Z

Stefan2: /* Query 4. Spatial Selection II: Distance within */

'''The HSR Texas Spatial Database Benchmark''' - A Proposal

Date of first proposal: December 21, 2009.
Status: Call for Comments and Call for Participation.

== Introduction ==

[[Bild:HSR_Spatial_Benchmark_Fig1.png|thumb|200px|right|Draft of the benchmark.]]

This is a proposal for a spatial database benchmark from the University of Applied Sciences Rapperswil (HSR). A study of existing database benchmarks revealed that there exists no information publicly available which compares spatial database systems regarding their performance. Spatial 'database management systems' (DMBS) typically form the persistence layer of a geographic information system (GIS).

Thus the Institute for Software at the University of Applied Sciences Rapperswil (HSR) decided to propose such a benchmark. This benchmark is being called 'The HSR Texas Spatial Database Benchmark' because it was defined from a HSR institute and because data comes from Texas USA.

The benchmark is based on a predefined set of queries. These queries consist of simple spatial queries, defined in the OpenGIS(tm) 'Simple Features Interface Standard (SFS)'. The queries are performed on different-sized data sets for monitoring the behavior on various loads as well as on different hardware systems.

In the following sections the methodology, the queries are explained and the used datasets are defined. This proposal concludes with a Call for Comments about the benchmark as well as a Call for Participation to apply and test this benchmark on existing DBMS software.

== Methodology ==

The benchmark follows the below defined rules of engagement.
* Each test runs three times in a row, the results of the third run are used for the comparison: this benchmark assumes full system caches ('hot' benchmark).
* Each test takes place on the same machine.
* All other DBMS are shut down while the tests are running.
* Each DBMS has the same data which comes from real world data sets (as indicated below).
* The coordinate reference system used is spherical (geographical).
* Each test uses bounding box or point variables which are the same for the respective systems in the test. These variables are chosen at random from within the subset space.

Any hardware used needs to be specified according to following points:
* System Type
* Model
* Processor
* RAM
* Hard drive
* Operating system

There are two variants of the test: "No tuning/initial" and "Tuned".
# The variant "No tuning/initial" is mandatory in order to make benchmarks more widely applicable. The respective default installation of one DBMS is used.
# The variant "Tuned" is optional and needs proper documentation of all tuned parameters and activities.

== Queries ==

The following queries are selected from the SQL functions defined by the SFS. The placeholder '{dataset}' will be substituted by the respective table names. The variable @bbox complies with randomly selected rectangular polygons in the geographical range of each data set, but which are the same for every system. The variable @point corresponds to a random point which is located in the area of the subsets. "geo" identifies the table column with geographical data. The statements are given in pseudo SQL code.

=== Query 1. Loading the data ===
Create tiered data subsets by sub-dividing with a given bounding box '@bbox', creating the indices.

SELECT * INTO {dataset}
FROM {original dataset}
WHERE ST_Intersects(@bbox, geo);
CREATE SPATIAL INDEX idx ON {dataset} ([geo]);

=== Query 2. A Non-spatial Selection: Count ===
Count all railroads.

SELECT Count(*)
FROM {dataset lines} l
WHERE l.roadflg='Y';

=== Query 3. Spatial Selection I: Intersect Point, Line and Polygons ===
Count all a) points, b) lines, c) polygons that intersect with a given bounding box '@bbox'.

a)
SELECT Count(*)
FROM {dataset points} p
WHERE ST_Intersects(@bbox, p.geo);

b)
SELECT Count(*)
FROM {dataset lines} l
WHERE ST_Intersects(@bbox, l.geo);

c)
SELECT Count(*)
FROM {dataset polygons} pg
WHERE ST_Intersects(@bbox, pg.geo);

=== Query 4. Spatial Selection II: Distance within ===
Count all a) points, b) lines, c) polygons that are within 1000 meters from a given point '@point'.

a)
SELECT COUNT(*)
FROM {dataset points} p
WHERE ST_Distance(@point, geo) <= 1000;

b)
SELECT COUNT(*)
FROM {dataset lines} l
WHERE ST_Distance(@point, geo) <= 1000;

c)
SELECT COUNT(*)
FROM {dataset polygons} pg
WHERE ST_Distance(@point, geo) <= 1000;

=== Query 5. Spatial Selection III: Intersect/Join Lines and Polygons ===
Count all railroads that intersect with a water area.

SELECT COUNT(*)
FROM {dataset lines} l, areawater_full pg
WHERE ST_Intersects(pg.geom, l.geom) = 1 AND l.railflg = 'Y';

== Datasets ==

[[Bild:HSR_Spatial_Benchmark_Fig2.png|thumb|400px|right|Visualization of the Point (left) and polygon (right) data set.]]

At the Free and Open Source Software for Geospatial Conference (FOSS4G) 2009, a 'Web Mapping Performance Shoot-out' was performed which compared the open source GIS software products GeoServer and MapServer ([http://2009.foss4g.org/presentations/#presentation_109]). There, PostgreSQL/PostGIS and Oracle have been tested too. Several sets from the TIGER shapefiles of Texas that can be downloaded from the U.S. Census Bureau where used as data basis. Download the data from [http://wiki.osgeo.org/wiki/Benchmarking_2009#Data here]or from the original site ([http://www.census.gov/geo/www/tiger/]).

This data is proposed to use within this benchmark containing rivers, roads, railroads, Points-of-interest and water areas. The data originate from the following shape files:

{| class="prettytable"
|+ style="padding-bottom:1em;" | '''Table: Data sets from the TIGER shapefiles'''
|- bgcolor="#e0e0e0"
! Shape file name || gnis_names09 || edges_merge || areawater_merge
|- bgcolor="#eeeeee"
| Type || Point || Multilinestring || Multipolygon
|- bgcolor="#eeeeee"
| Number of records || 103,000 || Over 5 M || 380,000
|- bgcolor="#eeeeee"
| SRID || EPSG:4326 || EPSG:4326 || EPSG:4326
|- bgcolor="#eeeeee"
| Source || GNIS database || TIGER 2008 || TIGER 2008
|- bgcolor="#eeeeee"
| Description || All line elements (rivers and roads) from the TIGER 2008 dataset for the state of Texas. || All locations and Point of interest for the state of Texas. || The TIGER set of polygons describing water surface for the state of Texas.
|-
|}

These data sets are divided by a bounding-box procedure to subsets. Using these subsets the behavior of the respective DBMS can be observed in different-sized data sets. The figure to the right shows the gnis_names09 data set (left) and the areawater_merge data set (right).

It follows the definition of the (sub-)datasets (coordinates in [[WGS94]]):

=== Point data gnis_names09 ===

{| class="prettytable"
|+ style="padding-bottom:1em;" | '''Table: Shapefile gnis_names09'''
|- bgcolor="#e0e0e0"
! style="width:12em"| Subset || Number of records || Bounding box
|- bgcolor="#eeeeee"
| gnis_names_40000 ||style="text-align:right"| 40,744 || -103.208 33.565,-103.208 28.435,-96.891 28.435,-96.891 33.565
|- bgcolor="#eeeeee"
| gnis_names_60000 ||style="text-align:right"| 60,051 || -104.006 34.213,-104.006 27.787,-96.093 27.787,-96.093 34.213
|- bgcolor="#eeeeee"
| gnis_names_80000 ||style="text-align:right"| 80,174 || -104.904 34.942,-104.904 27.058,-95.195 27.058,-95.195 34.942
|- bgcolor="#eeeeee"
| gnis_names_100000 ||style="text-align:right"| 100,498 || -106.168 35.968,-106.168 26.032,-93.932 26.032,-93.932 35.968
|-
|}

=== Multilinestring data edges_merge ===

{| class="prettytable"
|+ style="padding-bottom:1em;" | '''Table: Shapefile edges_merge'''
|- bgcolor="#e0e0e0"
! style="width:12em"| Subset || Number of records || Bounding box
|- bgcolor="#eeeeee"
| edges_500000 ||style="text-align:right"| 500,282 || -102.169 32.720,-102.169 29.279,-97.930 29.279,-97.930 32.720
|- bgcolor="#eeeeee"
| edges_1000000 ||style="text-align:right"| 1,001,665 || -103.109 33.484,-103.109 28.516,-96.991 28.516,-96.991 33.484
|- bgcolor="#eeeeee"
| edges_1500000 ||style="text-align:right"| 1,498,936 || -103.518 33.816,-103.518 28.183,-96.581 28.183,-96.581 33.816
|- bgcolor="#eeeeee"
| edges_2000000 ||style="text-align:right"| 2,007,473 || -104.605 34.699,-104.605 27.301,-95.494 27.301,-95.494 34.699
|- bgcolor="#eeeeee"
| edges_2500000 ||style="text-align:right"| 2,498,941 || -106.041 35.865,-106.041 26.134,-94.058 26.134,-94.058 35.865
|-
|}

=== Multipolygon data areawater_merge ===

{| class="prettytable"
|+ style="padding-bottom:1em;" | '''Table: Shapefile areawater_merge'''
|- bgcolor="#e0e0e0"
! style="width:12em"| Subset || Number of records || Bounding box
|- bgcolor="#eeeeee"
| areawater_100000 ||style="text-align:right"| 100,860 || -102.789 33.224,-102.789 28.775,-97.310 28.775,-97.3102 33.224
|- bgcolor="#eeeeee"
| areawater_150000 ||style="text-align:right"| 150,655 || -103.261 33.608,-103.261 28.391,-96.838 28.391,-96.8380 33.608
|- bgcolor="#eeeeee"
| areawater_200000 ||style="text-align:right"| 200,814 || -103.674 33.943,-103.674 28.057,-96.425 28.057,-96.4257 33.943
|- bgcolor="#eeeeee"
| areawater_250000 ||style="text-align:right"| 249,685 || -104.272 34.429,-104.272 27.571,-95.827 27.571,-95.8272 34.429
|- bgcolor="#eeeeee"
| areawater_300000 ||style="text-align:right"| 299,965 || -104.691 34.769,-104.691 27.230,-95.408 27.230,-95.4083 34.769
|- bgcolor="#eeeeee"
| areawater_350000 ||style="text-align:right"| 350,449 || -105.536 35.455,-105.536 26.545,-94.563 26.545,-94.5637 35.455
|-
|}

== Call for Comments and Call for Participation ==

The 'HSR Texas Spatial Database Benchmark' was conducted and verified by a first time in December 2009 during a Master Seminar by the Institute for Software at HSR. Two database management systems software (DBMS), one commercially available (Microsoft SQL Server 2008 Spatial) an one under an Open Source license (PostgreSQL 8.4.1/PostGIS 1.4.0), have been chosen. The results are currently evaluated and will probably be published in 2010.

Two actions from researchers and volunteers may complement this research:
* Submit comments on the 'HSR Texas Spatial Database Benchmark' itself.
* Do further experiments on existing DBMS.

We are looking forward for a fruitful discussion!

== Feedback, Discussion and Contact ==

Please direct any comments either to the discussion page, to the [http://lists.osgeo.org/mailman/listinfo/benchmarking OSGEOs benchmarking mailing list] - or directly to [[Benutzer:Stefan|Prof. S. Keller from Institute for Software at HSR]].

== Weblinks ==
* [http://wiki.osgeo.org/wiki/Benchmarking_2009#Data GeoServer vs. MapServer Benchmark 2009]
* List of benchmarks and performance tests: http://delicious.com/sfkeller/database+benchmark

Diskussion:RainAlarm

2010-01-12T09:01:53Z

Stefan2: /* SB To Do */

== SB To Do ==

To Do App.:
* Erkennen von "No Data" ("Fehlende Daten beim Bundesamt") des Inputs (z.B. bei Betriebsausfall vom Monte Lama). Siehe z.B. die Abbildung rechts. [[Bild:Zppi_20090909_1310.png|thumb|200px|right| hier]]
* Ggf. Übernehmen der Klassifikation der Regen- und der Schneeintensität (auch wenn's nur ein mittlerer Wert pro Ellipse/'niederschlags-Zelle' ist)
* Filtern von kleinsten Pixelgruppen
* Erzeugen eines animierten GIFs, vom Web her zugänglich
* Anpassen des Response-Texts mit Datum/Zeit und (NEU, vgl. unten)
* Berücksichtigung von Grössenveränderung, Rotation/Richtungsänderung sowie Beschleunigung/Verlangsamung
* Die Koordinaten müssen innerhalb der Schweiz liegen, also zwischen 45 und 48 Grad Nord und zwischen 4 und 11 Grad Ost.
* Woher die Ausreisser, bei denen plötzlich kommt "Niederschlag unwahrscheinlich in den nächsten zwei Stunden!"??

Unklar:
* RegionForecast.changePosition => ?
* RainAlarm.java => Texte anpassen: Siehe unten.
* Refactoring: MrGimoList?
* Ev. Weglassen der Kategorie "< 1.0 mm/h" im Radarbild (bedeutet kaum Regen) durch Bildvorverarbeitung (Schwellwert dunkler?).

== Ausgabe-Texte ==
Texte (zusammengesetzt):
* Niederschlag unwahrscheinlich in den nächsten zwei Stunden! (2010-12-31 17:05+01:00)
* Niederschlag möglich in ca. 56 Min.; er dauert voraussichtlich ca. 40 Min.! (2010-12-31 17:05+01:00)
* Niederschlag möglich in ca. 56 Min.; er dauert voraussichtlich über zwei Stunden! (2010-12-31 17:05+01:00)
* Aktuell Niederschlag; er dauert voraussichtlich noch ca. 23 Min.! (2010-12-31 17:05+01:00)
* Aktuell Niederschlag; er dauert voraussichtlich noch über zwei Stunden! (2010-12-31 17:05+01:00)
... gefolgt von "RainAlarm by http://labs.geometa.info/rainalarm (Quellen: SF Meteo und !MeteoSchweiz)".

Ausnahme-Texte:
* Und bei Problemen: Es ist ein Problem aufgetreten. Bitte versuchen Sie es später noch einmal.
* falls ausser Betrieb: Service zurzeit nicht in Betrieb. Versuchen Sie es später noch einmal.
* falls falsche oder fehlende Koordinaten: Koordinaten fehlen oder falsch. Siehe http://www.gis.hsr.ch/wiki/RainAlarm.

== JSON ==

Auruf: GET <URL>&format=json

=== Beispiel: ===

{
"message":"Niederschlag unwahrscheinlich in den nächsten zwei Stunden!",
"raining":false,
"rainStart":-1,
"rainStop":-1
}

=== JSON-Attributes: ===

message: string
* "text" => enthält die Informationen in Textform

raining: boolean
* true => es regnet
* false => es regnet nicht

rainStart: integer
* -1 => start unbekannt
* 0 => zurzeit Niederschlag
* 1... max => start in x min

rainStop: integer
* -1 => stop Unbekannt
* 0... max => stop in x min

== RainAlarm.java ==

Folgende Ausgabe gibt es (abhängig von start und stop):
* Kein arrivalNowcast (start): "Niederschlag unwahrscheinlich in den nächsten zwei Stunden!" (start = -1)
* arrivalNowcast (start):
** 1. "Aktuell Niederschlag." (start = 0)
** 2. "Niederschlag möglich in ca. xx Min." (start > 0)
* durationNowcast (stop):
** 1. "Er dauert voraussichtlich ca. yy Min.!" (stop > start)
** 2. "Er dauert voraussichtlich mehr als zwei Stunden!" (stop = -1)

http://dev.ifs.hsr.ch/rainalarm/browser/trunk/rainalarm/src/ch/hsr/ifs/rainalarm/

Die aktuelle Revision 76, Zeile 48 ff. sieht so aus:
private String createSMS(int[] startStopRain) {
int start = startStopRain[0];
int stop = startStopRain[1];
String duration = "";

if (start == -1)
return "Kaum Niederschlag innert 2h.";
if (stop == -1)
duration = "lÃ€nger als " + (120 - start) + " Min";
else
duration = (stop - start) + "Min.";
if (start == 0)
return "Es regnet(schneit) voraussichtlich noch " + duration + SUFFIX;

return "Niederschlag mÃ¶glich in ca. " + start + " Min, dauert voraussichtlich " + duration + SUFFIX;
}

Hier sollte 1. von Niederschlag die Rede sein und 2. sollte start (besser: arrivalNowcast) und stop (besser durationNowcast) angegeben werden - auch im Antwort-Text. Daher Neu, Zeile 48 ff. :

final int MAXFORECASTDURATION = 120;
private String createSMS(int[] startStopRain) {
int start = startStopRain[0];
int stop = startStopRain[1];
String durationText = "";
String text = "";
final int NODATA = -1;

// Kein Niederschlag in Sicht
if (start == NODATA)
return "Niederschlag unwahrscheinlich in den nächsten zwei Stunden!";

// Niederschlag ins Sicht (mit Dauer) oder Niederschlag dauert an (noch Dauer...).
if (stop == NODATA || (stop - start) >= MAXFORECASTDURATION)
durationText = "; er dauert voraussichtlich lÃ€nger als" + (MAXFORECASTDURATION - start) + " Min.!";
else
durationText = "; er dauert voraussichtlich ca. " + (stop - start) + " Min.!";

if (start == 0)
text = "Aktuell Niederschlag" + durationText;
else
text = "Niederschlag mÃ¶glich in ca. " + start + " Min." + durationText;

return text + SUFFIX;
}

== Ideen ==

Website:
* http://dev.ifs.hsr.ch/rainalarm/browser/trunk/rainalarm-website/src/ch/hsr/ifs/rainalarm/web/QueryHandler.java: 15: "Service zurzeit nicht in Dini Mueter Betrieb..." :->
* SMS-Service => Reicht das noch dazu? "wenigstens" die standard-signature anpassen zu "www.gis.hsr.ch ," (anstelle von "http://www.hsr.ch," => beachte auch das Komma)