[postgis-users] Can I build an index on combined non-spatial&spatial columns?

[M.A. (Thijs) van den Berg]

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