[postgis] Geographic Indexing

classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view

[postgis] Geographic Indexing

Paul Ramsey-2
I have created a GiST spatial index on the 'geom' DRA table using the
following command:

create index geom_geoidx on geom using gist ( lineargeometry
gist_geometry_ops ) with (islossy);

You can do a spatial query on a bounding box with commands like this:

enable_seqscan = off;
select * from geom where lineargeometry && 'BOX3D(-147.1 50,-147

The '&&' operator is the 'overlaps' operator. It will return any feature
which has a bounding box which overlaps the specified bounding box.

The 'enable_seqscan = off' commands forces the planner to use the index.
You'll want to be judicious about using this in your day-to-day work,
because there are cases where a sequence scan is a more efficient thing
to do than an index scan (on small tables, for example, or when the
return set is going to be quite large).

(For the list, this is a usage example.)

     | Paul Ramsey
     | Refractions Research
     | Email: [hidden email]
     | Phone: (250) 885-0632

To unsubscribe from this group, send an email to:
[hidden email]


Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/