Hi everyone

I’m going to talk a few things about data access that has changed in the new version 2.4 (some of them already appeared in the 2.3, but now are consolidated in 2.4)

At first, they are changes that shouldn’t affect on the already existing developments, because all of them are about improvements or additions over the old versions.

I’m going to talk about:

• Automatic dispose of resources when we hit the end in an iteration over a FeatureSet.
• Obtain a “List” with the features of one “store”, with a filter or without it.
• Create a layer with the filtered data of one table.
• Create a filter for a DB that will be independent from itself.

We are going to be seeing this things.

Dispose of resources in a FeatureSet.

This feature already appear in the last builds of gvSIG 2.3. In several classes of the DAL library, it was given support to the Java interface ”Iterable”. This allow us to build code in more comfortable way. Instead of:

  try {
    FeatureSet set = store.getFeatureSet();
    Iterator it = set.iterator();
    while( it.hasNext() ) {
      Feature feature = (Feature) it.next();

      ...

    }
  } finally {
    DisposeUtils.disposeQuietly(it);
    DisposeUtils.disposeQuietly(set);
  }

Now we could make something like this:

  for(Feature feature : store.getFeatureSet() ) {

    ...

  }

In this way, the resources associated to the iterator and the set are disposed automatically when it reaches the end of the iteration. To do this, what has be done is when it’s called the method hasNext from the iterator and has been reached the end of the iteration, inside of this method the resources are disposed.

We should be careful with this code constructions when we are going to exit the loop before to reach the end of the iteration, with a break or a returrn,

Obtain a “List” of features

This feature also appeared in the last builds of gvSIG 2.3. It has been added the following methods to the FeatureStore:

  public List<Feature> getFeatures(FeatureQuery query, int pageSize);
  public List<Feature> getFeatures();

Until now, the only way to access to the store featuers was through the method getFeatureSet and then iterating over them. With the addition of this two methods now we can obtain directly a ”List” of features, allowing us to a random access over the store features. We should be careful with the use of this method. To obtain this list of features we use FeaturePagingHelper, so we don’t load all the features in memory. It uses a “paging” (size by default 500 elements) and the pages are being loaded and unloaded automatically depending on how they are being requested from the interface ”List”. A real random access over all the list could lead to an overflow in the data access and a significant loss of performance. This method is designed to give support to show results of a search over the user data, for example in a JList or JTable, since is relatively easy create a TableModel or a ListModel based on a “List<Feature>”. Usually the performance of a JTable and a JList will be acceptable even with a hundred of thousands of records.

An important consideration. Once is obtained a ”List”, if we try to edit or end the edition through the store from where it has been obtained it, it can produce some errors. In the 2.4 version we should not change the edition mode of a store once we have obtained a ”List”. We will try to fix this issue in future releases.

Create a layer with the filtered data of one table

This is not a truly improvement in the data access library, but because in some way is related to the data access and a interesting improvement, that’s why I’m going to explain it here.

It has been added to the VectorLayer interface this methods:

    public FeatureQuery getBaseQuery();
    public void addBaseFilter(Evaluator filter);
    public void addBaseFilter(String filter);

The addBaseFilter methods allow us to add a filter that will be applied when it’s needed to obtain a FeatureSet used for drawing layer elements.

This allow us with an easy way through code to add filtered information from layers to a view that is going to be visualize. Starting with the same table is possible to have different layers with different data visualizations.

Create a filter for a DB that will be independent from itself.

I’m going to take more time explaining this topic, because in my experience, usually it takes time to understand.

The way to filter data in gvSIG 2 is through the ”addFilter” method or a ”setFilter” from a ”FeatureQuery”. A filter is a class that the interface ”Evaluator” has implemented.

Suppose that we have a table with a geometry field and we want filter the features with lines that intersects with a given area.

The implementation should be something like this:

public class  IntersectsGeometryEvaluator extends AbstractEvaluator {

    private String geometryColumnName;
    private Geometry areaOfInterest;

    IntersectsGeometryEvaluator(Geometry areaOfInterest, String geometryColumnName) {
        this.areaOfInterest = areaOfInterest;
        this.geometryColumnName = geometryColumnName;
    }

    @Override
    public Object evaluate(EvaluatorData data) throws EvaluatorException {
        try {
        Geometry geometryColumn = (Geometry) data.getDataValue(geometryColumnName);
            if (geometryColumn == null) {
                return false;
            }
        return geometryColumn.intersects(this.areaOfInterest);

        } catch (Exception e) {
            return false;
        }
    }

    @Override
    public String getName() {
        return "intersects with geometry";
    }

}

  ...
  Geometry areaOfInterest = ...

  FeatureQuery query = store.createFeatureQuery();
  query.setFilter(new IntersectsGeometryEvaluator(areaOfInterest,"the_geom"));
  FeatureSet set = store.getFeatureSet(query);
  ...

Thereby we would obtain a set of features filtered with the condition that we are interested.

Even that this works with all data sources (shapes, dxf or DB tables), it wouldn’t be optimal for a DB. If we let the code like that, it will get all the table records associated with the store, bring them to local and filter them in local. If the table is big and the DB has support for spatial indexes, this will be very inefficient.

How should we do it ?

The ”Evaluator” interface has a method called ”getSQL”. In the example, we didn’t implement it. This method should return the ”where” part of the ”SQL” clause that do something similar to our Java code. It’s not precisely that they are identical, but at least it should return the same or more records (never less) than our Java code filter. When we apply this filter over a data source that supports SQL conditions like ”where”, the data access library will take care of it and return this records. If the filter is applied over a data source that doesn’t have support for it, for example a shape file, the parameter returned from ”getSQL” it will be ignored. If it supports a SQL type filter, the data access library will create a query from the DB with this condition and then will filter the obtained records with our evaluator. With this way to work, it will be not necessary get all the table data records and we will keep available all the filter potential from our Java code.

We could have a ”getSQL” method like this:

  @Override
  public String getSQL() {
      return MessageFormat.format(
          "ST_intersects( ST_GeomFromText(''{0}'',{1}), {2} )",
          this.areaOfInterest.convertToWKT(),
          this.srsid,
          this.geometryColumnName
      );
  }

This method will return the condition part that we will put in the ”where” clause. It will allow us filter it with the interested condition.

If we take a closer look we could find a few problems..

• We need a“srsid” to rebuild the geometry from the DB server starting with aWKT. This shouldn’t be big a problem, we just have to set our evaluator in which projection are our geometries. Usually we will use a IProjection object, this object is the usual return from any artifact in gvSIG when we ask for a projection. Here we required the data base SRS identifier, not a projection object.
• The syntax used to make to query is so OGC.. What if the DB that we are accessing doesn’t accept this type of syntax?

To solve this problems in gvSIG 2.4 it has been introduced the concept of ”ExpressionBuilder” linked to a data source FeatureStore. We can ask to the store, with whitch we are working, for a ”ExpressionBuilder” appropriate for itself and use it to create the condition that we need. Let see an example:

public class  IntersectsGeometryEvaluator extends AbstractEvaluator {

  private String geometryColumnName;
  private Geometry areaOfInterest;
  private IProjection projection;
  private ExpressionBuilder builder;

  IntersectsGeometryEvaluator(
      Geometry areaOfInterest,
      String geometryColumnName,
      IProjection projection,
      ExpressionBuilder builder
    ) {
      this.areaOfInterest = areaOfInterest;
      this.geometryColumnName = geometryColumnName;
      this.proyeccion = projection;
      this.builder = builder;
  }

  @Override
  public Object evaluate(EvaluatorData data) throws EvaluatorException {
      try {
        Geometry geometryColumn = (Geometry) data.getDataValue(geometryColumnName);
        if (geometryColumn == null) {
            return false;
        }
        return geometryColumn.intersects(this.areaOfInterest);

      } catch (Exception e) {
      return false;
      }
  }

  @Override
  public String getName() {
      return "intersects with geometry";
  }

  @Override
  public String getSQL() {
    String condition = this.builder.set(
        this.builder.ST_Intersects(
          this.builder.geometry(this.areaOfInterest, this.projection),
          this.builder.column(this.geometryColumnName)
        )
    ).toString();
    return condition;
  }
}

  ...
  Geometry areaOfInterest = ...

  FeatureQuery query = store.createFeatureQuery();
  query.setFilter(new IntersectsGeometryEvaluator(
        areaOfInterest,
        "the_geom",
        projection,
        store.createExpressionBuilder()
      )
  );
  FeatureSet set = store.getFeatureSet(query);
  ...

I’m going to explain the changes..

First of all, our evaluator, in addition to receive a geometry with an area on which we want the filter, and the column name which we want to filter, it will receive an object “Iprojection” with the projection in which are our geometries, and a ”ExpressionBuilder” that we have obtained through the ”store” that we are working with. We will save this new two parameters in our class properties like we did with the previous two parameters. The rest of the changes are already in the ”getSQL” method. Now there isn’t SQL code in our method. Instead it’s called a builder to build the SQL code. This builder will take care of:

  this.builder.geometry(this.areaOfInterest, this.projection)

.. build this SQL code necessary for our geometry with its projection in our format, to be translated correctly to the format of the DB associated with the store that we are working with. We shouldn’t be worried about its type, WKT or some other format, or if the IProjection has this or other code in this DB. The builder will take care of it for us. The same as it take care to translate:

  this.builder.ST_Intersects(
    this.builder.column("a")
    this.builder.column("b")
  )

to the appropriate syntax for our DB. For example. If we are working over a PostgreSQL+PostGIS, it will generate something like:

  ST_intersects("a","b")

But If our “store” is stored in a SQLServer data base, it will generate something like:

  [a].STIntersects([b])

It will take care of this job and letting our code free to handle different SQL dialects.

(Thanks to Oscar for the translation)