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

[Paragon Corporation]

 Try,

SELECT a.id, a.layer, a.the_geom into newtable
  FROM my_first_table as a LEFT JOIN my_second_sable as b
   ON ST_CoveredBy(a.the_geom, b.the_geom)
WHERE b.gid IS NULL;


Where b.gid is the primary key in your b table.

Leo
http://www.postgis.us



-----Original Message-----
From: postgis-users-bounces at lists.osgeo.org
[mailto:postgis-users-bounces at lists.osgeo.org] On Behalf Of Pietro Rossin
Sent: Tuesday, May 07, 2013 10:31 AM
To: postgis-users at postgis.refractions.net
Subject: [postgis-users] ST_CoveredBy() query run very very very slow

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-t
p5003187.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