[postgis-users] Benshmark postgis

[Stefan Keller]

Hi Arnaud,

Sorry for coming late to this discussion.

I can offer another random generator (see below) as well as a
well-defined benchmark based on real world data: "The HSR Texas
Spatial Database Benchmark" (see http://www.gis.hsr.ch/wiki/Benchmark
). If you want larger real world data then one could tage
OpenStreetMap data. But this still needs some schema mapping before,
like we do with our osm2gis converter (see OpenStreetMap-in-a-Box:
http://dev.ifs.hsr.ch/osminabox ).
Perhaps this helps doing your benchmarks.

Yours, S.

-----------------------------------------------------------------
-- Generate random point geometries (2D) given a boundary polygon
-- Source from WKTRasterTutorial01 (Credits to Paul Racine)
-----------------------------------------------------------------
DROP FUNCTION IF EXISTS ST_RandomPoints(geom geometry, nb int);
--
CREATE OR REPLACE FUNCTION ST_RandomPoints(geom geometry, nb int)
RETURNS SETOF geometry
LANGUAGE 'plpgsql' IMMUTABLE STRICT
AS $$
DECLARE
  pt geometry;
  xmin float8;
  xmax float8;
  ymin float8;
  ymax float8;
  count integer := 0;
BEGIN
  IF (ST_GeometryType(geom) != 'ST_Polygon' )
    AND (ST_GeometryType(geom) != 'ST_MultiPolygon') THEN
    RAISE EXCEPTION 'Attempting to get random point in a non polygon geometry';
  END IF;
  SELECT ST_XMin(geom), ST_XMax(geom), ST_YMin(geom), ST_YMax(geom)
    INTO xmin, xmax, ymin, ymax;
  WHILE count < nb LOOP
    SELECT ST_SetSRID(
	ST_MakePoint(xmin + (xmax - xmin) * random(),
                     ymin + (ymax - ymin) * random()), ST_SRID(geom))
      INTO pt;
    IF ST_Contains(geom, pt) THEN
      count := count + 1;
      RETURN NEXT pt;
    END IF;
  END LOOP;
  RETURN;
END;
$$

-- Tests:
SELECT generate_series(1, 5) AS id,
ST_AsText(ST_RandomPoints(ST_MakeEnvelope(-180, -90, 180, 90, 4326),
5));
SELECT id, ST_AsText(ST_RandomPoint(ST_MakeEnvelope(-180, -90, 180,
90, 4326))) FROM generate_series(1, 5) AS id;
SELECT id, ST_AsText(ST_RandomPoint(ST_MakeEnvelope(45.82, 5.9, 47.84,
10.58, 4326))) FROM generate_series(1, 5) AS id;
SELECT generate_series(1, 5) AS id,
ST_AsText(ST_RandomPoints(ST_GeomFromText('POLYGON((480408
75436,484818 299965,835118 303541,844109 79149,480408 75436))',
21781),5));



2010/11/26 strk <strk at keybit.net>:
> On Fri, Nov 26, 2010 at 09:38:09AM +0100, Arnaud Vandecasteele wrote:
>> I understand better the interest of the Gis Index !  Thank you for your answer.
>> I do not want to evaluate the postgis vs other database, just the time
>> the queries will take for 1000 10 000 or more objects.
>> The use case will be this one :
>> We have several thousands of vessels and every 5 seconds I must do a
>> query to check if they are or not in a dangerous area?
>
> How do dangerous areas look ?
> Do their bounding boxes overlap much or not at all ?
> Ho many areas ?
> Do areas move often or could you "prepare" them to take
> the most out of the index ?
>
> --strk;
>
>  ()   Free GIS & Flash consultant/developer
>  /\   http://strk.keybit.net/services.html
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>