[udig-devel] Re: Can uDig plot PostGIS data from an SQL view?

["Gary Lucas" <gwlucas@xxxxxxxxxxxxx>]

Cory,

I think that I basically already tried what you suggested.
Alas, to no avail...

I hadn't thought of this before my last email, but later it
occured to me to insert an entry into the geometry_columns table
so that it matched the record from the original table on which the
view is based (with the name of the view appearing in
the "f_table_name" column).  Still nothing
plotted.  It did make a difference in that when I ran uDig and 
brought up the "Resource Summary" it now shows the 
correct value for the Coordinate Reference System (CRS) which,
in this case, is just EPSG:WGS84 (SRID=4326). On the other hand, the 
values in the Bounds entry are rather suspicious being
(-494.9, -79.3), (483.1,144.3)    They seem to change everytime
I run the program.

Could there be another table beside geometry_columns that
needs an additional entry?

Other details...  I am running uDig 1.1.RC4.   As of this writing,
RC5 hasn't shown up at the uDig web site yet, so I'll download
it tomorrow if it's ready and then see if something got fixed
between releases.

Also, I didn't name my geometry column "the_geom" which seems
to be a prefered default in some of the GeoTools code.

Gary


---
Gary W. Lucas, Senior Software Engineer
Sonalysts, Inc
215 Parkway North
Waterford, CT 06320
(860) 326-3682




Message: 6
Date: Thu, 02 Nov 2006 09:55:27 -0800
From: Cory Horner <chorner@xxxxxxxxxxxxxxx>
Subject: Re: [udig-devel] Can uDig plot PostGIS data from an SQL view?
To: User-friendly Desktop Internet GIS
<udig-devel@xxxxxxxxxxxxxxxxxxxxx>
Message-ID: <454A310F.7050202@xxxxxxxxxxxxxxx>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed

Cory Horner wrote:

> Gary Lucas wrote:
>
>> I am trying to use uDig 1.1.RC4 to view a subset of records from a 
>> Postgres table. The table contains 50 or 60 thousand records, I'm try 
>> to view a few hundred. I am interested in doing this on a read-only 
>> basis, using uDig as a data-inspection tool, so editing doesn't come 
>> into play. I didn't see any way to accomplish this through the style 
>> specifications (putting aside overhead issues, the query I use to 
>> develop the subset depends on the SQL LIKE operation). Anyway, I had 
>> the idea of creating an
>> SQL View and reading the data from the view rather than from the 
>> actual table.
>>
>> The uDIG GUI recognizes the SQL view and allows me to add it to a map.
>> Unfortunately, uDig does not appear to pull back any data. Nothing 
>> plots and
>> I get a zero feature count.
>>
>> Did I miss a procedure? Do I have to do something extra in PostGIS to
>> enable it to identify the geometry attribute in the SQL view? 
>
Props to Jesse for the absolutely gorgeous feature in 1.1.RC5 that tells 
the user they don't have permission to read a table :)

Here are the steps I used to make this work:

CREATE VIEW grnf_view AS SELECT * FROM grnf059r06a_e WHERE type = 'RD';

GRANT SELECT ON TABLE grnf_view TO test;

INSERT INTO geometry_columns VALUES 
('','public','grnf_view','the_geom',2,4269,'MULTILINESTRING');

Cheers,
Cory.