Alex, Here are some questions/suggestions
1) If you are running large datasets and are starting out, try to start with the newer versions -- lots of speed improvements have been made in PostGIS 1.4+ (some in later 1.3+) such as prepared geometries and so forth Do a SELECT postgis_full_version(); -- gives the postgis version SELECT version(); --gives you postgresql version 2) I presume you have spatial indexes on your geometry fields -- if not put them in and then VACUUM ANALYZE osm_poi; .. other tables 3) Why are you doing this? You probably have a valid reason -- just wondering why. The reason I ask is generally speaking when people do queries like this they either want to bulk load for a different use or they don't know any better One generally runs a contains query by limiting one side by a non-spatial field such as the gid or a bounding box so that even when querying huge tables, the number of results is quite limited and pretty fast to compute (your tables aren't really that big in size actually -- but your polygons may be huge which will affect speed a lot as they would have huge bounding boxes). It looks like you are trying to match everything with everything else by containment. You might have slightly better luck with st_intersects or ST_Dwithin(a,b,0.0000001) since you are dealing with point and polygon geometries it will yield the same answer 4) Run an explain EXPLAIN your_query_here Leo -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of Alexander Strunck Sent: Friday, September 11, 2009 5:49 AM To: [email protected] Subject: [postgis-users] cursor Hello i am an newby and i am trying to make a contains query. the problem is that my tables are to big to pull the query in one (248000 polygon and 105000 points). my original query: create table osm_in_50 as SELECT gr.gid, gr.name, gr.feattyp, b.id FROM buffer_50 AS gr, osm_poi AS b WHERE Contains ( gr.setsrid, b.geom_900913 ) = TRUE; i tried to construct a cursor for this and it worked on a small table but for the big one it doesnt seem to work. it was running for allmost 12 hoers: declare my_cursor cursor for select * from buffer_50; FETCH FORWARD 1000 FROM my_cursor; declare my_cursor2 cursor for select *from osm_poi; FETCH FORWARD 1000 FROM my_cursor2; create table osm_in_50 as SELECT gr.gid, gr.name, gr.feattyp, b.id FROM buffer_50 AS gr, osm_poi AS b WHERE Contains ( gr.setsrid, b.geom_900913 ) = TRUE; close my_cursor2; close my_cursor; can someone please help me! alex -- Jetzt kostenlos herunterladen: Internet Explorer 8 und Mozilla Firefox 3 - sicherer, schneller und einfacher! http://portal.gmx.net/de/go/atbrowser _______________________________________________ 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
