[postgis-users] Why PostGIS ST_Intersection much slower than ArcGIS Intersect ?

[Étienne Desgagné]

Thank you very much Paul for the explanation... So for now I will have to
live with the current situation. In general I'm a big defender of PostGIS
and I like to claim that it can give similar or better result than any GIS
like ArcGIS... However, in the case of this specific ST_Intersection
operator it's just a little bit disappointing but nothing critical...

Best regards

Etienne  

-----Message d'origine-----
De : pramsey at cleverelephant.ca [mailto:pramsey at cleverelephant.ca] De la part
de Paul Ramsey
Envoyé : 29 novembre 2012 11:38
À : etienne.desgagne at roche.ca; PostGIS Users Discussion
Objet : Re: [postgis-users] Why PostGIS ST_Intersection much slower than
ArcGIS Intersect ?

For information: ST_Intersects used to be slow, and the caching system and
"prepared intersects" development made it 10-20x faster. A similar approach
to intersection would yield similar results.

On Thu, Nov 29, 2012 at 10:36 AM, Paul Ramsey <pramsey at opengeo.org> wrote:
> It's normal, assuming that some of your geometries are quite large. If 
> someone wants to fund a "prepared intersection" development task I'm 
> sure we could make it 10x-40x faster.
>
> P.
>
> On Thu, Nov 29, 2012 at 10:04 AM, Étienne Desgagné 
> <etienne.desgagne at roche.ca> wrote:
>> It take millisecond to me to when using ST_Intersects on a spatial join.
>> What take much longer is when using ST_Intersection in the SELECT 
>> clause to effectively “clip” a layer with the other one

>>
>>
>>
>> My computer run under Windows 7 64bits with dual core 2.70GHZ and 8 Go
RAM.
>>
>>
>>
>> Is there anything I can do to check if it’s normal behavior on this 
>> platform or if something may be wrong on my installation?
>>
>>
>>
>> Thanks
>>
>>
>>
>> Etienne
>>
>>
>>
>> De : postgis-users-bounces at lists.osgeo.org
>> [mailto:postgis-users-bounces at lists.osgeo.org] De la part de Jeff 
>> Lake Envoyé : 29 novembre 2012 09:15 À : 
>> postgis-users at lists.osgeo.org Objet : Re: [postgis-users] Why PostGIS 
>> ST_Intersection much slower than ArcGIS Intersect ?
>>
>>
>>
>> Must have something to do with windows and or memory ..
>> I use ST_Intersects daily on a Linux Server (PostGIS 2.0, PG 9.1.6) 
>> with a quad core Xeon and 16gb memory it takes milliseconds on much 
>> larger layers ..
>>
>> Jeff Lake
>> MichiganWxSystem
>> AllisonHouse
>> GRLevelXStuff
>>
>> On 11/28/2012 16:22, Étienne Desgagné wrote:
>>
>> Hello,
>>
>>     I don't want to denigrate PostGIS in any way and I know that my 
>> comparison may be wrong but I'm trying to understand why a basic 
>> ST_Intersection of two relatively small layers (< 1000 polygons) take 
>> about
>> 1m40 sec in PostGIS(2.0 on PG 9.2 on Windows) while applying the 
>> ArcGIS Intersect on same layers and machine take about 2 sec?
>>
>> I'm aware of some PostgreSQL tuning settings but even applying basic 
>> tuning recommendation for PostGIS I'm facing the same problem...
>>
>>
>> My two layers also have a spatial index.
>>
>>
>> Is it normal behavior? Any suggestion for reaching better performance?
>>
>> Note that I'm also using ST_Intersects on the join to filter only 
>> intersecting features...
>>
>> Thanks a lot
>>
>> Etienne
>>
>>
>>
>>
>> _______________________________________________
>>
>> 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
>>