Create the gist index on table containing points using the following syntax:
CREATE INDEX <index name> ON <table name> USING GIST (<geometry column name>);
After the index is created use the criteria st_contains(polygon,point) in the where clause of select statement:
Select * from <tablename> where st_contains(polygon, point) ;
Regards,
Kedar Tambade
This electronic mail message and any attached files contain information intended for the exclusive use of the individual or entity to whom it is addressed and may contain information that is propriety, privileged, confidential and/or exempt from disclosure under applicable law. If you are not the intended recipient, you are hereby notified that any viewing, copying, disclosure or distribution of this information may be subject to legal restriction or sanction. Please notify the sender, by electronic mail or telephone, of any unintended recipients and delete the original message without making any copies.
From: postgis-users-bounces@lists.osgeo.org [mailto:postgis-users-bounces@lists.osgeo.org] On Behalf Of BladeOfLight16
Sent: Wednesday, June 05, 2013 2:29 PM
To: PostGIS Users Discussion; pgsql-general@postgresql.org
Subject: [postgis-users] point_ops with GiST PostGIS Spatial Index
I posted this question on StackOverflow, and the only person to answer recommended I ask these lists for more details and link to the question:
http://stackoverflow.com/questions/16927331/postgresql-point-ops-with-gist-postgis-spatial-index
My question is:
The 9.0 release notes for PostgreSQL states the following change:
Add point_ops operator class for GiST (Teodor Sigaev)
This feature permits GiST indexing of point columns. The index can be used for several types of queries such as point <@ polygon (point is in polygon). This should make many PostGIS queries faster.
I have a very large table (millions of rows) with a GEOMETRY(POINT,[SRID]) column that I sometimes compare to polygons. Do I need to do anything to enable this when creating the index? Do I have to use the operator indicated, or would this work with ST_Contains which uses && internally?
Thanks for any help.