Up until now we have been working with shape files, in this lab we bring out the big guns - a real spatial database.

If you are working in an enterprise that has Oracle, DB2 or even (gasp!) ArcSDE you can use the lessons in this lab to connect to your existing infrastructure. We do hope you take home another lesson - PostGIS is a very capable alternative brought to you by the nice folks at Refractions Research.

PostGISLab Application

1. Ensure the following dependencies to your project pom.xml

   <dependencies>
       <dependency>
         <groupId>org.geotools</groupId>
         <artifactId>gt2-cql</artifactId>
         <version>2.4-RC0</version>
       </dependency>
       <dependency>
         <groupId>org.geotools</groupId>
         <artifactId>gt2-epsg-hsql</artifactId>
         <version>2.4-RC0</version>
       </dependency>
       <dependency>
         <groupId>org.geotools</groupId>
         <artifactId>gt2-postgis</artifactId>
         <version>2.4-RC0</version>
       </dependency>
       <dependency>
         <groupId>org.geotools</groupId>
         <artifactId>gt2-widgets-swing</artifactId>
         <version>2.4-RC0</version>
       </dependency>
     </dependencies>

2. Update your IDE project files

   mvn eclipse:eclipse

   And refresh

3. Create a new class org.geotools.demo.PostGISLab
4. Fill in the following code

   package org.geotools.demo;
   
   import java.awt.Color;
   import java.awt.Dimension;
   import java.awt.GridBagConstraints;
   import java.awt.GridBagLayout;
   import java.awt.Insets;
   import java.awt.event.ActionEvent;
   import java.awt.event.ActionListener;
   import java.io.File;
   import java.io.FileNotFoundException;
   import java.io.FileReader;
   import java.io.IOException;
   import java.io.Reader;
   import java.util.Map;
   import java.util.Properties;
   import java.util.Vector;
   
   import javax.swing.JButton;
   import javax.swing.JComboBox;
   import javax.swing.JDialog;
   import javax.swing.JOptionPane;
   import javax.swing.JPanel;
   import javax.swing.JScrollPane;
   import javax.swing.JTextArea;
   
   import org.geotools.data.DataStore;
   import org.geotools.data.DataStoreFinder;
   import org.geotools.data.DataUtilities;
   import org.geotools.data.DefaultQuery;
   import org.geotools.data.FeatureSource;
   import org.geotools.demo.postgis.PostGISDialog;
   import org.geotools.feature.AttributeType;
   import org.geotools.feature.Feature;
   import org.geotools.feature.FeatureCollection;
   import org.geotools.feature.FeatureType;
   import org.geotools.feature.GeometryAttributeType;
   import org.geotools.feature.visitor.FeatureVisitor;
   import org.geotools.filter.FilterTransformer;
   import org.geotools.filter.text.cql2.CQL;
   import org.geotools.gui.swing.ProgressWindow;
   import org.opengis.filter.Filter;
   
   public class PostGISLab {
   
   	public static void main(String[] args) throws Exception {
   		DataStore dataStore = getDatabase(args);
   		String[] typeNames = dataStore.getTypeNames();
   		if (typeNames == null) {
   			JOptionPane.showConfirmDialog(null, "Could not conntect");
   			System.exit(0);
   		}
   		JQuery dialog = new JQuery(dataStore);
   		dialog.setVisible(true);
   		dialog.dispose();
   		System.exit(0);
   	}
   
   	private static DataStore getDatabase(String[] args) throws IOException {
   		PostGISDialog dialog;
   
   		if (args.length == 0) {
   			dialog = new PostGISDialog();
   		} else {
   			File file = new File(args[0]);
   			if (!file.exists()) {
   				throw new FileNotFoundException(file.getAbsolutePath());
   			}
   			Reader reader = new FileReader(file);
   			Properties config = new Properties();
   			config.load(reader);
   
   			dialog = new PostGISDialog(config);
   		}
   		dialog.setVisible(true);
   		Map properties = dialog.getProperties();
   		dialog.dispose();
   
   		if (properties == null) {
   			System.exit(0);
   		}
   		return DataStoreFinder.getDataStore(properties);
   	}
   
   	static class JQuery extends JDialog {
   		final DataStore dataStore;
   
   		JTextArea query;
   		JTextArea show;
   		JButton selectButton;
   		JButton closeButton;
   		JComboBox typeNameSelect;
   		JButton schemaButton;
   
   		private JButton filterButton;
   
   		JQuery(DataStore database) throws IOException {
   			this.dataStore = database;
   			setTitle("Query");
   			setModal(true);
   			setDefaultCloseOperation(JDialog.HIDE_ON_CLOSE);
   
   			JPanel panel = new JPanel();
   			panel.setLayout(new GridBagLayout());
   			GridBagConstraints c = new GridBagConstraints();
   			c.fill = GridBagConstraints.NONE;
   			c.anchor = GridBagConstraints.WEST;
   			c.insets = new Insets(2, 2, 2, 2);
   			c.weightx = 1.0;
   
   			Vector options = new Vector();
   			String typeNames[] = dataStore.getTypeNames();
   			for (int i = 0; i < typeNames.length; i++) {
   				String typeName = typeNames[i];
   				options.add(typeName);
   			}
   			typeNameSelect = new JComboBox(options);
   			panel.add(typeNameSelect, c);
   
   			schemaButton = new JButton("Describe Schema");
   			schemaButton.addActionListener(new ActionListener() {
   				public void actionPerformed(ActionEvent e) {
   					try {
   						String typeName = (String) typeNameSelect
   								.getSelectedItem();
   						FeatureType schema = dataStore.getSchema(typeName);
   						display(schema);
   					} catch (Throwable t) {
   						display(t);
   					}
   				}
   			});
   			c.gridwidth = GridBagConstraints.REMAINDER;
   			panel.add(schemaButton, c);
   
   			c.weightx = 0.0;
   			c.weighty = 0.0;
   			query = new JTextArea(4, 80);
   			c.fill = GridBagConstraints.BOTH;
   			JScrollPane scrollPane1 = new JScrollPane(query);
   			scrollPane1.setPreferredSize(query.getPreferredScrollableViewportSize());
   			scrollPane1.setMinimumSize( query.getPreferredScrollableViewportSize());
   			panel.add(scrollPane1, c);
   
   			c.fill = GridBagConstraints.NONE;
   			c.weightx = 0.0;
   			c.weighty = 0.0;
   			c.gridwidth = GridBagConstraints.RELATIVE;
   			selectButton = new JButton("Select Features");
   			selectButton.addActionListener(new ActionListener() {
   				public void actionPerformed(ActionEvent e) {
   					try {
   						String text = query.getText();
   						FeatureCollection features = filter(text);
   						display(features);
   					} catch (Throwable t) {
   						display(t);
   					}
   				}
   			});
   			panel.add(selectButton, c);
   
   			c.gridwidth = GridBagConstraints.REMAINDER;
   			filterButton = new JButton("CQL to Filter 1.0");
   			filterButton.addActionListener(new ActionListener() {
   				public void actionPerformed(ActionEvent e) {
   					try {
   						String text = query.getText();
   						Filter filter = CQL.toFilter(text);
   
   						display(filter);
   					} catch (Throwable t) {
   						display(t);
   					}
   				}
   			});
   			panel.add(filterButton, c);
   
   			c.fill = GridBagConstraints.BOTH;
   			c.weightx = 1.0;
   			c.weighty = 1.0;
   			show = new JTextArea(24, 80);
   			show.setTabSize(2);
   			JScrollPane scrollPane2 = new JScrollPane(show);
   			scrollPane2.setVerticalScrollBarPolicy(JScrollPane.VERTICAL_SCROLLBAR_ALWAYS);
   			scrollPane2.setPreferredSize(show.getPreferredScrollableViewportSize());
   			//scrollPane2.setMinimumSize(show.getMinimumSize() );
   			panel.add(scrollPane2, c);
   			add(panel);
   
   			c.weighty = 0.0;
   			c.weightx = 0.0;
   			c.fill = GridBagConstraints.NONE;
   			c.anchor = GridBagConstraints.CENTER;
   			c.gridheight = GridBagConstraints.REMAINDER;
   			c.gridwidth = GridBagConstraints.REMAINDER;
   			closeButton = new JButton("Close");
   			closeButton.addActionListener(new ActionListener() {
   				public void actionPerformed(ActionEvent e) {
   					setVisible(false);
   				}
   			});
   			panel.add(closeButton, c);
   
   			this.setMinimumSize(panel.getMinimumSize());
   			this.setPreferredSize(panel.getPreferredSize());
   			this.pack();
   		}
   
   		protected void display(Filter filter) throws Exception {
   			StringBuffer buf = new StringBuffer();
   			FilterTransformer transform = new FilterTransformer();
   			transform.setIndentation(2);
   			String xml = transform.transform(filter);
   
   			show.setText(xml);
   		}
   
   		public void display(FeatureType schema) {
   			if (schema == null) {
   				show.setText("");
   				return;
   			}
   			StringBuffer buf = new StringBuffer();
   			buf.append("typeName=");
   			buf.append(schema.getTypeName());
   
   			buf.append(" namespace=");
   			buf.append(schema.getNamespace());
   			buf.append("attributes = ([\n");
   
   			for (int index = 0; index < schema.getAttributeCount(); index++) {
   				AttributeType type = schema.getAttributeType(index);
   				buf.append(type.getLocalName());
   				buf.append(" [\n");
   
   				buf.append("\t binding=");
   				buf.append(type.getBinding());
   				buf.append("\n");
   
   				buf.append("\t minOccurs=");
   				buf.append(type.getMinOccurs());
   				buf.append(" maxOccurs=");
   				buf.append(type.getMaxOccurs());
   				buf.append(" nillable=");
   				buf.append(type.isNillable());
   				buf.append("\n");
   
   				buf.append("\t restrictions=");
   				buf.append(type.getRestriction());
   				buf.append("\n");
   
   				if (type instanceof GeometryAttributeType) {
   					GeometryAttributeType geomType = (GeometryAttributeType) type;
   					buf.append("\t crs=");
   					if (geomType.getCoordinateSystem() == null) {
   						buf.append("null");
   					} else {
   						buf.append(geomType.getCoordinateSystem().getName());
   					}
   					buf.append("\n");
   				}
   				buf.append("]\n");
   			}
   			buf.append(")");
   			show.setText(buf.toString());
   		}
   
   		public FeatureCollection filter(String text) throws Exception {
   			Filter filter;
   			filter = CQL.toFilter(text);
   
   			String typeName = (String) typeNameSelect.getSelectedItem();
   			DefaultQuery query = new DefaultQuery();
   			query.setTypeName( typeName );
   			query.setFilter( filter );
   			query.setMaxFeatures( 1000 );
   			
   			FeatureSource table = dataStore.getFeatureSource(typeName);
   			return table.getFeatures(query);
   		}
   
   		protected void display(FeatureCollection features) throws Exception {
   			if (features == null) {
   				show.setText("empty");
   				return;
   			}
   			final FeatureType schema = features.getSchema();
   			final StringBuffer buf = new StringBuffer();
   			
   			buf.append(DataUtilities.spec(schema));
   			buf.append("\n");
   			
   			ProgressWindow progress = new ProgressWindow(this);
   			features.accepts(new FeatureVisitor() {
   				public void visit(Feature feature) {
   					buf.append(feature.getID());
   					buf.append(" [\n");
   					for (int index = 0; index < schema.getAttributeCount(); index++) {
   						AttributeType type = schema.getAttributeType(index);
   						String name = type.getLocalName();
   						buf.append("\t");
   						buf.append(name);
   						buf.append("=");
   						buf.append(feature.getAttribute(name));
   					}
   					buf.append("]");
   				}
   			}, progress );
   			show.setText(buf.toString());
   		}
   
   		public void display(Throwable t) {
   			show.setText(t.getLocalizedMessage());
   			show.setForeground(Color.RED);
   		}
   	}
   }

5. We are not quite ready to run yet, we need a dialog to ask for the connection settings

DataStoreFactory Parameters and PostGISDialog

We are not going to go into a lot of details here, and DataStoreFactory can provide you with a description of the parameters needed to make a connection. This description is complete (providing key name, expected value type, and display name and description for your end user).

1. Create a new package org.geotools.demo.postgis
2. Create a new class org.geotools.demo.postgis.PostGISDialog
3. Fill in the following code:

   package org.geotools.demo.postgis;
   
   import java.awt.Color;
   import java.awt.Dimension;
   import java.awt.GridLayout;
   import java.awt.event.ActionEvent;
   import java.awt.event.ActionListener;
   import java.awt.event.KeyAdapter;
   import java.awt.event.KeyEvent;
   import java.util.Collections;
   import java.util.HashMap;
   import java.util.Map;
   
   import javax.swing.JButton;
   import javax.swing.JDialog;
   import javax.swing.JLabel;
   import javax.swing.JPanel;
   import javax.swing.JPasswordField;
   import javax.swing.JTextField;
   
   import org.geotools.data.DataStoreFactorySpi.Param;
   import org.geotools.data.postgis.PostgisDataStoreFactory;
   
   public class PostGISDialog extends JDialog implements ActionListener {
   	private static final long serialVersionUID = -4773502007868922959L;
   	private PostgisDataStoreFactory factory;
   
   	private JParamField database;
   	private JParamField dbtype;
   	private JParamField host;
   	private JParamField port;
   	private JParamField schema;
   	private JParamField user;
   	private JPasswordField password;
   	private JButton okay;
   	private JButton cancel;
   	
   	boolean connect = false;
   	public PostGISDialog(){
   		this( Collections.EMPTY_MAP);
   	}
   
   	public PostGISDialog(Map config) {
   		setTitle("Connection Parameters");
   		setModal( true );
   		dbtype = new JParamField( PostgisDataStoreFactory.DBTYPE, config );
   		host = new JParamField( PostgisDataStoreFactory.HOST, config );
   		port = new JParamField( PostgisDataStoreFactory.PORT, config );
   		
   		schema = new JParamField( PostgisDataStoreFactory.SCHEMA, config );
   		database = new JParamField( PostgisDataStoreFactory.DATABASE, config );
   		user = new JParamField( PostgisDataStoreFactory.USER, config );
   		password = new JPasswordField( (String) config.get( PostgisDataStoreFactory.USER.key ));			
   		password.setToolTipText( PostgisDataStoreFactory.PASSWD.description );
   
   		okay = new JButton("OK");
   		cancel = new JButton("Cancel");
   		
   		okay.addActionListener( this );
   		cancel.addActionListener( this );
   		
   		// layout dialog
   		setLayout( new GridLayout(0,2));
   		
   		add( new JLabel("DBType") );
   		add( dbtype );
   		add( new JLabel("Host"));
   		add( host );
   		add( new JLabel("Port"));
   		add( port );
   		add( new JLabel("Schema"));
   		add( schema );
   		add( new JLabel("Database"));
   		add( database );
   		add( new JLabel("user"));
   		add( user );
   		add( new JLabel("password"));
   		add( password );
   		
   		add( new JLabel(""));
   		JPanel buttons = new JPanel();
   		add( buttons );			
   		buttons.add( okay );
   		buttons.add( cancel );
   		
   		setDefaultCloseOperation( JDialog.HIDE_ON_CLOSE );
   		Dimension preferredSize = getPreferredSize();
   		preferredSize.height += 30;
   		setSize( preferredSize );
   	}
   
   	public Map getProperties() {
   		if( !connect ){
   			return null;
   		}
   		Map config = new HashMap();
   		config.put( PostgisDataStoreFactory.DBTYPE.key, dbtype.getValue() );
   		
   		config.put( PostgisDataStoreFactory.HOST.key, host.getValue() );
   		config.put( PostgisDataStoreFactory.PORT.key, port.getValue() );
   		
   		config.put( PostgisDataStoreFactory.SCHEMA.key, schema.getValue() );
   		config.put( PostgisDataStoreFactory.DATABASE.key, database.getValue() );
   		config.put( PostgisDataStoreFactory.USER.key, user.getValue() );
   		config.put( PostgisDataStoreFactory.PASSWD.key, password.getText() );			
   		
   		
   		return config;
   	}
   
   	public void actionPerformed(ActionEvent e) {
   		String action = e.getActionCommand();
   		if( "OK".equals( action )){
   			connect = true;
   		}
   		setVisible( false );			
   	}
   	class JParamField extends JTextField {
   		Param param;
   		Object value;
   		
   		JParamField( Param param ){
   			this( param, Collections.EMPTY_MAP );
   		}
   		JParamField( Param param, Map map ){
   			super( 14 );
   			this.param = param;
   			setValue( map.get( param.key ));
   			addKeyListener( new KeyAdapter(){
   				public void keyReleased(KeyEvent e) {
   					refresh();
   				}
   			});
   			setToolTipText( param.description );
   		}
   		public void refresh(){
   			try {
   				JParamField.this.value = param.parse( getText() );
   				setToolTipText( param.description );
   				setForeground( Color.BLACK );
   			} catch (Throwable e) {
   				setToolTipText( e.getLocalizedMessage() );
   				setForeground( Color.RED );
   				JParamField.this.value = null;
   			}
   		}
   		public void setValue( Object value ){
   			if( value == null ){
   				value = param.sample;
   			}
   			this.value = value;
   			if( value == null ){
   				setText("");
   			}
   			else {
   				setText( param.text( value ) );			
   			}
   		}
   		public Object getValue() {
   			return value;
   		}
   	}
   }

4. If you look carefully at the details of the inner JParamField utility class you can see examples of the DataStoreFactorySPI.Param class in action.

Running the Application

Before we start lets look at some example connection properties:

1. Create a new file postgis.properties
2. Fill in the following information

   #
   # Properties needed to connect to PostGIS
   # 
   
   # Must be the string "posgis"
   dbtype=postgis
   
   # Machine name or IP address to connect to
   host=www.refractions.net
   
   # Port number to connect to, default 5432
   port=5432
   
   # The database schema to access
   schema = public
   database=demo-bc
   
   # User name and password
   user=demo
   passwd=demo

3. If you start the application with this file as an argument you will not have to type in the connection parameters

The above database is supplied as part of the uDig documentation.

1. Run the application:
   • As a normal Java application from your IDE
     
   • On the command line

     java org.geotools.examplePostGISLab postgis.properties

     If you are using a proxy you will need to tell Java about it:

     java -DsocksProxyHost=proxy -DsocksProxyPort=1080 org.geotools.example.PostGISLab postgis.properties

2. The application will prompt you for connection parameters
   
3. Enter the connection parameters from the above properties file
4. You should see the application screen
   

The application lets you:

• change the featuretype: choose from the available drop down list
• press Describe Schema to see the kind of metadata we track for each table
• press Select Features to perform a query against the database
• press CQL to Filter 1.0 to see your filter represented as XML

Example CQL Queries

With bc_hospitals:

• include
• authority="Fraser"
• BBOX(the_geom, 1200000, 450000, 1400000, 460000 )

For more examples see the CQL module documentation:

• 01 CQL Examples