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 <[email protected]> > 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: [email protected] > [mailto:[email protected]] On Behalf Of Pietro Rossin > Sent: Tuesday, May 07, 2013 10:31 AM > To: [email protected] > 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 > [email protected] > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users > > > _______________________________________________ > postgis-users mailing list > [email protected] > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users >
_______________________________________________ postgis-users mailing list [email protected] http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
