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: [email protected] > [mailto:[email protected]] 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: [email protected] > >> [mailto:[email protected]] 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: [email protected] > >>>> [mailto:[email protected]] 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: [email protected] > >>>>>> [mailto:[email protected]] On > >>>> Behalf Of > >>>>>> M.A. (Thijs) van den Berg > >>>>>> Sent: 18 March 2009 11:57 > >>>>>> To: [email protected] > >>>>>> 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 > >>>>>> [email protected] > >>>>>> http://postgis.refractions.net/mailman/listinfo/postgis-users > >>>>>> > >>>>> > >>>>> > >>>>> _______________________________________________ > >>>>> postgis-users mailing list > >>>>> [email protected] > >>>>> http://postgis.refractions.net/mailman/listinfo/postgis-users > >>>> > >>>> _______________________________________________ > >>>> postgis-users mailing list > >>>> [email protected] > >>>> http://postgis.refractions.net/mailman/listinfo/postgis-users > >>>> > >>> > >>> > >>> _______________________________________________ > >>> postgis-users mailing list > >>> [email protected] > >>> http://postgis.refractions.net/mailman/listinfo/postgis-users > >> > >> _______________________________________________ > >> postgis-users mailing list > >> [email protected] > >> http://postgis.refractions.net/mailman/listinfo/postgis-users > >> > > > > > > _______________________________________________ > > postgis-users mailing list > > [email protected] > > http://postgis.refractions.net/mailman/listinfo/postgis-users > > _______________________________________________ > postgis-users mailing list > [email protected] > http://postgis.refractions.net/mailman/listinfo/postgis-users > _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
