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

[Étienne Desgagné]

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

[Jeff Lake]

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

_______________________________________________
postgis-users mailing list
postgi...@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

[Étienne Desgagné]

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-us...@lists.osgeo.org [mailto:postgis-us...@lists.osgeo.org] De la part de Jeff Lake
Envoyé : 29 novembre 2012 09:15
À : postgi...@lists.osgeo.org
Objet : Re: [postgis-users] Why PostGIS ST_Intersection much slower than ArcGIS Intersect ?

[Paul Ramsey]

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....@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-us...@lists.osgeo.org
> [mailto:postgis-us...@lists.osgeo.org] De la part de Jeff Lake
> Envoyé : 29 novembre 2012 09:15
> À : postgi...@lists.osgeo.org
> Objet : Re: [postgis-users] Why PostGIS ST_Intersection much slower than
> ArcGIS Intersect ?
>
>
>

[Paul Ramsey]

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.

[É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 : pra...@cleverelephant.ca [mailto:pra...@cleverelephant.ca] De la part
de Paul Ramsey
Envoyé : 29 novembre 2012 11:38
À : etienne....@roche.ca; PostGIS Users Discussion

[Andrea Peri]

Hi Paul,

I like understand better this situation.

I read often that to fast the ST_Intersect() over huge geometry is
better to clip them on lower size geometries.
This is understandable for me.

But the Etienne use-case speak of the ST_Intersection() don't the
ST_Intersect (that is fast instead).

My doubt is "Why is fast the ST_Intersects() if it is a huge geometry ?"

And when you say a "prepared intersection" are speaking of the usual
teorical solution to clip a huge geometry on a set of lower sized
geometries ?

Thx,

--
-----------------
Andrea Peri
. . . . . . . . .
qwerty àèìòù
-----------------

[Paul Ramsey]

Google knows,
http://trac.osgeo.org/geos/wiki/PreparedGeometry
http://blog.cleverelephant.ca/2008/10/postgis-performance-prepared-geometry.html

p

[Andrea Peri]

Paul Ramsey wrote:
>Google knows,
>http://trac.osgeo.org/geos/wiki/PreparedGeometry
>http://blog.cleverelephant.ca/2008/10/postgis-performance-prepared-geometry.html
>
>p

I see
http://blog.cleverelephant.ca/2008/10/postgis-performance-prepared-geometry.html

Now I understand better.

As I understand:
It is the "prepared geometry" solution
it is used on geos and just used on these postgis functions:
ST_Intersects(), ST_Contains(), ST_Covers() and ST_ContainsProperly(),
but actually, don't used by the ST_Intersection().

[Brian Walawender]

I found way speed up the query some by using a bounding box sub query first.  I have a function that calculates the miles of railroad in a given geometry.  I found that just using ST_Intersection combined with large complex geometries (like the state of New York) would take forever on my server.  I rewrote my function as below and my query dropped from 20 minutes to 35 seconds!

SELECT (SUM(ST_Length(ST_Intersection(ST_Transform(r_geom, 2163),ST_Transform($1,2163))))/1000)*.6213711 as miles_of_rails

FROM (

SELECT ST_Intersection(r.geom, ST_Envelope($1)) AS r_geom

FROM data.rail_lines r

WHERE ST_Intersects(r.geom, $1)

) AS q

bw