<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=us-ascii">
<META content="MSHTML 6.00.6000.16481" name=GENERATOR></HEAD>
<BODY>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=539482714-23092008>Its probably more efficient to do</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=539482714-23092008></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=539482714-23092008><FONT face="Times New Roman" color=#000000
size=3>UPDATE yolorta_all </FONT></SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=539482714-23092008><FONT face="Times New Roman" color=#000000
size=3> SET city = iller.iladi</FONT></SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=539482714-23092008><FONT face="Times New Roman" color=#000000 size=3>FROM
iller </FONT></SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=539482714-23092008><FONT face="Times New Roman" color=#000000
size=3> WHERE
st_coveredby(yolorta_all.the_geom,iller.the_geom) ;</FONT></SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT face="Times New Roman" color=#000000 size=3><SPAN
class=539482714-23092008></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=539482714-23092008>Hope that helps,</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=539482714-23092008>Regina</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=539482714-23092008><FONT face="Times New Roman" color=#000000
size=3></FONT> </DIV></SPAN></FONT><BR>
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left>
<HR tabIndex=-1>
<FONT face=Tahoma size=2><B>From:</B>
postgis-users-bounces@postgis.refractions.net
[mailto:postgis-users-bounces@postgis.refractions.net] <B>On Behalf Of
</B>Balkan Uraz<BR><B>Sent:</B> Tuesday, September 23, 2008 8:26
AM<BR><B>To:</B> postgis-users@postgis.refractions.net<BR><B>Cc:</B>
volkany@mobiliz.com.tr<BR><B>Subject:</B> [postgis-users] st_coveredby query
taking too much time<BR></FONT><BR></DIV>
<DIV></DIV>
<DIV dir=ltr>
<DIV>Dear All;</DIV>
<DIV> </DIV>
<DIV>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.</DIV>
<DIV> </DIV>
<DIV>All the related info is below:</DIV>
<DIV> </DIV>
<DIV>max_connections = 300<BR>shared_buffers = 256MB<BR>work_mem =
2MB<BR>maintenance_work_mem = 32MB<BR>max_fsm_pages = 153600<BR>wal_buffers =
1MB<BR>checkpoint_segments = 16<BR>effective_cache_size = 2GB</DIV>
<DIV>CPU: Quad Core Intel Xeon 2.33GHz<BR>Memory: 4GB</DIV>
<DIV>balkan_20080908_0_0_1_postgis=# explain update yolorta_all set city
=<BR>(select iladi from iller where
st_coveredby(yolorta_all.the_geom,<BR>iller.the_geom) =
'TRUE');<BR>
QUERY
PLAN<BR>-------------------------------------------------------------------------<BR> Seq
Scan on yolorta_all (cost=0.00..3458534.64 rows=1526838
width=675)<BR> SubPlan<BR> -> Seq Scan on
iller (cost=0.00..2.21 rows=1
width=7)<BR> Filter: (($0
&& the_geom) AND _st_coveredby($0, the_geom))<BR>(4 rows)</DIV>
<DIV>balkan_20080908_0_0_1_postgis=# \d
iller<BR>
Table "public.iller"<BR> Column
|
Type
|
Modifiers<BR>----------+-----------------------+-----------------------------------------<BR>------------<BR> gid
|
integer
| not null default<BR>nextval('iller_gid_seq'::regclass)<BR> plakano
|
integer
|<BR> iladi | character varying(15)
|<BR> ilceadi | character varying(20) |<BR> the_geom |
geometry
|<BR>Indexes:<BR> "iller_pkey" PRIMARY KEY, btree
(gid)<BR> "iller_gid" UNIQUE, btree (gid) CLUSTER<BR>
"iller_the_geom_gist" gist (the_geom)<BR>Check constraints:<BR>
"enforce_dims_the_geom" CHECK (ndims(the_geom) = 2)<BR>
"enforce_geotype_the_geom" CHECK (geometrytype(the_geom)
=<BR>'MULTIPOLYGON'::text OR the_geom IS NULL)<BR>
"enforce_srid_the_geom" CHECK (srid(the_geom) = 4326)</DIV>
<DIV>balkan_20080908_0_0_1_postgis=# \d
yolorta_all<BR>
Table "public.yolorta_all"<BR> Column
|
Type
|
Modifiers<BR>-------------+-----------------------+--------------------------------------<BR>---------------------<BR> gid
|
integer
| not null
default<BR>nextval('yolorta_all_gid_seq'::regclass)<BR> adi
| character varying(50)
|<BR> tipi | character
varying(25) |<BR> rnname | character
varying(10) |<BR> adiuluslar | character varying(5)
|<BR> kkno | character
varying(25) |<BR> the_geom |
geometry
|<BR> city | character
varying(50) |<BR> country | character varying(50)
|<BR> district | character varying(50)
|<BR> subdistrict | character varying(50) |<BR>Indexes:<BR>
"yolorta_all_pkey" PRIMARY KEY, btree (gid)<BR> "yolorta_gid"
UNIQUE, btree (gid) CLUSTER<BR> "yolorta_all_the_geom_gist" gist
(the_geom)<BR>Check constraints:<BR> "enforce_dims_the_geom" CHECK
(ndims(the_geom) = 2)<BR> "enforce_geotype_the_geom" CHECK
(geometrytype(the_geom) =<BR>'MULTILINESTRING'::text OR the_geom IS
NULL)<BR> "enforce_srid_the_geom" CHECK (srid(the_geom) =
4326)</DIV>
<DIV> </DIV>
<DIV>Is this time spent for the query meaningful? What can I do for faster
spatial queries in this context?</DIV>
<DIV> </DIV>
<DIV>Regards,</DIV>
<DIV> </DIV>
<DIV>Balkan.</DIV>
<DIV> </DIV>
<DIV> </DIV></DIV></BODY></HTML>
<HTML><BODY><P><hr size=1></P>
<P><STRONG>
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.
</STRONG></P></BODY></HTML>
<P><hr size=1></P>
<P><STRONG><font size="2" color="339900"> Help make the earth a greener place. If at all possible resist printing this email and join us in saving paper. </p> <p> </font></STRONG></P>