[postgis-users] point_ops with GiST PostGIS Spatial Index

[BladeOfLight16]

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 <ktambade at 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-bounces at lists.osgeo.org [mailto:
> postgis-users-bounces at lists.osgeo.org] *On Behalf Of *BladeOfLight16
> *Sent:* Wednesday, June 05, 2013 2:29 PM
> *To:* PostGIS Users Discussion; pgsql-general at 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-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20130605/b09477b6/attachment.html>