Thread: point_ops with GiST PostGIS Spatial Index

point_ops with GiST PostGIS Spatial Index

From
BladeOfLight16
Date:
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.

Re: [postgis-users] point_ops with GiST PostGIS Spatial Index

From
"Tambade, Kedar"
Date:

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.

Re: [postgis-users] point_ops with GiST PostGIS Spatial Index

From
BladeOfLight16
Date:
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@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@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.


_______________________________________________
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users