[postgis-users] cursor

[Paragon Corporation]

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: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
Alexander Strunck
Sent: Friday, September 11, 2009 5:49 AM
To: postgis-users at postgis.refractions.net
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
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users