Also thinking about your problem a little bit more, you could divide your space into sub divisions giving each space a unique id. If you then assign each building the unique id of its correspending sub division you can create a compound index on building_type and sub_division_id. For your query you would search on the relevant sub_division and building_type along with distance. Depending on how equally distanced out your points are you'd probably want to divide the more dense sub divisions into a quadtree.
> -----Original Message----- > From: [email protected] > [mailto:[email protected]] On > Behalf Of Kemal Bayram > Sent: 18 March 2009 16:36 > To: 'PostGIS Users Discussion' > Subject: RE: [postgis-users] Can I build an > indexoncombinednon-spatial&spatial columns? > > 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 > _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
