[postgis-users] Can I build anindexoncombinednon-spatial&spatialcolumns?

Wed Mar 18 09:14:25 PDT 2009

Thanks
I'm going to try this multi column index approach, see how it performs!
Apart from the question in the osdir post, none of those links  
mentions compound indices though. I did a lot of searching myself, but  
it's very hard to find any example of a compound spatial/non-spatial  
index. Some example have spatial/non-spatial WHERE clauses, but non  
have compound indices..

Best,
Thijs
On Mar 18, 2009, at 4:53 PM, Michael Smedberg wrote:

> 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: 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: 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: 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: 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: 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
>>>>
>>>
>>>
>>> _______________________________________________
>>> 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