[postgis-users] Why does this take almost 3 hours?

[Paul Ramsey]

Sean,

GEOS is a reflection of JTS.  To the extent that native compilation  
of Java code works, there is a great deal to be said for it: the  
optimization is done by experts; there is no chance of introducing  
errors or bad practices in the porting process.  Native compiled JTS  
has been found to be faster than GEOS in a number of cases (though  
fixes applied to trunk by the C++ experts remove a some of them).   
Unfortunately, native compiled JTS is really only available on  
platforms will supported by GCJ, and Windows is among those platforms  
not 100% supported.  While GEOS is available on almost all GNU  
supported platforms.  I would love to see native compiled JTS take  
over from GEOS, but I do not see GCJ coming along all that fast on  
the platform side.  YMMV, for sure.

Paul

On Aug 29, 2005, at 4:25 PM, Sean Gillies wrote:

> I'm seeing the recommendation to try JTS instead of GEOS in several  
> threads now. Are we losing confidence in GEOS?
>
> Sean
>
> On Aug 29, 2005, at 4:46 PM, strk at refractions.net wrote:
>
>
>> Does queryByShape() detect real intersection?
>>
>> I bet the index has nothing to do with this, it's just
>> the Intersects() function taking your time. Try to take
>> it off.
>>
>> Then, if you can, try using JTS instead of GEOS to compare
>> performances (I can do this part for you if you send me
>> source data).
>>
>> --strk;
>>
>> On Mon, Aug 29, 2005 at 03:14:20PM -0600, Ethan Alpert wrote:
>>
>>>
>>> Bill yes my nga_reg is 11 complex shapes.
>>>
>>> What I don't understand is why queryByShape() using QIX files is  
>>> SO much
>>> better.
>>>
>>> -e
>>>
>>> -----Original Message-----
>>> From: postgis-users-bounces at postgis.refractions.net
>>> [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf  
>>> Of Bill
>>> Binko
>>> Sent: Monday, August 29, 2005 3:22 PM
>>> To: PostGIS Users Discussion
>>> Subject: Re: [postgis-users] Why does this take almost 3 hours?
>>>
>>>
>>> On Mon, 29 Aug 2005, Ethan Alpert wrote:
>>> ...
>>>
>>>>
>>>> nga_reg is basically the world admin divided up into 11 distinct
>>>> regions. My new_snaps table has 13543 records.
>>>>
>>>> 2 hours and 39 minutes later pgsql2shp of the above query finished
>>>>
>>>> The above query's explain analyze is:
>>>>
>>>> spatialdb_sil0=> explain analyze  SELECT a.catalogid, b.cii_region,
>>>> a.the_geom  FROM new_snaps a, nga_reg b WHERE a.the_geom &&  
>>>> b.the_geom
>>>>
>>>
>>>
>>>> AND intersects(a.the_geom, b.the_geom);
>>>>
>>>>
>>> QUERY
>>>
>>>> PLAN
>>>>
>>>
>>>
>>>> ------------------------------------------------------------------- 
>>>> ---
>>>> --
>>>>
>>>>
>>> -------------------------------------------------------------------- 
>>> ---
>>>
>>>>  Nested Loop  (cost=0.00..67.41 rows=4966 width=273) (actual
>>>> time=11.040..2397944.885 rows=13416 loops=1)
>>>>    Join Filter: intersects("inner".the_geom, "outer".the_geom)
>>>>    ->  Seq Scan on nga_reg b  (cost=0.00..1.11 rows=11 width=37)
>>>>
>>> (actual
>>>
>>>> time=0.004..147.586 rows=11 loops=1)
>>>>    ->  Index Scan using new_snaps_spatial on new_snaps a
>>>> (cost=0.00..6.01 rows=1 width=268) (actual time=68.748..9599.432
>>>> rows=3892 loops=11)
>>>>          Index Cond: (a.the_geom && "outer".the_geom)
>>>>          Filter: (a.the_geom && "outer".the_geom)
>>>>  Total runtime: 2397998.833 ms
>>>>
>>>>
>>>
>>> How many shapes are in nga_reg?  When you say "basically the  
>>> world admin
>>>
>>> divided into 11 districts", do you mean there are only 11  
>>> shapes?  If
>>> so,
>>> how complex are the shapes?
>>>
>>> There are two possibilities I can see where PostGIS would fail  
>>> you in
>>> this
>>> way.
>>>
>>> One is that the nga_reg table is huge (lots of shapes). If that's  
>>> the
>>> case, then PostGIS should be driving the other direction (doing a  
>>> seq
>>> scan
>>> on new_snaps and using the index on nga_reg).  If it's not, it is  
>>> a poor
>>>
>>> query plan and perhaps the PostGIS developers can help track that  
>>> down.
>>>
>>> An easy way to test this is to remove the index on new_snaps and  
>>> redo
>>> your
>>> explain (and your timing test).
>>>
>>> The alternative is that you have 11 (or some other small number  
>>> of) very
>>>
>>> large, very complicated shapes.  If that's the case, your indexes  
>>> are
>>> not
>>> going to help you since you'll end up doing an expensive intersect()
>>> operation for each pair, and most new_snaps's bouding boxes will  
>>> fall
>>> within the large shape's bounding boxes.  If the shapes are  
>>> Collections,
>>>
>>> you might try "exploding" them so that you can get better use of the
>>> bounding boxes.
>>>
>>> I hope this helps
>>> Bill
>>> _______________________________________________
>>> postgis-users mailing list postgis-users at postgis.refractions.net
>>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>> _______________________________________________
>>> postgis-users mailing list
>>> postgis-users at postgis.refractions.net
>>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>>
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>
>>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>