This page last changed on Jul 31, 2006 by cholmes.

Spatial DB in a Box Postgresql bindings

This is tutorial for how to generate the Postgresql Spatial DB in a Box bindings. The required source code is attached at the bottom of this message. Almost all the functionality of the OGC SF SQL specification is handled by these bindings. See the To Do section for comments. See Add A Function for more details of generated code.

The basic architecture is:

A) Use a compiled native JTS to handle ALL processing Compiling JTS
B) The compiled JTS Java interface ("StaticGeometry") is wrapped by a simple C++ library (libstaticgeom) that handles exceptions and the details of interfacing with the compiled Java. This wrapper is auto-generated.
C) A Postgresql specific "C" library is autogenerated that glues the DB calling convention to the C++ JTS wrapper.
D) An auto-generated SQL "CREATE FUNCTION" script that glues the OGC SF SQL function names to the "C" functions created in step (C).

See Add A Function for details of the autogenerated Postgresql code (and all the other Spatial DB in a Box databases).


Un-tar the included source code, and then run the commands in the "make" file. This isn't a normal make file - its just a simple script that does all the work. You run it by typing "source make".

It does a lot of work:
1. compiles all the java files into .class
2. compiles all the .class and .jar files to .so
3. runs the java autogeneration of the c++, "c", and sql scripts
4. compiles all the auto-generated code
5. makes a database and installs the functions in it
6. runs a few simple tests on the database

NOTE: if things dont work properly, its probably because it cannot find the libraries its generating. You need to become root and do a "/sbin/ldconfig `pwd`" for linux to realize there's a library there. Read the documentation in the "make" file for more details.
NOTE: make sure that you compiled your postgresql so it links to libstdc+. (ie. either "LDFLAGS=-lstdc+ ./configure ..." or "set LDFLAG=-lstdc++ ; ./configure ..." when you configure and build postgresql.

Interesting Files          -- main static wrapper for all the functions - creates the c++ wrapper for StaticGeometry   - creates postgresql bindings for StaticGeometry - creates SQL create function statements

More information is available at Add A Function.


In order to use the Spatial functions inside the database, you must tell the database about your function. This is handled by the "jts_postgres.sql" script.

You can then start calling the OGC SF SQL functions:

#select intersection('LINESTRING(0.1 0.1, 1 1)','LINESTRING(0 0, 0.5 0.5)');
 LINESTRING (0.1 0.1, 0.5 0.5)
(1 row)

#select relate('POINT(0 0)','LINESTRING(0 0, 10 10)');
(1 row)
#select touches('POINT(0 0)','LINESTRING(0 0, 10 10)');
(1 row)

#select geometrytype('POINT(0 0)');
(1 row)

To Do

1. I'm using WKT as the serialized form, and using "text" as the Geometry type. This is not very efficient. I was going to use simple WKB as the serialized form - all thats required to do this is add a WKB reader/writer to StaticGeometry. I'm thinking that this is a more efficient serialized form:

serialized form:
     int          size; //postgresql required structure length
     int          SRID;
     BOX2DFLOAT4  bbox;
     char         WKB;

This way the DB can do everything it needs to do - indexing, keeping SRIDs correct with the SRID/bbox fields, and everything else can be handed off to the JTS wrapper for evaluation. We'll probably use the 3d (x,y,z) and 4d (x,y,z,m) extended WKB representation.

Once this is done, all the other "Spatial DB in a Box" databases will also be able to use the functionality. The Java databases may use the normal JTS Geometry serializer (depending on the DB specific implementation strategy). Its possible to just use WKB as the serialized form, but it can get complex because of custom coordinate sequences. For DB operations, this shouldnt be a problem.

2. There are a few functions to add. They're mostly hidden somewhere in JTS or are really simple - like PolyFromWKT() (a special form of GeomFromWKT()). This should be trivial to do.

3. index/estimation support. This should be trivial to add - just attach these functions to the bbox in the serialized form in (1) above.

4. Coordinate reference system support. PostGIS currently uses PROJ4 to do this - either we can just bind the PROJ4 to the WKB SpatialDB representation, or you could just add the Geotools Java Coordinate System transform library. This means that all the DB would get the support.

5. Metadata support. I think we could improve on the Postgis version, but its simple and should work on all databases!

6. Get the compile running from a platform independent makefile instead of the (stinky) compile script I've included.

postgresql_bindings.tar.gz (application/x-gzip)
Document generated by Confluence on May 14, 2014 23:00