[postgis-users] Spatial indexes

[R. van Twisk]

James,

will a temporary table work for you?

Ries
> Hi Markus, thanks for the response.
>
> I have a table set up detailing tree preservation orders containing 4912
> records. There are several attribute columns and a geometry column for
> each record. There is also a spatial index attached to the table and if
> I connect to the database with Cadcorp I can see and successfully load
> the map features.
>
> In most cases there are several map features that all form part of the
> same preservation order, for instance there might be a couple of
> individual trees, a group and a small woodland which all form part of a
> single preservation order. I need to aggregate these map features into a
> single multipart polygon.
>
> I used the following to create a view which I thought would provide me
> with a nice aggregated view of the tree preservation data:
>
> SELECT collect (tree_preservation.geometry) AS collect FROM
> tree_preservation
> GROUP BY tree_preservation.tpo_no;
>
> I've got 2 problems with this view. First I'm not sure of the SQL syntax
> required to pick up columns other than the geometry column. It would be
> useful to pick up the TPO number as well. Second, I can't 'see' this
> view in my Cadcorp software at all. 
>
> I have successfully created another view, added a line to
> geometry_tables and I can then see the table in Cadcorp and load it
> albeit without a spatial index. 
>
> Unfortunately I can't provide details of any SQL statements coming from
> Cadcorp because the tables are loaded through a wizard dialogue. In this
> dialogue I'm presented with a list of tables containing geometry that
> Cadorp can use. Cadcorp also knows what tables / views have spatial
> indexes associated because it reveals this in the wizard.
>
> I've got a feeling that I'm going to get stuck on this one!! This is a
> shame because postgis seems to be operating an awful lot quicker than
> other DBMS that I use for spatial stuff.
>
> Thanks
>
> James
>
> James Rutter
> GIS Manager
> Surrey Heath Borough Council
> Surrey Heath House
> Knoll Road
> Camberley
> GU15 3HD
> Tel: 01276 707200
> Fax: 01276 707516
>
> -----Original Message-----
> From: postgis-users-bounces at postgis.refractions.net
> [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
> Markus Schaber
> Sent: 18 September 2006 14:36
> To: PostGIS Users Discussion
> Subject: Re: [postgis-users] Spatial indexes
>
> Hi, James,
>
> James Rutter wrote:
>   
>> I've created a view on a spatial table and added a line into the
>> geometry_columns table to reference the view. The problem I've got is
>> trying to pass on to the client (in my case Cadcorp GIS system)
>> information about a spatial index. I know that a view is essentially
>>     
> an
>   
>> 'on the fly' reconstruction of a query on a table but is there any way
>> to associate a spatial index with a view?
>>     
>
> This is not really a PostGIS problem, but a general PostgreSQL problem.
>
> Currently, PostgreSQL cannot create indices on views. However, it will
> happily use indices on the underlying tables whenever it thinks they can
> help to accelerate the query.
>
> Maybe you can create such indices (possibly functional ones)?
>
> You could give us more details about your view and the queries typically
> runing against it, so we could help more.
>
> HTH,
> Markus
>