Pardon my lack of specificity. I'm familiar with spatial indexes and at least somewhat familiar with ST_Contains and its internal bounding box check. Maybe it would help to clarify why I found this point_ops operator class so interesting. Currently, my queries are often getting bad estimates for the number of rows the spatial index will return. On a sample set of data (roughly 500K rows), EXPLAIN ANALYZE will show that the estimator thinks it will return 1 row when it actually returns over 10,000. I've been meaning to come up with a SSCCE to report as an issue but haven't had time. (I still intend to, when I can.) So when I saw this, I thought maybe it's something I can implement quickly to try out and see if it changes anything. So I'm trying to figure out what I need to do to try it out (if it's even possible).
So when that index is created, is PostgreSQL/PostGIS somehow figuring out that my geometry consists of points only and using the point_ops operator class, or is it just using the operator class that goes with && (bounding box overlap operator), or are those operators in the same class? I'm pretty much lost on which operators and indexes go with what classes. (point_ops is the only operator class I've even heard of.) Thanks again. On Wed, Jun 5, 2013 at 2:50 PM, Tambade, Kedar <ktamb...@cghtech.com> wrote: > 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-boun...@lists.osgeo.org [mailto: > postgis-users-boun...@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<http://www.postgresql.org/docs/9.2/static/release-9-0.html>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<http://postgis.refractions.net/docs/ST_Contains.html>which uses > && internally?**** > > Thanks for any help.**** > > _______________________________________________ > postgis-users mailing list > postgis-us...@lists.osgeo.org > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users > >
