Info on GIST indexes is available here: http://www.postgresql.org/docs/8.2/static/gist.html
Some info on PostGIS and GIST is available here: http://postgis.refractions.net/documentation/manual-1.3/ (specifically http://postgis.refractions.net/documentation/manual-1.3/ch04.html#id2743 709) http://en.wikipedia.org/wiki/GiST http://osdir.com/ml/gis.postgis/2005-01/msg00032.html -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of M.A. (Thijs) van den Berg Sent: Wednesday, March 18, 2009 8:38 AM To: PostGIS Users Discussion Subject: Re: [postgis-users] Can I build anindexoncombinednon-spatial&spatialcolumns? that's exactly what I want to do! In my case I would have centroid and and integer (the integer representing the building_type and being constrained on equality) Do you have some more reference/info? Where did you learn this? On Mar 18, 2009, at 4:24 PM, Michael Smedberg wrote: > It's possible to create GIST indexes on multiple columns of mixed > types. > Here's an example of an index I use on my data: > > create index listing_search_keys_centroid on listing_search_keys USING > GIST (centroid, listing_price, num_bedrooms) where centroid is not > null; > > This application is used for searching for homes for sale- each home > has > a centroid, a listing price, and a number of bedrooms. Users may > constrain by combinations of polygon, price, and bedrooms. > > I haven't thought enough about your particular situation to know if > this > is what you want to do, but it's certainly possible. > > -----Original Message----- > From: [email protected] > [mailto:[email protected]] On Behalf Of > M.A. > (Thijs) van den Berg > Sent: Wednesday, March 18, 2009 8:08 AM > To: PostGIS Users Discussion > Subject: Re: [postgis-users] Can I build an > indexoncombinednon-spatial&spatialcolumns? > > That's what I'm doing right now! (so far the update has been running > 20 minutes) > > Basically this is replicating the spatial index idea with classic > database operators. > > Yet another possibility is to move things in 3D (and use the cube > index) having, x,y the building location, and uze the z-axis to > position each building_type at a different height... or build my own > indexer, pff... > On Mar 18, 2009, at 3:51 PM, Kemal Bayram wrote: > >> 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 > > _______________________________________________ > 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
