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

Kemal Bayram kemal at cins.co.uk
Wed Mar 18 07:51:33 PDT 2009 

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: postgis-users-bounces at postgis.refractions.net 
> [mailto:postgis-users-bounces at postgis.refractions.net] 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: 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
> > 
> 
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>

More information about the postgis-users mailing list