[postgis-users] Re: Slow spatial join

[Anders Larsson]

Thank you for suggestions, but unfortunately it didn´t help using Contains.
The region data is a standard country map (i also tried with another 
country dataset), but same result.
I have also tried with other point data, but same slow response.

I tried to do the join with Esri ArcMap and it finished in 5 sek! Only a 
1/10th of the time...
Also if I leave out the last part and only do the 
Indexed-overlaps-porthion of the question it still takes
15 sek - much longer than the complete question in ArcMap
Ex:
SELECT occurrencies.name, country.name
FROM occurrencies, country
WHERE occurrencies.the_geom && country.the_geom

I have also made a fresh install of PostgreSQL 8.2 + PostGIS 1.2.1 on 
another computer and tried the join on that computer, with same slow 
result..
I'm using windows XP sp1. and XP sp2

All my data is created with SRID -1 (does this matter for speed?)

/Anders
>
>> From: Paul Ramsey <pramsey at refractions.net>
>>
>> That's not a  lot of data, and your explain shows that the index  
>> portion of the problem finished up pretty fast. That just leaves your  
>> regions. Are they really big? (lots and lots of vertices?)
>>
>> Try using Contains(country.the_geom, occurencies.the_geom) instead of  
>> Within and see what changes.
>>
>> P
>>
>> On 20-May-07, at 1:36 AM, Anders Larsson wrote:
>>
>>   
>>> Hello!
>>>
>>> I am trying to join some points with regions.
>>> I have one table(occurrencies) with 900 points and another table 
>>> (coutry)
>>> with 600 regions.
>>>
>>> I use following SQL to get a countryname bound to each point.
>>>
>>> SELECT occurrencies.name, country.name
>>> FROM occurrencies, country
>>> WHERE occurrencies.the_geom && country.the_geom
>>> AND within(occurrencies.the_geom, country.the_geom)
>>>
>>> This takes 50 sek.
>>>
>>>
>>> I have spatial index on both columns and have vacuum analyzed both  
>>> tables.
>>>
>>> If I do an "explain analyze" I get following result:
>>>
>>> Nested Loop  (cost=0.00..3715.18 rows=526 width=30) (actual
>>> time=6.311..46717.773 rows=371 loops=1)
>>> Join Filter: within("inner".the_geom, "outer".the_geom)
>>> ->  Seq Scan on country (cost=0.00..50.09 rows=609 width=13200)
>>> (actual time=0.012..1.243 rows=609 loops=1)
>>> ->  Index Scan using occurrencies_spatial_index on
>>> occurrencies(cost=0.00..6.00 rows=1 width=51) (actual  
>>> time=3.427..10.884
>>> rows=2 loops=609)
>>>       Index Cond: (occurrencies.the_geom && "outer".the_geom)
>>>       Filter: (occurrencies.the_geom && "outer".the_geom)
>>> Total runtime: 46718.838 ms
>>>
>>> I am using Postgres version 8.1
>>>
>>> Is this time OK or what am I doing wrong?
>>>
>>> /Anders Larsson
>>> Sweden
>>>
>>>
>>>
>>>
>>> _______________________________________________
>>> 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
>>
>>
>> End of postgis-users Digest, Vol 55, Issue 17
>> *********************************************
>>
>>   
>

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20070531/69f953f2/attachment.html>