[postgis-users] Re: Slow spatial join
anla2973 at student.uu.se
Thu May 31 10:58:50 PDT 2007
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
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
I'm using windows XP sp1. and XP sp2
All my data is created with SRID -1 (does this matter for speed?)
>> 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.
>> On 20-May-07, at 1:36 AM, Anders Larsson wrote:
>>> I am trying to join some points with regions.
>>> I have one table(occurrencies) with 900 points and another table
>>> 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
>>> 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
>>> 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
>>> postgis-users mailing list
>>> postgis-users at postgis.refractions.net
>> postgis-users mailing list
>> postgis-users at postgis.refractions.net
>> End of postgis-users Digest, Vol 55, Issue 17
-------------- next part --------------
An HTML attachment was scrubbed...
More information about the postgis-users