Thread: Re: [GENERAL] GiST: Need ideas on how to minimise data in a GiST index
Re: [GENERAL] GiST: Need ideas on how to minimise data in a GiST index
From
"Mark Cave-Ayland"
Date:
Hi Tom, > "Mark Cave-Ayland" <m.cave-ayland@webbased.co.uk> writes: > > However, each database column also has a spatial reference system > > identifier (SRID) that specifies the coordinate system the > geometry is > > in. What should happen is that given two geometries or > bounding boxes, > > an error should be generated whenever the SRIDs do not match. > > Offhand I'm not sure why you think the index should solve > this. It seems more like grist for a CHECK constraint > applied to the table. Yes, there is already a check constraint on the column that quite nicely handles inserts/deletions etc. The problem occurs when someone tries to query an area in a form like: select * from geomtable where geom && BOX2D('1.0000 1.000, 2.000 2.000', -2) (where say -2 is the SRID and geom has a SRID of -1). In this case the BOX2D (which is the area you are trying to query) gets cast into a BOX based on its bounding box to perform the query with no way of recognising the SRIDs are different :( > One way to attack it without bloating the index is to > consider the index as lossy, meaning that the actual > geometric operator must be reapplied on each row identified > by the index. Then the operator can compare SRIDs, but you > need not store SRIDs in the index. This sounds exactly as if it would solve the problem. Have you got any pointers to documentation on this, and more specifically on the PostgreSQL implementation of GiST? (I'm guessing this is a GiST only extension). Has it been available since PostgreSQL 7.1 aswell? Cheers, Mark. --- Mark Cave-Ayland Webbased Ltd. Tamar Science Park Derriford Plymouth PL6 8BX England Tel: +44 (0)1752 764445 Fax: +44 (0)1752 764446 This email and any attachments are confidential to the intended recipient and may also be privileged. If you are not the intended recipient please delete it from your system and notify the sender. You should not copy it or use it for any purpose nor disclose or distribute its contents to any other person.
"Mark Cave-Ayland" <m.cave-ayland@webbased.co.uk> writes: > This sounds exactly as if it would solve the problem. Have you got any > pointers to documentation on this, and more specifically on the > PostgreSQL implementation of GiST? (I'm guessing this is a GiST only > extension). Has it been available since PostgreSQL 7.1 aswell? No, lossiness is a generic index property. There's been some level of support for it since Berkeley days, although we improved the flexibility in 7.2 --- we now think of lossiness as a property associated with specific operators rather than a whole index. That would probably not matter for this particular situation, since you'd be wanting to mark all the operators lossy anyway. You can read about this stuff in the PG Programmer's Guide, "Interfacing Extensions To Indexes". I'd start with the 7.3 version and then work backwards. regards, tom lane