[postgis-users] calling postgis functions from jdbc

[Smith Roman]

Hello guys,

I want to execute the postgis function ST_Within from a java application using jdbc. My function executes successfully but I am not getting the result I am expecting. My function returns false when it should be true. Since I am able to view the point and polygon features in udig. (The point is actually within the polygon).  I want to do a point in polygon test i.e. test if a point feature is within a polygon feature. The two layers use epsg 4326.

Here is the code I am using. I will like to know if my code is okay:


public ArrayList<SearchResultDTO> searchTown(String town, String state)
    {

        ArrayList<SearchResultDTO> searchResults = new ArrayList<SearchResultDTO>();
        SearchResultDTO searchResultDTO = null;

    
           query = "select a.gid, a.the_geom from places a where name like ?";
     

        try {

            logger.info(query);

          PreparedStatement prepstmt = pgCon.prepareStatement(query);
            prepstmt.setString(1, town + "%");
            //prepstmt.setString(2, state);

           ResultSet rset = prepstmt.executeQuery();

            while (rset.next()) {

              PGgeometry geom = (PGgeometry)rset.getObject(2);
               containedInState(geom, state);

                searchResultDTO = new SearchResultDTO();
                //result = true;
               searchResultDTO = getTown(rset.getInt(1));
               searchResults.add(searchResultDTO);
            }

            rset.close();
            prepstmt.close();
        } catch (SQLException e) {
            logger.info(e.getMessage());
            e.printStackTrace();
        } 

        return searchResults;
    }



public boolean containedInState(PGgeometry pgGeometry, String state)
{
    Boolean result = null;

    CallableStatement upperProc = null;

        
           query2 = "{ ? = call ST_Within(?, ?) }";

                           
          query = "select b.the_geom from nglga b where b.statename = ? limit 1";
          logger.info(query);

        try {

            PreparedStatement prepstmt = pgCon.prepareStatement(query);
            prepstmt.setString(1, state);
            //prepstmt.setString(2, state);

           ResultSet rset = prepstmt.executeQuery();

           if ( rset.next() )
           {
              
             PGgeometry geom = (PGgeometry)rset.getObject(1);
             
             logger.info(query2);

            upperProc = pgCon.prepareCall(query2);
            upperProc.registerOutParameter(1, Types.BOOLEAN);
            upperProc.setObject(2, pgGeometry);
            upperProc.setObject(3, geom);
           //  upperProc.setObject(2, geom);
           // upperProc.setObject(3, pgGeometry);
            upperProc.execute();

            result = upperProc.getBoolean(1);

            if (result)
             logger.info("outcome: true " );
            else
              logger.info("outcome: false " );

            }
             
           upperProc.close();
            rset.close();
            
        } 
        catch (SQLException e)
        {
            logger.info(e.getMessage());
            e.printStackTrace();
        }

    return result;
}




 public Connection getPostGISConnection() throws Exception
     {
        Connection con = null;
       
         String url = "jdbc:postgresql://192.168.56.130:5432/postgis";


        logger.info(" Get postgis database connection ......... ");

          //  DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

        //url = "jdbc:oracle:thin:@" + hostName + ":" + portNo + ":" + sid;

          Class.forName("org.postgresql.Driver");
          con = DriverManager.getConnection(url, "postgres", "test");

          ((org.postgresql.PGConnection)con).addDataType("geometry","org.postgis.PGgeometry");
          ((org.postgresql.PGConnection)con).addDataType("box3d","org.postgis.PGbox3d");


       
            logger.info("postgis database connection successful ! ");

        return con;
    }