Adding a Function to the Spatial DB in a Box

One of the key features of the Spatial DB in a Box is that it allows for more functionality to be added, and for that functionality to be realized in multiple databases. In order to show this, I'm going to give a quick tutorial that explains how to add a sophisticated spatial function to all the Spatial DB in a Box databases (in this example, we'll look at postgresql and derby).

We're going to add the polygonize function - this takes in a set of lines and produces a set of polygons for all the "enclosed" areas:

Example geometry:

   **********
   * * A  * *
   *  *  *  *
   * B ** C *
   *   * *  *
   *  *   * *
   * *  D  **
   **       *    
   **********

In this example geometry, the input is a MULTILINESTRING (shown as "*"s above) that "fences off" 4 areas - A, B, C, and D. Polygonization is the process of taking the lines and producing a set of polygons representing the enclosed areas.

The JTS polygonize function expects the input data to be noded (i.e. lines do not cross over each in the middle of a line). I've removed this restriction for this implementation.

Add functionality to StaticGeometry.java

Polygonize is a JTS function, so we can directly add it to the StaticGeometry.java class:

static public Geometry polygonize(Geometry g)
    {
        MultiLineString mls = (MultiLineString) g;

        // dont need to do this (its slow), but it will node for you!!
        mls = (MultiLineString) mls.union(mls);

        Polygonizer polygonizer = new Polygonizer();
        polygonizer.add(mls);

        Collection c = polygonizer.getPolygons();

        Geometry gparts[] = (Geometry[]) c.toArray( new Geometry[c.size()] );
          //I should be using the GeometryFactory, but this is just an example
        return new GeometryCollection(gparts,mls.getPrecisionModel(), mls.getSRID());
    }

Compile

See Postgresql Bindings for how to compile for Postgresql, and SpatialDerby for how to get it runing for Derby/Cloudscape.

Run

Thats it! Everything else is auto-generated for you.

test_postgresqlSpatial=# select polygonize(
'MULTILINESTRING((0 0,10 0),(10 0,10 10),(10 10,0 10),(0 10,0 0),(0 0,10 10),(0 10,10 0))');
                                         polygonize 
-------------------------------------------------------------------------------
 GEOMETRYCOLLECTION (
   POLYGON ((10 0, 0 0, 5 5, 10 0)), 
   POLYGON ((10 10, 10 0, 5 5, 10 10)), 
   POLYGON ((0 10, 10 10, 5 5, 0 10)), 
   POLYGON ((0 0, 0 10, 5 5, 0 0))
                  )
(1 row)

This query corresponds to the above picture - the multilinestring makes up the "box with an X in the middle of it". The 4 areas (A,B,C,D) are the resulting polygons.

Details of Generated Code

Derby/Cloudscape

Because Derby is a java database, the generated function is very simple:

//autogenerated code
 static public String polygonize(String geo0)
   {
        Geometry arg0 = deserialize(geo0);
         return serialize(StaticGeometry.polygonize(arg0));
   }

See SpatialDerby for more information the Derby (Cloudscape) Bindings.

Postgresql

C++ wraper for StaticGeometry

The C++ wraper functions are all the same - they:
1. ensure that java is ready (JTSsetup).
2. do some basic input checking
3. return the results of the StaticGeometry function
4. if an exception occurs, report the error to the caller

NOTE: this is generic - it can be used by any non-java. It is not dependent on Postgresql at all.

//autogenerated code
RETURNTYPE JTSCPP_polygonize(Geometry *arg0)
{
     JTSsetup(); // just in case!

    if (arg0 == NULL)
    {
         return createErrorReturn("polygonize - argument #0 is NULL",1);
    }
    try {
         return createGeometryReturn ( StaticGeometry::polygonize(arg0)  );
    }
    catch (java::lang::Throwable *e)
    {
        return createjstringErrorReturn( e->toString() );
    }
}

All the C++ functions return a "RETURNTYPE" which is a simple structure that can report an error or return a boolean ("b"), string ("ptr"), Geometry ("g"), int ("integer"), or double ("float8"). If type == TYPE_ERROR, an error occured. For more information, see the documentation in the auto-generated c++ file.

typedef struct ab
{
   char  type;
   union
   {
      char   b;
      void   *ptr;     //generic pointer type
      Geometry *g;
      int    integer;
      double float8;
  } data;
} RETURNTYPE;

C wrapper

The C wrapper is a postgresql specific file that maps the DB calls to the C++ wrapper. In general, they all:
1. grab parameters from the DB
2. convert the DB serialied form to appropriate forms (deserializeGeometry() and text_to_cstring()).
3. call the appropriate C++ wrapper function
4. report any errors
5. convert the result to an appropriate serialized form serializeGeometry() an cstring_to_text()

NOTE: if you want to change the serialized form of the geometry objects, you just have to re-implement deserializeGeometry() and serializeGeometry() functions. Currently they just throw the text to the JTS WKT reader.

//autogenerated code
PG_FUNCTION_INFO_V1(JTS_polygonize);
Datum JTS_polygonize(PG_FUNCTION_ARGS)
{
     Geometry *arg0 = deserializeGeometry(    (char *)PG_DETOAST_DATUM(PG_GETARG_DATUM(0))   );

      RETURNTYPE result =  JTSCPP_polygonize(arg0);
      if (result.type == TYPE_ERROR)
      {
            if (result.data.ptr == NULL)
                    elog(ERROR,"UNKNOWN JTS ERROR");

                    char *msg = (char*)palloc( strlen(result.data.ptr) +1);
                    memcpy(msg,result.data.ptr,strlen(result.data.ptr) +1);
                    free(result.data.ptr);
                    elog(ERROR,msg);
      }
      PG_RETURN_POINTER( serializeGeometry(result.data.g) );
}

SQL

The SQL file just tells the database how to map the SQL function name to a "C" function defined above. The only trick is that you might want to change the name of a JTS function to something more appropriate for the database (see morphFunctionName() in the .java).

Notice that the serialized form in this example is "text" - this will probably change in the future (see above and Postgresql Bindings).

-- autogenerated code
CREATE OR REPLACE FUNCTION polygonize(text)
 RETURNS text
 AS '/home/dblasby/postgis4/libpostgresqlSpatial.so' , 'JTS_polygonize'
 LANGUAGE 'C' IMMUTABLE STRICT;