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: [email protected]
[mailto:[email protected]] 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: [email protected]
[mailto:[email protected]] 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: [email protected]
[mailto:[email protected]] 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: [email protected]
[mailto:[email protected]] 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: [email protected]
[mailto:[email protected]] 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: [email protected]
[mailto:[email protected]] 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: [email protected]
[mailto:[email protected]] On
Behalf Of
M.A. (Thijs) van den Berg
Sent: 18 March 2009 11:57
To: [email protected]
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
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-
users
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
