[postgis-users] ST_CoveredBy() query run very very very slow

[Hugues François]

Hello,

In this way, you are testing each  line with each polygon : each covered by one polygon is not covered by the others and will be returned n-1 times. 
You should try to union your polygons to a single one (with multiparts or not) in a with or a temp table and then try the coveredby test with it.

Hug

Pietro Rossin <pierigis at gmail.com> a écrit :

Hello everybody
I'm trying to select features from two spatial tables, geometry 2D.

The first table is polylines from my whole area (984000 records).
The second table is a polygon table, 970000 records. These polygons were
created from the first table with an external tool (FME).

Lots of polylines weren't converted to polygons and now I want to extract
(into a new table) from the first table all the lines not converted to
polygon to perform additional operations.

The two table have indexes (gist) on the geom column

So, my query is:

SELECT a.id, a.layer, a.the_geom into newtable
  FROM my_first_table as a, my_second_sable as b
  where
  not ST_CoveredBy(a.the_geom, b.the_geom);

Yesterdary I let it run for 72.000.000ms and then I had to stop it.
I thought it was because of the Z values of all the geometries and the query
was:

SELECT a.id, a.layer, a.the_geom into newtable
  FROM my_first_table as a, my_second_sable as b
  where
  not ST_CoveredBy(st_force_2d(a.the_geom), st_force_2d(b.the_geom));

Today It's running for 14.000.000ms and I have to stop it again...

Why is this query so slow???
My pc isd Windows XP 32b 4Gb ram, 2 opteron 252 processor.

Thanks
Pietro



--
View this message in context: http://postgis.17.x6.nabble.com/ST-CoveredBy-query-run-very-very-very-slow-tp5003187.html
Sent from the PostGIS - User mailing list archive at Nabble.com.
_______________________________________________
postgis-users mailing list
postgis-users at lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users