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 <[EMAIL PROTECTED]>
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
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
------------------------------
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
End of postgis-users Digest, Vol 55, Issue 17
*********************************************
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
