[postgis-users] Can I build an index on combined non-spatial&spatial columns?

[Kemal Bayram]

Well according to EXPLAIN a partial index is taken into account, how much of
a performance benefit you gain ultimately depends on how many records lie
within your average distance.

If your data set is relatively static you may also want to consider
clustering your table too.

> -----Original Message-----
> From: postgis-users-bounces at postgis.refractions.net 
> [mailto:postgis-users-bounces at postgis.refractions.net] On 
> Behalf Of M.A. (Thijs) van den Berg
> Sent: 18 March 2009 13:00
> To: PostGIS Users Discussion
> Subject: Re: [postgis-users] Can I build an index on combined 
> non-spatial&spatial columns?
> 
> Thanks :)
> 
> Does PostGIS support partial indices? 
> http://www.postgresql.org/docs/8.3/interactive/indexes-partial.html
> 
> If so.. I could do something like this:
> 
> CREATE INDEX building_type_spindx_school ON buildings USING 
> GIST(the_geom) WHERE building_type='School';
> 
> CREATE INDEX building_type_spindx_bar
> ON buildings
> USING GIST(the_geom)
> WHERE building_type='Bar';
> 
> ...
> Yet another option would be to partition the buildings table 
> into child tables per building_type (ie have child table 
> containing all the schools, another child table having all 
> the bars), and have individua spatial indices on the child 
> tables. Is that an option?
> 
> 
> 
> 
> On Mar 18, 2009, at 11:36 AM, Kemal Bayram wrote:
> 
> > I don't know about combining indexes but you want to 
> include a spatial 
> > operator such as && in addition to Distance so that you can take 
> > advantage of an index on "the_geom".
> >
> >> -----Original Message-----
> >> From: postgis-users-bounces at postgis.refractions.net
> >> [mailto:postgis-users-bounces at postgis.refractions.net] On 
> Behalf Of 
> >> M.A. (Thijs) van den Berg
> >> Sent: 18 March 2009 11:57
> >> To: postgis-users at postgis.refractions.net
> >> Subject: [postgis-users] Can I build an index on combined 
> non-spatial 
> >> &spatial columns?
> >>
> >> Suppose I want to speedup the following  type of queries with an 
> >> index:
> >>
> >> SELECT the_geom, building_name
> >>  FROM buildings
> >>  WHERE Distance(the_geom, 'POINT(100312 102312)') < 5000  AND 
> >> building_type = 'School'
> >>
> >> Is that possible, and if not, why not? My current option 
> is to write 
> >> my own index storage in C++, using a hashmap to filter 
> building_type, 
> >> and build separate spatial indices for each building type. 
> Can I do 
> >> something similar in postgresql?
> >> Would my only option be to split the building_types into separate 
> >> tables?
> >> _______________________________________________
> >> postgis-users mailing list
> >> postgis-users at postgis.refractions.net
> >> http://postgis.refractions.net/mailman/listinfo/postgis-users
> >>
> >
> >
> > _______________________________________________
> > postgis-users mailing list
> > postgis-users at postgis.refractions.net
> > http://postgis.refractions.net/mailman/listinfo/postgis-users
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>