Postgres K-V store supporting radius searches

gistfile1.java

package postgres;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

import org.postgis.Point;
import org.postgresql.PGConnection;

class GeoKey {
  
	private Double lat;
	private Double lon;
	private String context;
	private Double radius;
	
	GeoKey setRadius(double radius){
		this.radius = radius;
		return this;
	}
	
	GeoKey setLat(double lat){
		this.lat = lat;
		return this;
	}
	
	GeoKey setLon(double lon){
		this.lon = lon;
		return this;
	}
	
	GeoKey setContext(String context){
		this.context = context;
		return this;
	}
	
	Double getLat(){
		return this.lat;
	}
	
	Double getLon(){
		return this.lon;
	}
	
	String getContext(){
		return this.context;
	}
	
	Double getRadius(){
		return this.radius;
	}
}

public class PostgresStore {
	
	Connection conn;
	
	PreparedStatement insertStatement;
	PreparedStatement updateStatement;
	PreparedStatement getStatement;
	PreparedStatement deleteStatement;
	PreparedStatement radiusQueryStatement;
	
	String tableName;
	
	PostgresStore(String hostName, String dbName, int port, String user, String pass, String tableName) throws Exception {
		
		this.tableName  = tableName;
		Properties props = new Properties();
		props.setProperty("user", user);
		props.setProperty("password", pass);
		conn =  DriverManager.getConnection("jdbc:postgresql://"+hostName+":"+ port+"/"+dbName, props);
		((PGConnection)conn).addDataType("geometry",org.postgis.PGgeometry.class);
		((PGConnection)conn).addDataType("point",org.postgresql.geometric.PGpoint.class);
		
		// create the table if it does not exist already...
		Statement s = conn.createStatement();
		s.execute("CREATE TABLE if not exists "+ this.tableName +"(id SERIAL PRIMARY KEY, geokey GEOGRAPHY(POINT,4326), context varchar(1024), value bytea);");
		
		insertStatement = conn.prepareStatement("insert into "+tableName+"(geokey, context, value) values(ST_GeographyFromText(?),?,?)");
		getStatement = conn.prepareStatement("select value from "+ this.tableName+" where geokey = ST_GeographyFromText(?) and context = ?");
		radiusQueryStatement = conn.prepareStatement("select ST_AsText(geokey) as geokey, value from "+ this.tableName + 
				" where ST_DWithin(geokey, ST_GeographyFromText(?),?);");
		updateStatement = conn.prepareStatement("update "+ tableName+" set value = ? where geokey = ST_GeographyFromText(?) and context = ?");
	}
	
	/**
	 * 
	 * If geoKey contains a radius, perform a bounding box search around the lat, lon in the key. 
	 * Else, the context has to be non-null and perform a exact fetch
	 * 
	 * @param key
	 * @return
	 * @throws Exception
	 */
	public List<byte[]> get(GeoKey key) throws Exception {
		if (key.getRadius() != null){
			// getAll fits better since you need keys also
			return radiusSearch(key);
		} else if (key.getClass() != null){
			return exactQuery(key);
		}
		return new ArrayList<byte[]>(0);
	}
	
	
	private List<byte[]> radiusSearch(GeoKey key){
		List<byte[]> vals = new ArrayList<byte[]>();
		try {
			radiusQueryStatement.setString(1, new Point(key.getLat(), key.getLon()).toString());
			radiusQueryStatement.setDouble(2, key.getRadius());
			ResultSet rs = radiusQueryStatement.executeQuery();
			while (rs.next()){
				vals.add(rs.getBytes("value"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}		
		return vals;
	}
	
	private List<byte[]> exactQuery(GeoKey key){
		List<byte[]> vals = new ArrayList<byte[]>();
		try {
			getStatement.setString(1, new Point(key.getLat(), key.getLon()).toString());
			getStatement.setString(2, key.getContext());
			ResultSet rs = getStatement.executeQuery();
			while (rs.next()){
				vals.add(rs.getBytes("value"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}		
		return vals;
	}
	
	/**
	 * Writes the given value to storage based on the following. 
	 * 
	 * Update if a data point with the triplet {lat, lon, context} already exists. :  
	 * 
	 * Insert/Add the geoKey, value to storage if 
	 * 
	 * 
	 * @param key
	 * @param value
	 * @throws Exception
	 */
	public void put(GeoKey key, byte[] value) throws Exception {
		
		List<byte[]> vals = exactQuery(key);
		if (vals.size() > 0){
			// do an update
			updateStatement.setBytes(1, value);
			updateStatement.setString(2, new Point(key.getLat(), key.getLon()).toString());
			updateStatement.setString(3, key.getContext());
			updateStatement.executeUpdate();
		} else {
			// do an insert
			insertStatement.setString(1, new Point(key.getLat(), key.getLon()).toString());
			insertStatement.setString(2, key.getContext());
			insertStatement.setBytes(3, value);
			insertStatement.executeUpdate();
		}
	}
	
	public void delete(double lat, double lon) throws Exception {
		
	}
	
	
	public void dumpTable() throws Exception {
		Statement s = conn.createStatement(); 
	    ResultSet r = s.executeQuery("select ST_AsText(geokey) as geokey from "+ tableName); 
	    while( r.next() ) {
	      Point point = new Point(r.getString(1));
	      System.out.println(point.getX()+","+ point.getY()); 
	    } 
	    s.close(); 
	}
	
	
	
	public void close() throws Exception {
		if(insertStatement!=null)
			insertStatement.close();
		if(updateStatement!=null)
			updateStatement.close();
		if(deleteStatement!=null)
			deleteStatement.close();
		if (conn != null)
			conn.close();
	}

	/**
	 * @param args
	 */
	public static void main(String[] args) throws Exception {
		// TODO Auto-generated method stub
		PostgresStore pgStore = new PostgresStore("localhost", "voldemort_spatial", 5432, "postgres-user", "postgress-pass", "spatialtest");
		pgStore.put(new GeoKey().setLat(10.2).setLon(11.2).setContext("user1"), "hello postgis".getBytes());		
		pgStore.put(new GeoKey().setLat(10.2).setLon(11.2).setContext("user1"), "hello postgis1".getBytes());
		GeoKey key2 = new GeoKey().setLat(10.2).setLon(11.2).setContext("user2");
		pgStore.put(key2, "hello postgis2".getBytes());
		pgStore.put(new GeoKey().setLat(20.2).setLon(80.2).setContext("user3"), "hello postgis3".getBytes());
		pgStore.put(new GeoKey().setLat(20.2).setLon(80.2).setContext("user4"), "hello postgis4".getBytes());

		System.out.println("get test : "+ new String(pgStore.get(key2).get(0)));
		GeoKey radiusKey = new GeoKey().setLat(1.0).setLon(1.0).setRadius(10000000);
		
		for (byte[] val : pgStore.get(radiusKey)){
			System.out.println("Radius test : "+ new String(val));
		}
		

		pgStore.dumpTable();
		pgStore.close();
	}

}