If you are comfortable with building your own version of PostGIS, you
could get the latest development version from the SVN repository (you'll
need to get the latest dev version of GEOS too), and try using the new
'prepared' predicate ST_IntersectsPrepared. This is a replacement for
the intersects() function in your code, that (depending on the
geometries involved) could speed up that test by 100s of times. It
optimized for the case when the first argument is changing slowly, and
the second argument is changing every invocation.
What do you think is bottleneck in your code though? Is it the
intersection() operation or the intersects() test? It depends on the
distribution of your data, and the odds of any pair of geometries
intersecting.
b
Stephen Woodbridge wrote:
Yeah, this sounds like it will run for a very very long time. A couple
of thoughts that have more to do with managing the process:
1) make sure you run an is_valid() check on both tables and remove/fix
any geometries that are not valid. It is a pain when you hit one of
these and it nukes your transaction or crashes the server and you have
to start over.
2) you might want to break this into multiple queries based on some
subset of the record in the smaller table. Like do 1-10000,
10001-20000, etc. This would allow you to get the results of each
commited so a restart would be less painful, also this would allow you
to get some timing statistics to better predict how long the remainder
of the rows will take.
-Steve
niels hoffmann wrote:
Hi,
I am fairly new to Postgis so I like some feedback whether I am going
through the right moves.
I am trying to create a new table with the intersected results from
two input polygon tables.
Both tables are in NZMG (2193) the first table has 100000+ records,
the second table has 400000+ records.
The query I am using is:
Create table ablc_pol with OIDS as
SELECT intersection(a.geom, l.geom) as intersect_geom, a.*,
l."CLASS",l."NAME", l."REPLID"
from first_table a, second_table l
Where a.geom && l.geom
AND intersects(a.geom, l.geom);
Currently this query is taking >200 hours before I cancelled it
because I wasn't sure it would ever end. However, running it on a
small subset showed satisfactory results...
I am using version 1.2 on Windows.
Does it matter which table I put first in the query or would the
optimizer take care of that?
Cheers,
Niels
------------------------------------------------------------------------
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
begin:vcard
fn:Ben Jubb
n:Jubb;Ben
org:Refractions Research
adr:;;#300 - 1207 Douglas Street;Victoria;;V8W 2E7;Canada
email;internet:[EMAIL PROTECTED]
tel;cell:250 813 0171
x-mozilla-html:FALSE
url:http://www.refractions.net/
version:2.1
end:vcard
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
