[postgis-users] performace question

[Martin Landa]

Hi,

2009/4/1 Paul Ramsey <pramsey at opengeo.org>:
> You could also make it faster by cutting your boundary line up into
> about 200 smaller lines. That way your indexes would be useful and
> most of the candidates would be rejected right away on bounding box
> criteria, instead of falling through to full geometry tests.

thanks for help.

Probably I do not understand ST_Touches() correctly.

1) create boundary of CZ based on cities ('obce').

CREATE TABLE cr (gid serial);
ALTER TABLE cr ADD PRIMARY KEY (gid);
SELECT AddGeometryColumn('cr', 'the_geom', 102067, 'LINESTRING', 2);
INSERT INTO cr VALUES (1, (SELECT ST_Boundary(ST_Union(the_geom))
 AS the_geom FROM obce GROUP BY pomoc));
CREATE INDEX cr_the_geom_gist ON cr USING GIST (the_geom);

2) select all cities which touch the state boundary

SELECT count(obce.gid) FROM obce,cr WHERE ST_Touches(obce.the_geom,
cr.the_geom);

-> 285 cities selected (OK)

OK, it takes more then 300s because the boundary is represented by one
linestring.

3) split the boundary to 2km length linestrings.

CREATE TABLE cr_2km AS SELECT ST_Line_Substring(the_geom, 2000 * n / length,
  CASE
	WHEN 2000 * (n + 1) < length THEN 2000 * (n + 1) / length
	ELSE 1
  END) AS the_geom
FROM
(SELECT cr.gid,
  ST_LineMerge(cr.the_geom) AS the_geom,
  ST_Length(cr.the_geom) AS length
  FROM cr
  ) AS t
CROSS JOIN generate_series(0, 2000) AS n
WHERE n * 2000 / length < 1;

ALTER TABLE cr_2km ADD COLUMN gid serial;
ALTER TABLE cr_2km ADD PRIMARY KEY (gid);
CREATE INDEX cr_2km_the_geom_gist ON cr_2km USING GIST (the_geom);

4) SELECT count(distinct obce.gid) FROM obce,cr_2km WHERE
ST_Touches(obce.the_geom, cr_2km.the_geom);

-> only 211 cities selected (BAD)

but

SELECT count(distinct obce.gid) FROM obce,cr_2km WHERE
ST_Intersects(obce.the_geom, cr_2km.the_geom);

gives right answer.

Thanks for any hits, Martin

-- 
Martin Landa <landa.martin gmail.com> * http://gama.fsv.cvut.cz/~landa