[postgis-users] st_coveredby query taking too much time

[Obe, Regina]

Its probably more efficient to do
 
UPDATE yolorta_all  
    SET city = iller.iladi
FROM iller 
    WHERE  st_coveredby(yolorta_all.the_geom,iller.the_geom) ;
 
Hope that helps,
Regina
 

________________________________

From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
Balkan Uraz
Sent: Tuesday, September 23, 2008 8:26 AM
To: postgis-users at postgis.refractions.net
Cc: volkany at mobiliz.com.tr
Subject: [postgis-users] st_coveredby query taking too much time


Dear All;
 
I have been trying to process and update query (by st_coveredby) using 2
spatial tables. It seems that everything goes on track except that it
has been taking more than 2 hours right now.
 
All the related info is below:
 
max_connections = 300
shared_buffers = 256MB
work_mem = 2MB
maintenance_work_mem = 32MB
max_fsm_pages = 153600
wal_buffers = 1MB
checkpoint_segments = 16
effective_cache_size = 2GB
CPU: Quad Core Intel Xeon 2.33GHz
Memory: 4GB
balkan_20080908_0_0_1_postgis=# explain update yolorta_all set city =
(select iladi from iller where st_coveredby(yolorta_all.the_geom,
iller.the_geom) = 'TRUE');
                              QUERY PLAN
------------------------------------------------------------------------
-
 Seq Scan on yolorta_all  (cost=0.00..3458534.64 rows=1526838 width=675)
  SubPlan
    ->  Seq Scan on iller  (cost=0.00..2.21 rows=1 width=7)
          Filter: (($0 && the_geom) AND _st_coveredby($0, the_geom))
(4 rows)
balkan_20080908_0_0_1_postgis=# \d iller
                                 Table "public.iller"
 Column  |         Type          |                      Modifiers
----------+-----------------------+-------------------------------------
----
------------
 gid      | integer               | not null default
nextval('iller_gid_seq'::regclass)
 plakano  | integer               |
 iladi    | character varying(15) |
 ilceadi  | character varying(20) |
 the_geom | geometry              |
Indexes:
   "iller_pkey" PRIMARY KEY, btree (gid)
   "iller_gid" UNIQUE, btree (gid) CLUSTER
   "iller_the_geom_gist" gist (the_geom)
Check constraints:
   "enforce_dims_the_geom" CHECK (ndims(the_geom) = 2)
   "enforce_geotype_the_geom" CHECK (geometrytype(the_geom) =
'MULTIPOLYGON'::text OR the_geom IS NULL)
   "enforce_srid_the_geom" CHECK (srid(the_geom) = 4326)
balkan_20080908_0_0_1_postgis=# \d yolorta_all
                                  Table "public.yolorta_all"
  Column    |         Type          |                         Modifiers
-------------+-----------------------+----------------------------------
----
---------------------
 gid         | integer               | not null default
nextval('yolorta_all_gid_seq'::regclass)
 adi         | character varying(50) |
 tipi        | character varying(25) |
 rnname      | character varying(10) |
 adiuluslar  | character varying(5)  |
 kkno        | character varying(25) |
 the_geom    | geometry              |
 city        | character varying(50) |
 country     | character varying(50) |
 district    | character varying(50) |
 subdistrict | character varying(50) |
Indexes:
   "yolorta_all_pkey" PRIMARY KEY, btree (gid)
   "yolorta_gid" UNIQUE, btree (gid) CLUSTER
   "yolorta_all_the_geom_gist" gist (the_geom)
Check constraints:
   "enforce_dims_the_geom" CHECK (ndims(the_geom) = 2)
   "enforce_geotype_the_geom" CHECK (geometrytype(the_geom) =
'MULTILINESTRING'::text OR the_geom IS NULL)
   "enforce_srid_the_geom" CHECK (srid(the_geom) = 4326)
 
Is this time spent for the query meaningful? What can I do for faster
spatial queries in this context?
 
Regards,
 
Balkan.
 
 


-----------------------------------------
The substance of this message, including any attachments, may be
confidential, legally privileged and/or exempt from disclosure
pursuant to Massachusetts law. It is intended
solely for the addressee. If you received this in error, please
contact the sender and delete the material from any computer.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20080923/067ac36f/attachment.html>