:Author: Jody Garnett :Author: Micheal Bedward :Thanks: geotools-user list :Version: |release| :License: Create Commons with attribution .. include:: .. _query: ***************** Query Tutorial ***************** Welcome ======== Welcome to Geospatial for Java. This workbook is aimed at Java developers who are new to geospatial and would like to get started. .. _quickstarts: ../quickstart/index.html You should have completed one of the GeoTools' Quickstarts_ prior to running through this workbook. We need to be sure that you have an environment to work in with GeoTools jars and all their dependencies. We will list the maven dependencies required at the start of the workbook. This tutorial illustrates how to query spatial data in GeoTools. In the earlier tutorials we have been working with shapefiles. The focus of this workbook is the ``Filter`` API used to query ``DataStores``, such as shapefiles, databases and Web Feature Servers. So in this lab we will bring out the big guns - a real spatial database. If you are working in an enterprise that has as spatial database (e.g. Oracle, DB2) or geospatial middle ware you can use GeoTools to connect to your existing infrastructure. Here we will use `PostGIS `_ which is a spatially-enabled extension of PostgreSQL supporting *Simple Features for SQL*. We will build an application that can connect to both a PostGIS database and shapefiles. We are trying out a code first idea with these workbooks |hyphen| offering you a chance to start with source code and explore the ideas that went into it later if you have any questions. Query Lab Application ===================== The :download:`QueryLab.java ` example will go through using a ``Filter`` to select a ``FeatureCollection`` from a shapefile or other ``DataStore``. We are going to be using connection parameters to connect to our ``DataStore`` this time; and you will have a chance to try it out using PostGIS or a Web Feature Server at the end of this example. 1. Please ensure your ``pom.xml`` includes the following: .. literalinclude:: artifacts/pom.xml :language: xml :start-after: http://maven.apache.org :end-before: 2. Create the package ``org.geotools.tutorial.filter`` and class ``QueryLab`` class and copy and paste the following to get going: .. literalinclude:: /../src/main/java/org/geotools/tutorial/filter/QueryLab.java :language: java :start-after: // docs start source :end-before: // docs end main The Application GUI ------------------- Next we create the application user interface which includes a text field to enter a query and a table to display data for the features that the query selects. Here is the code to create the controls: 1. Add the following constructor: .. literalinclude:: /../src/main/java/org/geotools/tutorial/filter/QueryLab.java :language: java :start-after: // docs start constructor :end-before: // docs start file menu 2. Next we add menu items and Actions to the File menu to connect to either a shapefile or a PostGIS database: Each ``Action`` is calling the same method but passing in a different ``DataStoreFactory`` .. literalinclude:: /../src/main/java/org/geotools/tutorial/filter/QueryLab.java :language: java :start-after: // docs start file menu :end-before: // docs end file menu 3. Now let us look at the ``Data`` menu items and ``Actions``: .. literalinclude:: /../src/main/java/org/geotools/tutorial/filter/QueryLab.java :language: java :start-after: // docs start data menu :end-before: // docs end data menu :append: } Connect to DataStore --------------------- In the quickstart we made use of ``FileDataStoreFinder`` to connect to a specific file. This time we will be using the more general ``DataStoreFinder`` which takes a map of connection parameters. Note the same code can be used to connect to quite different types of data stores as specified by the ``DataStoreFactorySpi`` (*Service Provider Interface*) parameter. The file menu actions call this method with an instance of the either ``ShapefileDataStoreFactory`` or ``PostgisNGDataStoreFactory``. The ``JDataStoreWizard`` displays a dialog with entry fields appropriate to either a shapefile or PostGIS database. It requires a few more lines of code than ``JFileDataStoreChooser`` which was used in the Quickstart to prompt the user for a shapefile, but allows greater control. 1. The File menu actions call this method to connect. .. literalinclude:: /../src/main/java/org/geotools/tutorial/filter/QueryLab.java :language: java :start-after: // docs start connect :end-before: // docs end connect 2. Helper method to update the combo box used to choose a feature type: .. literalinclude:: /../src/main/java/org/geotools/tutorial/filter/QueryLab.java :language: java :start-after: // docs start update :end-before: // docs end update Query ----- A ``Filter`` is similar to the where clause of an SQL statement; defining a condition that each feature needs to meet in order to be selected. Here is our strategy for displaying the selected features: * Get the feature type name selected by the user and retrieve the corresponding ``FeatureSource`` from the ``DataStore``. * Get the query condition that was entered in the text field and use the CQL class to create a ``Filter`` object. * Pass the ``Filter`` to the ``getFeatures`` method which returns the features matching the query as a ``FeatureCollection``. * Create a ``FeatureCollectionTableModel`` for our dialog's ``JTable``. This GeoTools class takes a ``FeatureCollection`` and retrieves the feature attribute names and the data for each feature. With this strategy in mind here is the implementation: 1. Getting feature data using ``featureSource.getFeatures(filter)`` .. literalinclude:: /../src/main/java/org/geotools/tutorial/filter/QueryLab.java :language: java :start-after: // docs start filterFeatures :end-before: // docs end filterFeatures 2. The ``FeatureCollection`` behaves as a predefined query or result set and does not load the data into memory. You can ask questions of the ``FeatureCollection`` as a whole using the available methods. .. literalinclude:: /../src/main/java/org/geotools/tutorial/filter/QueryLab.java :language: java :start-after: // docs start countFeatures :end-before: // docs end countFeatures 4. By using the ``Query`` data structure you are afforded greater control over your request allowing you to select just the attributes needed; control how many features are returned; and ask for a few specific processing steps such as reprojection. Here is an example of selecting just the geometry attribute and displaying it in the table. .. literalinclude:: /../src/main/java/org/geotools/tutorial/filter/QueryLab.java :language: java :start-after: // docs start queryFeatures :end-before: // docs end queryFeatures Running the Application ----------------------- Now we can run the application and try out some of these ideas: 1. Start the application and select either *Open shapefile...* from the File menu. The ``JDataStoreWizard`` will prompt you for a file. Please select the ``cities.shp`` shapefile available as part of the `uDig sample data set `_ used in previous tutorials. .. image:: images/shapeWizard1.png 2. Press **Next** to advance to a page with optional parameters. For this example please press **Finish** to continue past these options. .. image:: images/shapeWizard1.png 4. Once you have successfully connected to your shapefile the ``ComboBox`` in the ``MenuBar`` will display the names of the available feature types. A single type for a shapefile is not that exciting but when you use PostGIS you should be able to choose which table to work with here. 5. The query field will indicate we wish to select all features using the common query language: .. code-block:: sql include Select :menuselection:`Data --> Get features` menu item and the table will display the feature data. .. image:: images/citiesInclude.png 6. Common query language allows for simple tests such as selecting features where the ``CNTRY_NAME`` attribute is 'France': .. code-block:: sql CNTRY_NAME = 'France' And choose Get Features to display. .. image:: images/citiesFrance.png 7. Comparisons are supported such as features with value ``>= 5`` for the ``POP_RANK`` attribute: .. code-block:: sql POP_RANK >= 5 8. Boolean logic is supported allowing you to combine several tests: .. code-block:: sql CNTRY_NAME = 'Australia' AND POP_RANK > 5 9. Spatial queries are also supported: .. code-block:: sql BBOX(the_geom, 110, -45, 155, -10) This is a bounding box query that will select all features within the area bounded by 110 - 155 |deg| W, 10 - 45 |deg| S (a loose box around Australia). Notice that we name the geometry attribute which, for the *cities* shapefile, is ``Point`` type. .. note:: The geometry of a Shapefile is always called ``the_geom``, for other data stores we would need to look up the name of the geometry attribute. Things to Try ============== * Try connecting to a public PostGIS instance. Select *Connect to PostGIS database...* from the file menu and fill in the following parameters. .. image:: images/postgisWizard1.png If you don't have a PostGIS database you can try connecting to a public online database at `Refractions Research `_ with the following credentials: :host: ``www.refractions.net`` :port: ``5432`` :database: ``demo-bc`` :user: ``demo`` :passwd: ``demo`` Next the wizard will display a second page of optional parameters. For this example you can leave this blank and just click the *Finish* button. * We have seen how to represent a Filter using CQL. There is also the original XML representation used by web features servers to work with. .. code-block:: java Configuration configuration = new org.geotools.filter.v1_0.OGCConfiguration(); Parser parser = new Parser(configuration); ... Filter filter = (Filter) parser.parse(inputstream); If you need an XML file to start from you can write one out using. .. code-block:: java Configuration = new org.geotools.filter.v1_0.OGCConfiguration(); Encoder encoder = new org.geotools.xsd.Encoder(configuration); encoder.encode(filter, org.geotools.filter.v1_0.OGC.FILTER, outputstream); For these examples to work you will need a dependency on *gt-xml*. * Earlier we covered the use ``FeatureIterator`` to sift through the contents of a ``FeatureCollection``. Using this idea with ``Query`` allows you to work with just the geometry when determining the center of a collection of features. .. literalinclude:: /../src/main/java/org/geotools/tutorial/filter/QueryLab.java :language: java :start-after: // docs start centerFeatures :end-before: // docs end centerFeatures Filter ======= .. sidebar: CQL CQL is defined in OGC Catalog specification; the standard comes from library science. To request information from a ``FeatureSource`` we are going to need to describe (or select) what information we want back. The data structure we use for this is called a ``Filter``. We have a nice parser in GeoTools that can be used to create a ``Filter`` in a human readable form: .. code-block:: java Filter filter = CQL.toFilter("POPULATION > 30000"); We can also make spatial filters using CQL |hyphen| geometry is expressed using the same Well Known Text format employed earlier for JTS Geometry:: Filter pointInPolygon = CQL.toFilter("CONTAINS(THE_GEOM, POINT(1 2))"); Filter clickedOn = CQL.toFilter("BBOX(ATTR1, 151.12, 151.14, -33.5, -33.51)"; You may also skip CQL and make direct use of a ``FilterFactory``: .. code-block:: java FilterFactory ff = CommonFactoryFinder.getFilterFactory(null); Filter filter = ff.propertyGreaterThan(ff.property("POPULATION"), ff.literal(12)); Your IDE should provide command completion allowing you to quickly see what is available from ``FilterFactory``. Note, filter is a real live java object that you can use to do work:: if(filter.evaluate(feature)){ System.out.println("Selected "+ feature.getId(); } The implementation in GeoTools is very flexible and able to work on ``Features``, ``HashMaps`` and ``JavaBeans``. .. HINT:: You may have noticed that ``Filter`` is actually an interface. Because the ``Filter`` data structure is defined by a specification we cannot support the definition of new kinds of ``Filter`` objects and expect them to be understood by the external services we communicate with. The good news is that ``Filter`` can be extended with new ``Functions``; and our implementation can be taught how to work on new kinds of data using ``PropertyAccessors``. Expression ---------- You may have missed it in the last section; but we also described how to access data using an ``Expression``. Here are some examples: .. code-block:: java ff.property("POPULATION"); // expression used to access the attribute POPULATION from a feature ff.literal(12); // the number 12 You can also make function calls using the expression library. Here are some examples: .. code-block:: java CQL.toExpression("buffer(THE_GEOM)"); CQL.toExpression("strConcat(CITY_NAME, POPULATION)"); CQL.toExpression("distance(THE_GEOM, POINT(151.14,-33.51))"); Query ----- The ``Query`` data structure is used to offer finer grain control on the results returned. The following query will request ``THE_GEOM`` and ``POPULATION`` from a ``FeatureSource`` "cities": .. code-block:: java Query query = new Query("cities", filter, new String[]{ "THE_GEOM", "POPULATION" }); FeatureCollection ----------------- Previously we added features to a ``FeatureCollection`` during the CSV2SHP example. This was easy as the ``FeatureCollection`` was in memory at the time. When working with spatial data we try to not have a ``FeatureCollection`` in memory because spatial data gets big in a hurry. Special care is needed when stepping through the contents of a ``FeatureCollection`` with a ``FeatureIterator``. A ``FeatureIterator`` will actually be streaming the data off disk and we need to remember to close the stream when we are done. Even though a ``FeatureCollection`` is a :code:`Collection` it is very lazy and does not load anything until you start iterating through the contents. The closest Java concepts I have to ``FeatureCollection`` and ``FeatureIterator`` come from JDBC as shown below. ====================== ==================== GeoTools JDBC ====================== ==================== ``FeatureSource`` ``View`` ``FeatureStore`` ``Table`` ``FeatureCollection`` ``PreparedStatement`` ``FeatureIterator`` ``ResultSet`` ====================== ==================== If that is too much just remember |hyphen| please close your feature iterator when you are done. If not you will leak resources and get into trouble.