Can uDig plot PostGIS data from an SQL view?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

Can uDig plot PostGIS data from an SQL view?

Gary W. Lucas
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?


---
Gary W. Lucas, Senior Software Engineer
Sonalysts, Inc
215 Parkway North
Waterford, CT 06320
(860) 326-3682
_______________________________________________
User-friendly Desktop Internet GIS (uDig)
http://udig.refractions.net
http://lists.refractions.net/mailman/listinfo/udig-devel
Reply | Threaded
Open this post in threaded view
|

Re: Can uDig plot PostGIS data from an SQL view?

Piebe de Vries
To enable spatial operations on a  view, you have to add a record to the
geometry_columns table in PostGIS describing the view. However, last
time  I tried (with a previous version of uDig) uDig was not able to
show the view even if this was properly done. I do not know why.

piebe

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?
>
>
> ---
> Gary W. Lucas, Senior Software Engineer
> Sonalysts, Inc
> 215 Parkway North
> Waterford, CT 06320
> (860) 326-3682
> _______________________________________________
> User-friendly Desktop Internet GIS (uDig)
> http://udig.refractions.net
> http://lists.refractions.net/mailman/listinfo/udig-devel
>
>
>
>  


--
--------------------------- ----------------------------
  Piebe de Vries  [hidden email]
  Geodan IT b.v.  Tel: +31 (0)73 - 6925 151
  President Kennedylaan 1  Fax: +31 (0)73 - 5711 333
  1079 MB Amsterdam (NL)  http://www.geodan.nl
--------------------------- ----------------------------



_______________________________________________
User-friendly Desktop Internet GIS (uDig)
http://udig.refractions.net
http://lists.refractions.net/mailman/listinfo/udig-devel
Reply | Threaded
Open this post in threaded view
|

Re: Can uDig plot PostGIS data from an SQL view?

Cory Horner-2
In reply to this post by Gary W. Lucas
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?
>  
>
Hi Gary,

I'll have a look at this right now and get back to the list today or
tomorrow detailing what the problem is and if a quick fix can be applied.

Cheers,
Cory.
_______________________________________________
User-friendly Desktop Internet GIS (uDig)
http://udig.refractions.net
http://lists.refractions.net/mailman/listinfo/udig-devel
Reply | Threaded
Open this post in threaded view
|

Re: Can uDig plot PostGIS data from an SQL view?

Cory Horner-2
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.
_______________________________________________
User-friendly Desktop Internet GIS (uDig)
http://udig.refractions.net
http://lists.refractions.net/mailman/listinfo/udig-devel
Reply | Threaded
Open this post in threaded view
|

Re: Can uDig plot PostGIS data from an SQL view?

Gary W. Lucas
In reply to this post by Gary W. Lucas

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 <[hidden email]>
Subject: Re: [udig-devel] Can uDig plot PostGIS data from an SQL view?
To: User-friendly Desktop Internet GIS
<[hidden email]>
Message-ID: <[hidden email]>
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.
_______________________________________________
User-friendly Desktop Internet GIS (uDig)
http://udig.refractions.net
http://lists.refractions.net/mailman/listinfo/udig-devel
Reply | Threaded
Open this post in threaded view
|

Re: Re: Can uDig plot PostGIS data from an SQL view?

Cory Horner-2
Gary Lucas wrote:

>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.
>  
>
I suspect that the GeoTools PostGIS implementation currently does not
know to treat views differently from tables.  The estimate_extents
function likely fails on the view, and an approximation is made based on
a handful of features (if it fails it should check if it is a view and
ask the table -- if available -- what its bounds are).  We should
investigate further to ensure that indexes are properly exploited.

Your view is the only layer in your map?  Also, you may need to remove
your PostGIS database from the uDig catalog and re-import it, as the
bounds and state will be cached.

>Could there be another table beside geometry_columns that
>needs an additional entry?
>
>Also, I didn't name my geometry column "the_geom" which seems
>to be a prefered default in some of the GeoTools code.
>  
>
The entry in the geometry_columns table for your view should be
identical to the one for the original table except for the table name.

If you don't have any luck, could you send me your table schema and
geometry columns entries so I might see if there is a case we aren't
handling correctly?

Thanks,
Cory.
_______________________________________________
User-friendly Desktop Internet GIS (uDig)
http://udig.refractions.net
http://lists.refractions.net/mailman/listinfo/udig-devel