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

[Pietro Rossin]

Hello
Today after 10h running the query stopped with this error
ERROR: could not extend file "base/552249/15383842.291": No space left on
device
Stato SQL: 53100
Suggerimento: Check free disk space.

The space available was 280Gb.... so That file (doesn't exist now, because
I think it was cancelled after query exit) was pretty big ;-)

Now I'm trying the left join method, let's see if it works.
The union of all 980000 polygons into one multipolygon will make a single
record table, right?
Isn't the complexity of the geometry contained into that single record "too
complex" to be handled by the query?

Another solution that I am thinking is to add a field to these table that
has the code of the original dxf extent.
All features come from a set of more than 900 dxf that were loaded into a
postgis db. all these 900 dxf were coded and I have a postgis tile vector
table (dxf tileindex)..
Then make a query with a join on a code field plus a geometry condition
Do you think this will be a better performing method?
Again thanks

bie
pietro


2013/5/7 Paragon Corporation <lr at pcorp.us>

>  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
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20130508/11d3ebe0/attachment.html>