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

Michael Smedberg Michael.Smedberg at redfin.com
Wed Mar 18 08:53:30 PDT 2009 

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

More information about the postgis-users mailing list