[postgis-users] Spatial indexes

[James Rutter]

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
-- 
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf.     | Software Development GIS

Fight against software patents in Europe! www.ffii.org
www.nosoftwarepatents.org

_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
_____________________________________________________________________________
This email and any attachments are intended for the addressee only.
The information contained in this email is confidential and may be 
legally privileged.  If you are not the intended recipient, the use 
of the information contained in this email or any disclosure, copying
or distribution is prohibited and may be unlawful. If you have received
this email in error please notify the sender or mailadmin at surreyheath.gov.uk. 

Surrey Heath Borough Council reserves the right to monitor all incoming and 
outgoing email to ensure compliance with current procedures. This email has 
been checked for computer viruses prior to sending, but it is also your
responsibility to virus check the email upon receipt.

www.surreyheath.gov.uk
_____________________________________________________________________________