2

I have a PostgreSQL/PostGIS table "geometry_table" that has a 1-to-many relation to another table "properties_table", where each geometry has many properties. These properties hold style values of geoemtries (e.g color, stroke-width, ... etc). I have a CSS style document that's used to style this geometry_table layer, something like the following:

/*Lines*/
[GetStyleProperty(geomId, 'visible') = 'true'] [dimension(geometry) = 1] [@sd < GetStyleProperty(geomId, 'feature_max_scale')] [@sd > GetStyleProperty(geomId, 'feature_min_scale')]{
   stroke: [GetStyleProperty(geomId, 'stroke_color')];
   stroke-width: [GetStyleProperty(geomId, 'stroke_width')];
   stroke-dasharray: [GetStyleProperty(geomId, 'stroke_dasharray')];
   stroke-opacity: [GetStyleProperty(geomId, 'opacity')];
}

The function "GetStyleProperty" is a custom Geotool function I've created to read a given property name of a given geometry from the DB. This function first opens a DB connection, then executes a DB stored function, then closes the connection.

My problem is that loading the geometry_table layer takes too much time (more than 6 seconds to display arround 10 featrues only). I have recognized that the problem is from my CSS Style document, because if I use another style document (e.g the generic one) the layer is dispalyed in less than one second. I've measured the execution time of "GetStyleProperty" function and found that quarter of the time is wasted in opening the DB connection.

So, my question is: instead of opening a DB connection in every function call, is there any shared connection that I can use to connect to my DB? Or, how can I make a shared DB connection that lives as long as the GeoServer application lives?

I'm using the following code to create a DB connection:

public static Connection createConnection() throws ClassNotFoundException, SQLException {
        Class.forName("org.postgresql.Driver");
        String url = "jdbc:postgresql://localhost:5432/spatialdbnew";
        Connection conn = DriverManager.getConnection(url, "postgres", "postgres"); //use postgres username to skip row-level security to enhance performance

        /*
         * Add the geometry types to the connection.
         */
        ((org.postgresql.PGConnection) conn).addDataType("geometry",
                (Class<? extends PGobject>) Class.forName("org.postgis.PGgeometry"));
        ((org.postgresql.PGConnection) conn).addDataType("box3d",
                (Class<? extends PGobject>) Class.forName("org.postgis.PGbox3d"));

        return conn;
    }
  • why do you need a custom function, just use the attributes directly from a view – Ian Turton Nov 24 '18 at 8:32
  • Hi Ian, I can't use a view, because the relation between the geometries table and the properties table is one-to-many, so if I create a view there will be many records for each feature (duplicate geometry data) – Faz B Nov 24 '18 at 9:01
  • Please guide me if I'm missing something. – Faz B Nov 24 '18 at 10:24
  • 1
    You could be using the JNDI connection, which contains a pool of connections. It is generally not a good idea to keep a connection open at all times, because you would need one connection for every thread (ex: 4 tiles requests in parallel, you don't want a single connection for the 4 requests). Using a connection from the pool should be fairly fast, as it doesn't have to always be re-established to the server ("closing" the connection means putting it back in the pool, which in turn decides to keep it open or to close it for real after some idle time) – JGH Nov 24 '18 at 16:53
  • Thanks JGH for your reply, but how to do that from the code (from a Geotools custom function)? – Faz B Nov 26 '18 at 7:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.