<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
Hi Niels,<br>
It seems like PostGIS didn't build against the correct version of
GEOS. <br>
Can you verify that the PostGIS is including the correct geos_c.h?<br>
<br>
I've checked in a revised version of the PostGIS code. I made a change
to improve performance, at least for large base geometry cases. The
SQL interface now has overloaded versions of ST_intersects (contains,
containsProperly, and covers) that take an additional integer
argument. This 3rd argument is a surrogate key whose value is used to
determine when the 1st geometry argument has changed. So you need to
arrange that this value changes whenever the first geometry changes, or
just set it to constant value (like 0) if you know the first geometry
argument will never change during the execution of the query. If don't
pass this 3rd argument, you get the normal behaviour of ST_intersects.<br>
<br>
b<br>
<br>
niels hoffmann wrote:
<blockquote
cite="mid:8a22209b0801291840h989c100sf9a2237b7653a9b1@mail.gmail.com"
type="cite">
<div>
<pre>I installed both the development versions of postgis and geos on a Fedora Core5 test box running PostgreSQL 8.1.3
"POSTGIS=""1.3.3SVN"" GEOS=""3.1.0-CAPI-1.5.0"" PROJ=""Rel. 4.6.0, 21 Dec 2007"" USE_STATS"</pre>
<pre>When I try to execute the intersect query with the ST_IntersectsPrepared I get the error: </pre>
<pre><div>SELECT ST_intersection(a.geom, l.geom) as intersect_geom, a.*, l.class,<a
moz-do-not-send="true" href="http://l.name">l.name</a>, l.replid
from first_table l, second_table a
Where ST_IntersectsPrepared(a.geom, l.geom);</div><div> </div><div>ERROR: Not implemented in this version!</div><div> </div><div>The original query: </div><div>SELECT intersection(a.geom, l.geom) as intersect_geom, a.*, l.class,<a
moz-do-not-send="true" href="http://l.name">l.name</a>, l.replid
from first_table l, second_table a
Where a.geom && l.geom
AND intersects(a.geom, l.geom);</div><div> </div><div>Throws an error as well:</div><div> </div><div><div>NOTICE: IllegalArgumentException: Exponent out of bounds</div>
<div> </div><div>ERROR: GEOS Intersection() threw an error!</div></div></pre>
</div>
<div>Does anybody have a suggestion how to troubleshoot this?</div>
<div>I grabbed GEOS from the SVN repository, the hourly svn snapshot
link on the geos page seems to give me the 3.0.0rc5 version?</div>
<div> </div>
<div>Cheers, Niels</div>
<div> </div>
<div>Ben Jubb wrote: </div>
<div>If you are comfortable with building your own version of
PostGIS, you <br>
could get the latest development version from the SVN repository
(you'll <br>
need to get the latest dev version of GEOS too), and try using the new
<br>
'prepared' predicate ST_IntersectsPrepared. This is a replacement for <br>
the intersects() function in your code, that (depending on the <br>
geometries involved) could speed up that test by 100s of times. It <br>
optimized for the case when the first argument is changing slowly, and <br>
the second argument is changing every invocation.</div>
<div> </div>
<div>What do you think is bottleneck in your code though? Is it the <br>
intersection() operation or the intersects() test? It depends on the <br>
distribution of your data, and the odds of any pair of geometries <br>
intersecting.</div>
<div> </div>
<div>b</div>
<div> </div>
<div>Stephen Woodbridge wrote:<br>
> > Yeah, this sounds like it will run for a very very long time.
A couple <br>
> > of thoughts that have more to do with managing the process:<br>
> ><br>
> > 1) make sure you run an is_valid() check on both tables and
remove/fix <br>
> > any geometries that are not valid. It is a pain when you hit
one of <br>
> > these and it nukes your transaction or crashes the server and
you have <br>
> > to start over.<br>
> ><br>
> > 2) you might want to break this into multiple queries based
on some <br>
> > subset of the record in the smaller table. Like do 1-10000, <br>
> > 10001-20000, etc. This would allow you to get the results of
each <br>
> > commited so a restart would be less painful, also this would
allow you <br>
> > to get some timing statistics to better predict how long the
remainder <br>
> > of the rows will take.<br>
> ><br>
> > -Steve<br>
> ><br>
> > niels hoffmann wrote:<br>
>> >> Hi,<br>
>> >> <br>
>> >> I am fairly new to Postgis so I like some feedback
whether I am going <br>
>> >> through the right moves.<br>
>> >> I am trying to create a new table with the
intersected results from <br>
>> >> two input polygon tables.<br>
>> >> Both tables are in NZMG (2193) the first table has
100000+ records, <br>
>> >> the second table has 400000+ records.<br>
>> >> The query I am using is:<br>
>> >> <br>
>> >> Create table ablc_pol with OIDS as<br>
>> >> SELECT intersection(a.geom, l.geom) as
intersect_geom, a.*, <br>
>> >> l."CLASS",l."NAME", l."REPLID"<br>
>> >> from first_table a, second_table l<br>
>> >> Where a.geom && l.geom<br>
>> >> AND intersects(a.geom, l.geom);<br>
>> >> <br>
>> >> Currently this query is taking >200 hours before I
cancelled it <br>
>> >> because I wasn't sure it would ever end. However,
running it on a <br>
>> >> small subset showed satisfactory results...<br>
>> >> I am using version 1.2 on Windows.<br>
>> >> Does it matter which table I put first in the query
or would the <br>
>> >> optimizer take care of that?<br>
>> >> <br>
>> >> <br>
>> >> Cheers,<br>
>> >> Niels</div>
<pre wrap="">
<hr size="4" width="90%">
_______________________________________________
postgis-users mailing list
<a class="moz-txt-link-abbreviated" href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a>
<a class="moz-txt-link-freetext" href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</a>
</pre>
</blockquote>
</body>
</html>