[postgis-users] Can I build an index oncombinednon-spatial&spatial columns?

[Kemal Bayram]

Well spatial indicies are not actually accurate because they use 32bit
floating point and work on bounding boxes, that is one assumption I
mistakenly made myself (should have RTFM properly :) So it makes sense that
you can not have a compound index.

> -----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 15:16
> To: PostGIS Users Discussion
> Subject: Re: [postgis-users] Can I build an index 
> oncombinednon-spatial&spatial columns?
> 
> I thinks you're right about the 'too many indices' , I'm 
> worried about that too. I'll have to do some test's now to 
> see what would work.  
> Thanks!
> ..anyway, one thing I've learned is that there are no 
> compound spatial/ non-spatial indices possibilities.
> 
> 
> On Mar 18, 2009, at 1:43 PM, Kemal Bayram wrote:
> 
> > For what you want to do I don't think partial indexs are the the 
> > solution as you would have too many indicies.  You don't want the 
> > query taking more time going through the metadata then the actual 
> > query.
> >
> > Postgres will use a separate index you have on building 
> type, infact 
> > if you look at EXPLAIN the best case costs are lower with 
> an index on 
> > building_type than on a partial index.  And like I said if 
> performance 
> > of this query is critical you can cluster on the building_type.
> >
> > You really need to play around with your queries to see 
> what is best.
> >
> >> -----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 14:08
> >> To: PostGIS Users Discussion
> >> Subject: Re: [postgis-users] Can I build an index on 
> >> combinednon-spatial&spatial columns?
> >>
> >> Thanks, that's true. I think the performance benefit with be good 
> >> because I have approx 10.000 building_type's (although, 
> that's a lot 
> >> of indices to choose between). A typical distance query for a 
> >> specific building_type will result in 10-100 matches. Not using 
> >> partial indexing will thus result in appox 500.000  rows for each 
> >> query (all building types), which I would then have to filter on 
> >> building_type, reducing the resultset with a factor 10.000
> >>
> >> Giving it some more thought, I think splitting the building table 
> >> into individual building_type tables (and attaching 
> spatial indices 
> >> to
> >> those) might be the best solution.
> >>
> >> From a technical point, I think the postgresql CREATE INDEX should 
> >> support multiple USING to allow for mixed patial / non-spatial 
> >> indices, something like this:
> >>
> >> CREATE INDEX blabla ON buildings (USING HASH(building_type), USING
> >> GIST(the_geom))
> >>
> >> internally that could be rewritten as CREATE INDEX 
> blabla_hash_value1 
> >> ON buildings USING
> >> GIST(the_geom) WHERE hash(building_type)=value1 CREATE INDEX
> >> blabla_hash_value2 ON buildings USING GIST(the_geom) WHERE
> >> hash(building_type)=value2 ..
> >>
> >>
> >> On Mar 18, 2009, at 12:43 PM, Kemal Bayram wrote:
> >>
> >>> 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
> >>>>
> >>>
> >>>
> >>> _______________________________________________
> >>> 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
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>