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

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

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