[postgis-users] ST_Crosses doesn't use index?
Hi
ST_Crosses doc says that it uses the spatial index (if available).
But I'm afraid it's not. I'm using PostGIS 1.5 on Linux.
-- as it should be:
SELECT name
FROM osm_line a, osm_line b
WHERE a.osm_id != b.osm_id
AND ST_Crosses(a.way, b.way)
-- takes longer than 60 sec, 586 features.
-- now with ST_Contains:
SELECT name
FROM osm_line a, osm_line b
WHERE ST_Contains('BOX(8.775 47.245, 8.873 47.208'::box2d,a.way)
AND ST_Contains('BOX(8.775 47.245, 8.873 47.208'::box2d,b.way)
AND a.osm_id != b.osm_id
AND ST_Crosses(a.way, b.way)
-- takes 3.5sec, 586 features.
Obviously ST_Crosses does not consider the geometry index.
Why this?
Yours, Stefan
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] ST_Crosses doesn't use index?
You might provide a description of the tables (\d at the psql prompt), and
perhaps the output of an EXPLAIN ANALYZE for this command. Any non-standard
config settings might be of relevance as well.
Greg Williamson
- Original Message -
From: Stefan Keller sfkel...@gmail.com
To: PostGIS Users Discussion postgis-users@postgis.refractions.net
Cc:
Sent: Thursday, February 16, 2012 3:51 PM
Subject: [postgis-users] ST_Crosses doesn't use index?
Hi
ST_Crosses doc says that it uses the spatial index (if available).
But I'm afraid it's not. I'm using PostGIS 1.5 on Linux.
-- as it should be:
SELECT name
FROM osm_line a, osm_line b
WHERE a.osm_id != b.osm_id
AND ST_Crosses(a.way, b.way)
-- takes longer than 60 sec, 586 features.
-- now with ST_Contains:
SELECT name
FROM osm_line a, osm_line b
WHERE ST_Contains('BOX(8.775 47.245, 8.873 47.208'::box2d,a.way)
AND ST_Contains('BOX(8.775 47.245, 8.873 47.208'::box2d,b.way)
AND a.osm_id != b.osm_id
AND ST_Crosses(a.way, b.way)
-- takes 3.5sec, 586 features.
Obviously ST_Crosses does not consider the geometry index.
Why this?
Yours, Stefan
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] ST_Crosses doesn't use index?
Stefan,
This has to do with your data / configuration, not ST_Crosses, as the
function clearly includes an index call.
CREATE OR REPLACE FUNCTION ST_Crosses(geometry,geometry)
RETURNS boolean
AS 'SELECT $1 $2 AND _ST_Crosses($1,$2)'
LANGUAGE 'SQL' IMMUTABLE;
P.
On Thu, Feb 16, 2012 at 3:51 PM, Stefan Keller sfkel...@gmail.com wrote:
Hi
ST_Crosses doc says that it uses the spatial index (if available).
But I'm afraid it's not. I'm using PostGIS 1.5 on Linux.
-- as it should be:
SELECT name
FROM osm_line a, osm_line b
WHERE a.osm_id != b.osm_id
AND ST_Crosses(a.way, b.way)
-- takes longer than 60 sec, 586 features.
-- now with ST_Contains:
SELECT name
FROM osm_line a, osm_line b
WHERE ST_Contains('BOX(8.775 47.245, 8.873 47.208'::box2d,a.way)
AND ST_Contains('BOX(8.775 47.245, 8.873 47.208'::box2d,b.way)
AND a.osm_id != b.osm_id
AND ST_Crosses(a.way, b.way)
-- takes 3.5sec, 586 features.
Obviously ST_Crosses does not consider the geometry index.
Why this?
Yours, Stefan
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] ST_Crosses doesn't use index?
2012/2/17 Greg Williamson gwilliamso...@yahoo.com:
You might provide a description of the tables (\d at the psql prompt),
and perhaps the output of an EXPLAIN ANALYZE for this command.
Any non-standard config settings might be of relevance as well.
Greg Williamson
Ok; I realize that my first query was taking whole DB whereas the
second takes a spatial subset.
But what I still wonder is, why ST_Contains needs to be applied to the
second geometry (of table b) too since the index should really sort
out any outliers of table b
See below (I'm using PostGIS 1.5 on Linux).
Yours, Stefan
Table def.:
CREATE TABLE osm_line
(
osm_id integer,
...
... -- around 20 attributes from osm2pgsql import
...
tags hstore,
way geometry
) WITH (OIDS=FALSE);
Query 1:
gisdb= EXPLAIN ANALYZE
gisdb- SELECT a.name
gisdb- FROM osm_line a, osm_line b
gisdb- WHERE ST_Contains(ST_Transform(ST_SetSRID('BOX(8.775 47.245,
8.873 47.208)'::box2d, 4326),900913),a.way)
gisdb- AND a.osm_id != b.osm_id
gisdb- AND ST_Crosses(a.way, b.way);
QUERY PLAN
--
Nested Loop (cost=60.59..14659.13 rows=713 width=14) (actual
time=157.097..26896.760 rows=954 loops=1)
Join Filter: ((a.osm_id b.osm_id) AND _st_crosses(a.way, b.way))
- Bitmap Heap Scan on osm_line a (cost=60.59..5708.36 rows=535
width=1097) (actual time=1.398..16.426 rows=1702 loops=1)
Recheck Cond: ('01030...'::geometry way)
Filter: _st_contains('01030...'::geometry, way)
- Bitmap Index Scan on osm_line_index (cost=0.00..60.46
rows=1606 width=0) (actual time=1.255..1.255 rows=1849 loops=1)
Index Cond: ('01030...'::geometry way)
- Index Scan using osm_line_index on osm_line b
(cost=0.00..15.94 rows=3 width=1083) (actual time=0.036..0.420 rows=28
loops=1702)
Index Cond: (a.way b.way)
Total runtime: 26897.124 ms
(10 rows)
Query 2:
gisdb= EXPLAIN ANALYZE
gisdb- SELECT a.name
gisdb- FROM osm_line a, osm_line b
gisdb- WHERE ST_Contains(ST_Transform(ST_SetSRID('BOX(8.775 47.245,
8.873 47.208)'::box2d, 4326),900913),a.way)
gisdb- AND ST_Contains(ST_Transform(ST_SetSRID('BOX(8.775 47.245,
8.873 47.208)'::box2d, 4326),900913),b.way)
gisdb- AND a.osm_id != b.osm_id
gisdb- AND ST_Crosses(a.way, b.way);
QUERY PLAN
--
Nested Loop (cost=60.59..10353.28 rows=1 width=14) (actual
time=19.925..3088.990 rows=586 loops=1)
Join Filter: ((a.osm_id b.osm_id) AND _st_crosses(a.way, b.way))
- Bitmap Heap Scan on osm_line a (cost=60.59..5708.36 rows=535
width=1097) (actual time=1.432..12.943 rows=1702 loops=1)
Recheck Cond: ('01030...'::geometry way)
Filter: _st_contains('01030...'::geometry, way)
- Bitmap Index Scan on osm_line_index (cost=0.00..60.46
rows=1606 width=0) (actual time=1.285..1.285 rows=1849 loops=1)
Index Cond: ('01030...'::geometry way)
- Index Scan using osm_line_index on osm_line b (cost=0.00..8.42
rows=1 width=1083) (actual time=0.464..1.069 rows=12 loops=1702)
Index Cond: (('01030...'::geometry b.way)
AND (a.way b.way))
Filter: _st_contains('01030...'::geometry, b.way)
Total runtime: 3089.228 ms
(11 rows)
gisdb=
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
