Skip to main content
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index] [List Home]
[udig-devel] Getting postGIS connection 

Wow!  Thanks so much.  It works!

I really appreciate your effort on this.

Murray



Jesse Eichar wrote:
So what I would like is to be able to define a new PostGIS datastore that is based on a SQL query but Geotools does not yet have that so what you will have to do is obtain the connection from the Service. Here's a an operation implementation outlined for you. Essentially this is just an operation, see http://udig.refractions.net/docs/api-udig/extension-points/net_refractions_udig_ui_operation.html for info on the operation extension point. It takes a number of layers as the input (defined in extension definition) and performs some checks to make sure that they are all postgis layers and are from the same postgis. Then it gets the connection. 
 (I'm just pulling off the top of my head but it should give you the idea):

class PostGISOp implements IOp{
public void op( final Display display, final Object target, final IProgressMonitor monitor )throws Exception{ 

        monitor.beginTask("creating view", 10);

        Layer[] layers = (Layer[]) target;

        // check to make sure that layers are PostGIS layers:
        for( Layer layer : layers ) {

            // a layer could have more than 1 resource so check all of them
            for( IGeoResource resource : layer.getGeoResources() ) {
if (!resource.service(new SubProgressMonitor(monitor, 1)).canResolve( 
                        PostgisDataStore.class)) {
                    notifyUserThatLayerIsNotPostGIS(display, layer);
                }
            }
        }

        // make sure that the layers are from the same postGIS

        IService service = null;
        for( Layer layer : layers ) {

            // a layer could have more than 1 resource so check all of them
            for( IGeoResource resource : layer.getGeoResources() ) {
IService current = resource.service(new SubProgressMonitor(monitor, 1)); 
                if (service == null) {
                    service = current;
                } else if (service != current) {
notifyUserThatLayersFromDifferentServices(display, layer); 
                    return;
                }
            }
        }
        // get the connection
Connection connection = service.resolve(Connection.class, new SubProgressMonitor(monitor, 1));
//do some work with the connection maybe make a View? don't for get to add the view to the 
        // geometrycolumns table
        // oh yes and update the progress monitor


    }
private void notifyUserThatLayerIsNotPostGIS( final Display display, Layer layer ) { 
        // like SWT you must be in the Display thread to update the UI
        display.asyncExec(new Runnable(){
            public void run() {
MessageDialog.openError(display.getActiveShell(), "title", "message"); 
            }
        });
    }
private void notifyUserThatLayersFromDifferentServices( final Display display, Layer layer ) { 
        // like SWT you must be in the Display thread to update the UI
        display.asyncExec(new Runnable(){
            public void run() {
MessageDialog.openError(display.getActiveShell(), "title", "message"); 
            }
        });
    }

}


Jesse


On 10-Jan-07, at 1:53 PM, Murray Richardson wrote:
Your emails just came in as I was finishing another LONG message, so I will send it first!
OK - that makes some sense and the snippets at geotools website are very enlightening.
Thing is - I still want to be able to access all types of tables and use them in my queries. For example, non-spatial lookup tables (e.g. a table with just three columns: soil texture, landuse, runoff coefficient) so I can assign this hydrologic parameter (i.e. runoff coefficient) to polygons with "x" soil type and "y" landuse. Once these are assigned to soil/landuse polygons, I need to do an aggregate query to determine the mean runoff coefficient from all polygons comprising a given watershed, then I could join this result to my watershed polygons and map it.
Or another example: For the selected points (e.g. gauged weirs stored as postGIS point layer), show me, in tabular format (or just export to text file) all of the runoff events that occurred in May 2002. This latter example would require some iterative coding procedure to identify runoff peaks and extract relevant non-spatial data. However, it would allow me to access it via a spatial interface.
With uDIG, once I add a postGIS layer, I can see all of the tables (spatial and non-spatial) in my database in the catalog view. If I understood how to access these tables and issue SQL statements through uDIG, and also perform some RecordSet manipulations through the uDIG client (with JDBC I guess) I think that is all I would need to do the above type manipulations. It would be great if I could even access these postGIS tables in a wizard to create some custom tools that do these types of things. My biggest hurdle right now is to understand my way around uDIG/eclipse since it's all new to me. 

Murray





Jesse Eichar wrote:
This are very simple snippets. Once you look at them consider a MathExpression that calculates a value based on two attributes in a Feature: 
 FilterFactory fac=FilterFactoryFinder.createFilterFactory();
MathExpression mathExpr = fac.createMathExpression(ExpressionType.MATH_ADD); 
        mathExpr.addLeftValue(fac.createAttributeExpression("att1"));
        mathExpr.addRightValue(fac.createAttributeExpression("att2"));
CompareFilter compare = fac.createCompareFilter(FilterType.COMPARE_GREATER_THAN_EQUAL); 
        compare.addLeftValue(mathExpr);
        compare.addRightValue(fac.createLiteralExpression(200));
this example returns the feature if the sum of the attributes: att1 and att2 is greater than or equal to 200. 
Jesse
On 10-Jan-07, at 1:06 PM, Jesse Eichar wrote:
You are right that you can write queries that reference multiple table at the same time. You have to write a detailed query for one then do more queries based on that result. If you are looking at a very complicated query then it is probably easier to make use of the DB connection.
For snippets on how to make Filters (the real power of a Query) take a look at: 

http://docs.codehaus.org/display/GEOTOOLS/Snippets

and look at the examples under the Filters heading.
FYI. A query currently permits you to restrict the Attributes retrieved from the table. SO if you are only interested in the geometry or just the a couple of attributes it allows you to restrict the features retrieved to only have those attributes. (Save bandwidth). 

Jesse

On 10-Jan-07, at 12:58 PM, Murray Richardson wrote:


Thanks Jesse,
Are there some coded examples on working with geotools? I don't see exactly how I can execute queries on multiple layers using QUERY and DATASTORE objects, although it's obviously possible from what you say.
I think getting the database connection and generating custom SQL would be most flexible, even if it's not very slick. Also, in some cases I work with massive point files (posted a question about this a couple days ago) and I think it would be better to have spatial queries done on the server side. Similarly with time-series data it is often necessary to add temporary columns, iterate through the table several times and perform some calculations. Given this I would happily take you up on some details to go this route, if it isn't too much trouble for you...
On the other hand, I may find this gets over my head fairly quickly and just have to implement these ideas without using uDIG for anything accept for viewing outputs! 

Murray









Jesse Eichar wrote:
Right now a layer is a table in postGIS. Using the QUERY object and a FeatureStore from the layer, it is possible to obtain features from that table with the attributes you specify in the query. The types of queries that can be created can be very complex and can use many spatial operations. However the datastore is primarily an Access/Query object that allows access to the features in the PostGIS (or more generally the data source what ever it may be) for viewing or direct editing. There isn't an API for adding columns but there is one for creating new tables. There is not yet an OPERATIONS API yet in Geotools, which is likely what you are looking for. There are currently 2 ways to do what you require that I can think of: 1. In order to satisfy your requirements on the server side you would have to get the Database connection and generate custom SQL queries and execute them. 2. You can fetch the features to the client using the Datastore API and its queries, and do the calculations on the client and write the result back to the PostGIS, also using the Datastore API. As far as I can tell you can do most of these steps using the second method. As I said Queries (and Filters) can do some very complicated work including GeoSpatial and other "normal" queries. Intersection, differences, etc... can all be done server side using Filter and Query. 
If you want more details on any of these steps let me know,
Jesse
On 10-Jan-07, at 9:32 AM, Murray Richardson wrote:

Hello Community,
Thanks for previous help you've offered. I could use a bit more direction if possible to determine whether I should carry on with uDIG for a small project I am working on. I'm finding the uDIG development learning curve to be a bit steep so far.
I've spent the last few days looking at uDIG (e.g. plugin tutorial and all the other online resources) and I am trying to figure out the simplest way to make use of powerful postgreSQL/postGIS capabilities. Previously I was concerned with constructing spatial queries on massive point data sets. These processes are easy if I use a standard postgreSQL interface like psql or pgAdmin. It seems like it should be a fairly simple task to construct plugins within uDIG that make use of these and other RDBMS capabilities through JDBC (if they don't already exist somewhere). I read an entry on Cory Horner's community page about extending uDIG functionality to incorporate better RDBMS capabilities. I am most definitely interested in this because I am always faced with problems where this type of capability would be an asset.
To give you an idea of the type of functionality I am interested in, considering the following two examples:
(1) I am constantly using GIS and RDBMS capabilities separately (loosely coupled fashion)for information management in the field of hydrology. For example - I need to summarize the joint probability distribution of land-use and soil types to determine an average (lumped) runoff coefficient for nested watersheds (since runoff is a function of landuse and underlying soil properties, among other things). I do this by exporting the polygon intersection of a soils layer, land-use layer and watershed layer to a RDBMS. Then I use lookup tables to determine coefficients for individual landuse-soil combinations and use an aggregate query to determine the mean value for each watershed. It is nice to then join the mean value back to the watershed layer and display this in a map. I also use the RDBMS to do reporting of the individual intersected polygons by watershed. This type of analysis and reporting is standard in flood risk assessment and could be very easily streamlined with a tool like uDIG and postgreSQL.
(2) Storing and accessing spatial-temporal hydrology data is an emerging area of interest (consider the ESRI ArcHydro framework if you're familiar with it) and an interesting direction for Open Source GIS. For example, I would like to be able to store decades worth of hourly streamflow data for numerous gauged weirs within a postgreSQL DB and have this linked to a uDIG application that allows me to extract the streamflow data by (e.g.) clicking on a point station and entering a date. The query result could be just exported to a text file for now. This capability would open the door to a whole range of interesting spatial-temporal hydrology applications (e.g. recession analysis, climate and streamflow data interpolation, hydrologic model parameterization).
Based on these two particular examples, could someone perhaps direct me on how to proceed to develop a very simple proof of concept for enhanced RDBMS capabilities within a GIS environment? Consider example 1 for instance - can I create a uDIG operation that is accessed by right-clicking on 3 highlighted layers (like the difference operation that is available when two layers are selected). For my purposes I would like these to be postGIS layers. The operation could then execute the postGIS intersection procedure, the attribute join and the aggregate function all within the postgreSQL service, and then map the results back to the watershed layer for display in uDIG?
I know this is a fairly specific question, but what it boils down to is, how do I access postGIS spatial operations as well as general postgreSQL RDBMS functionality and non-spatial tables within the uDIG environment?
Thanks in advance for any help. This project is for a small Open Source/Open Access Student Experience program grant I am working on through University of Toronto that involves developing a proof-of-concept idea and some educational tutorials using postgreSQL/postGIS. I would like to eventually be able to post the results on my own uDIG community page if things go well. I think this type of work could help open up uDIG capabilites to the slightly less technical users like myself (e.g. GIS and basic programming/RDBMS skills experience, but still need lots of examples and explicit documentation to follow!).
Look forward to hearing back from someone - hope it's not too much to ask over a mailing list! 

--Murray Richardson, M.Sc
PhD Candidate
University of Toronto
murray.richardson@xxxxxxxxxxx <mailto:murray.richardson@xxxxxxxxxxx>










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

_______________________________________________
User-friendly Desktop Internet GIS (uDig)
http://udig.refractions.net
http://lists.refractions.net/mailman/listinfo/udig-devel
--No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.5.432 / Virus Database: 268.16.8/621 - Release Date: 1/9/2007 1:37 PM 
_______________________________________________
User-friendly Desktop Internet GIS (uDig)
http://udig.refractions.net
http://lists.refractions.net/mailman/listinfo/udig-devel


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

------------------------------------------------------------------------
_______________________________________________
User-friendly Desktop Internet GIS (uDig)
http://udig.refractions.net
http://lists.refractions.net/mailman/listinfo/udig-devel
------------------------------------------------------------------------
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.5.432 / Virus Database: 268.16.8/621 - Release Date: 1/9/2007 1:37 PM 
_______________________________________________
User-friendly Desktop Internet GIS (uDig)
http://udig.refractions.net
http://lists.refractions.net/mailman/listinfo/udig-devel



------------------------------------------------------------------------

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


------------------------------------------------------------------------

No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.5.432 / Virus Database: 268.16.8/621 - Release Date: 1/9/2007 1:37 PM

Back to the top