[
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