<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META content="text/html; charset=ISO-8859-1" http-equiv=content-type>
<META name=GENERATOR content="MSHTML 8.00.7600.16625"></HEAD>
<BODY bgColor=#ffffff text=#000000>
<DIV dir=ltr align=left><SPAN class=296485221-17092010><FONT size=2
face=Arial>Gaston, </FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=296485221-17092010><FONT size=2
face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=296485221-17092010><FONT size=2
face=Arial>Try</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=296485221-17092010><FONT size=2
face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=296485221-17092010>SELECT zone.*
</SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=296485221-17092010>FROM
zone </SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=296485221-17092010> INNER
JOIN (SELECT geometry FROM othertable WHERE id=1) AS b</SPAN></DIV>
<DIV dir=ltr align=left><SPAN
class=296485221-17092010> ON
ST_Intersects(zone.geometry, b.geometry);</SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=296485221-17092010></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=296485221-17092010><FONT size=2
face=Arial>Also make sure you have a spatial index in place on your geometry
fields</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=296485221-17092010><FONT size=2
face=Arial></FONT></SPAN> </DIV>
<DIV><SPAN class=296485221-17092010></SPAN>Leo</DIV>
<DIV><SPAN class=296485221-17092010></SPAN><SPAN
class=296485221-17092010></SPAN><A href="http://www.postgis.us"><FONT
color=#000000>h<SPAN
class=296485221-17092010>ttp://www.postgis.us</A></SPAN></FONT></DIV>
<DIV><SPAN class=296485221-17092010></SPAN><BR> </DIV>
<DIV dir=ltr lang=en-us class=OutlookMessageHeader align=left>
<HR tabIndex=-1>
<FONT size=2 face=Tahoma><B>From:</B>
postgis-users-bounces@postgis.refractions.net
[mailto:postgis-users-bounces@postgis.refractions.net] <B>On Behalf Of
</B>Gastón Lucero<BR><B>Sent:</B> Friday, September 17, 2010 4:44
PM<BR><B>To:</B> postgis-users@postgis.refractions.net<BR><B>Subject:</B>
[postgis-users] Spatial queries using @ operator<BR></FONT><BR></DIV>
<DIV></DIV>Hello,i´m from argentina, so excuse my english. <BR>My problem is i
have a table with 500000 record,polygons and points type <SPAN
id=result_box><SPAN style="BACKGROUND-COLOR: rgb(255,255,255)" title="">and i
have to find objects which intersects with an boundary in
particular</SPAN></SPAN><BR><BR>Example of ,my query<BR>select * from zone
where geometry @ (select geometry from othertable where id=1) <BR><BR>My
table definition:<BR>CREATE TABLE "zone"<BR>(<BR> zoneid integer NOT NULL
DEFAULT nextval('zonas_id_seq'::regclass),<BR> "name" text,<BR>
buffer integer,<BR> description text,<BR> layerid integer,<BR>
geometry geometry,<BR> color integer,<BR> CONSTRAINT zone_pk PRIMARY
KEY (zoneid),<BR> CONSTRAINT enforce_dims_geometry CHECK
(st_ndims(geometry) = 2),<BR> CONSTRAINT enforce_srid_geometry CHECK
(st_srid(geometry) = 4326)<BR>)<BR>WITH (<BR> OIDS=FALSE<BR>);<BR>ALTER
TABLE "zone" OWNER TO postgres;<BR><BR>-- Index: idx_zone_geometry<BR><BR>--
DROP INDEX idx_zone_geometry;<BR><BR>CREATE INDEX idx_zone_geometry<BR> ON
"zone"<BR> USING gist<BR> (geometry);<BR><BR>-- Index:
idx_zone_zoneid<BR><BR>-- DROP INDEX idx_zone_zoneid;<BR><BR>CREATE INDEX
idx_zone_zoneid<BR> ON "zone"<BR> USING btree<BR>
(zoneid);<BR><BR>I have test of differents ways to accelerate my query
with other functions like st_distance,st_dwithin,etc<BR>but do not know which to
use<BR><BR>Thanks<BR> <BR><BR>
<TABLE>
<TBODY>
<TR>
<TD bgColor=#ffffff><PRE>Disclaimer:
This email and any attachments thereof may contain confidential, privileged, proprietary, or otherwise private information. This email is intended solely for the use of the individual to whom it is addressed. If you are not the intended recipient of the email and its attachments please inform the sender immediately and do not disclose the contents to any other person, use it for any purpose or store or copy the information in any way and delete this e-mail and its attachments from your system. Any views or opinions expressed are solely those of the author.</PRE></TD></TR></TBODY></TABLE></BODY></HTML>