[postgis-users] Spatial queries using @ operator

[Paragon Corporation]

Gaston, 
 
Try
 
SELECT zone.* 
FROM  zone 
    INNER JOIN (SELECT geometry FROM othertable WHERE id=1) AS b
      ON ST_Intersects(zone.geometry, b.geometry);
 
Also make sure you have a spatial index in place on your geometry fields
 
Leo
 <http://www.postgis.us> http://www.postgis.us

 
  _____  

From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Gastón
Lucero
Sent: Friday, September 17, 2010 4:44 PM
To: postgis-users at postgis.refractions.net
Subject: [postgis-users] Spatial queries using @ operator


Hello,i´m from argentina, so excuse my english. 
My problem is i have a table with 500000 record,polygons and points type and
i have to find objects which intersects with an boundary in particular

Example of ,my query
select * from zone where  geometry @ (select geometry from othertable where
id=1) 

My table definition:
CREATE TABLE "zone"
(
  zoneid integer NOT NULL DEFAULT nextval('zonas_id_seq'::regclass),
  "name" text,
  buffer integer,
  description text,
  layerid integer,
  geometry geometry,
  color integer,
  CONSTRAINT zone_pk PRIMARY KEY (zoneid),
  CONSTRAINT enforce_dims_geometry CHECK (st_ndims(geometry) = 2),
  CONSTRAINT enforce_srid_geometry CHECK (st_srid(geometry) = 4326)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE "zone" OWNER TO postgres;

-- Index: idx_zone_geometry

-- DROP INDEX idx_zone_geometry;

CREATE INDEX idx_zone_geometry
  ON "zone"
  USING gist
  (geometry);

-- Index: idx_zone_zoneid

-- DROP INDEX idx_zone_zoneid;

CREATE INDEX idx_zone_zoneid
  ON "zone"
  USING btree
  (zoneid);

I  have test of differents ways to accelerate my query with other functions
like st_distance,st_dwithin,etc
but do not know which to use

Thanks
 



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.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20100917/fa1a6030/attachment.html>