package org.wikibrain.spatial.dao.postgis;
import com.google.common.collect.Maps;
import com.google.common.collect.Sets;
import com.typesafe.config.Config;
import com.typesafe.config.ConfigObject;
import com.vividsolutions.jts.geom.Geometry;
import org.geotools.data.DataStoreFinder;
import org.geotools.data.FeatureSource;
import org.geotools.data.Query;
import org.geotools.factory.CommonFactoryFinder;
import org.geotools.feature.FeatureCollection;
import org.geotools.feature.FeatureIterator;
import org.geotools.feature.simple.SimpleFeatureImpl;
import org.geotools.feature.simple.SimpleFeatureTypeBuilder;
import org.geotools.feature.visitor.CalcResult;
import org.geotools.feature.visitor.UniqueVisitor;
import org.geotools.filter.text.cql2.CQL;
import org.geotools.filter.text.cql2.CQLException;
import org.geotools.jdbc.Index;
import org.geotools.jdbc.JDBCDataStore;
import org.geotools.jdbc.JDBCFeatureStore;
import org.opengis.feature.Feature;
import org.opengis.feature.simple.SimpleFeatureType;
import org.opengis.filter.Filter;
import org.opengis.filter.FilterFactory2;
import org.opengis.filter.expression.PropertyName;
import org.wikibrain.conf.Configuration;
import org.wikibrain.conf.ConfigurationException;
import org.wikibrain.conf.Configurator;
import org.wikibrain.core.dao.DaoException;
import org.wikibrain.core.dao.sql.WpDataSource;
import org.wikibrain.spatial.SpatialContainerMetadata;
import java.io.IOException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
/**
* Created by Brent Hecht on 12/30/13.
* Heavily revised in late March / early April 2014
*/
public class PostGISDB {
private static final Logger LOG = LoggerFactory.getLogger(PostGISDB.class);
private static final String SPATIAL_DB_NAME = "geometries";
private static final String GEOM_FIELD_NAME = "geometry";
private static final String LAYER_FIELD_NAME = "layer_name";
private static final String REF_SYS_FIELD_NAME = "ref_sys_name";
private static final String ITEM_ID_FIELD_NAME = "item_id";
private JDBCDataStore store;
protected static PostGISDB instance; // singleton instance to avoid having multiple JDBCDataStores, as recommended in GeoTools documentation
// private Transaction curSaveTransaction;
public PostGISDB(String dbType, String host, Integer port, String schema, String db, String user, String pwd,
int maxConnections) throws DaoException{
Map<String, Object> params = Maps.newHashMap();
params.put("dbtype", dbType);
params.put("host", host);
params.put("port", port);
params.put("schema", schema);
params.put("database", db);
params.put("user", user);
params.put("passwd", pwd);
params.put("max connections", maxConnections);
initialize(params);
}
public Geometry getGeometry(int itemId, String layerName, String refSysName) throws DaoException{
try {
FeatureSource contents = getFeatureSource();
String cqlQuery = String.format("item_id = %d AND layer_name = '%s' AND ref_sys_name = '%s'", itemId, layerName, refSysName);
Filter f = CQL.toFilter(cqlQuery);
FeatureCollection collection = contents.getFeatures(f);
if (collection.size() == 0) return null;
FeatureIterator iterator = collection.features();
if(!iterator.hasNext()){
iterator.close();
return null;
}
Feature feature = iterator.next();
iterator.close();
return ((Geometry)feature.getDefaultGeometryProperty().getValue());
}catch(Exception e){
throw new DaoException(e);
}
}
public SpatialContainerMetadata getLayerMetadata(String layerName, String refSysName) throws DaoException{
FeatureIterator iterator = getFeatureIteratorForLayer(layerName, refSysName);
int count = 0;
SpatialContainerMetadata.ShapeType shapeType = null;
while(iterator.hasNext()){
Feature f = iterator.next();
count++;
SpatialContainerMetadata.ShapeType curShapeType = SpatialContainerMetadata.getShapeTypeFromGeometry((Geometry)f.getDefaultGeometryProperty().getValue());
if (shapeType == null){
shapeType = curShapeType;
}else{
if (!shapeType.equals(SpatialContainerMetadata.ShapeType.MIXED)) {
if (!curShapeType.equals(shapeType)) {
shapeType = SpatialContainerMetadata.ShapeType.MIXED;
}
}
}
}
iterator.close();
return new SpatialContainerMetadata(layerName, refSysName, count, shapeType);
}
private FeatureIterator getFeatureIteratorForLayer(String layerName, String refSysName) throws DaoException{
try{
FeatureSource contents = getFeatureSource();
String cqlQuery = String.format("layer_name = '%s' AND ref_sys_name = '%s'", layerName, refSysName);
Filter f = CQL.toFilter(cqlQuery);
FeatureCollection collection = contents.getFeatures(f);
if (collection.size() == 0) return null;
FeatureIterator iterator = collection.features();
return iterator;
}catch(Exception e){
throw new DaoException(e);
}
}
/**
* Gets all the layers in a given reference system
* @param refSysName
* @return empty set if no layers or refSys does not exist, otherwise a set with all the layers in the input refsys
* @throws DaoException
*/
public Set<String> getLayersInReferenceSystem(String refSysName) throws DaoException{
try {
String cqlQuery = String.format("ref_sys_name = '%s'", refSysName);
Filter f = CQL.toFilter(cqlQuery);
Set<Object> uniques = getUniqueValues("layer_name", f);
Set<String> rVal = Sets.newHashSet();
for(Object o : uniques){
rVal.add(o.toString());
}
return rVal;
}catch(Exception e){
throw new DaoException(e);
}
}
public void removeLayer(String refSysName, String layerName) throws DaoException {
try {
String cqlQuery = String.format("ref_sys_name = '%s' and layer_name = '%s'", refSysName, layerName);
Filter f = CQL.toFilter(cqlQuery);
((JDBCFeatureStore)getFeatureSource()).removeFeatures(f);
} catch (CQLException e) {
throw new DaoException(e);
} catch (IOException e) {
throw new DaoException(e);
}
}
/**
* Gets all loaded reference systems
* @return
* @throws DaoException
*/
public Set<String> getAllReferenceSystems() throws DaoException{
try {
Set<Object> uniques = getUniqueValues("ref_sys_name", null);
Set<String> rVal = Sets.newHashSet();
if (uniques != null) {
for (Object o : uniques) {
rVal.add(o.toString());
}
}
return rVal;
}catch(Exception e){
throw new DaoException(e);
}
}
/**
* Returns the unique values in colName in the database, pre-filtered with filter f (if not null)
* @param colName
* @param f If null, will not use filter
* @return
* @throws DaoException
*/
private Set<Object> getUniqueValues(String colName, Filter f) throws DaoException{
try {
// get feature source
FeatureSource contents = getFeatureSource();
// prep query
Query query = getQuery();
if (f != null) {
query.setFilter(f);
}
query.setPropertyNames(new String[]{colName});
UniqueVisitor visitor = new UniqueVisitor(colName);
// get unique results
FeatureCollection collection = contents.getFeatures(query);
collection.accepts(visitor, null);
CalcResult result = visitor.getResult();
Set<Object> results = result.toSet();
return results;
}catch(Exception e){
throw new DaoException(e);
}
}
public Map<Integer, Geometry> getAllGeometriesInLayer(String layerName, String refSysName) throws DaoException{
FeatureIterator iterator = getFeatureIteratorForLayer(layerName, refSysName);
if(!iterator.hasNext()){
iterator.close();
return null;
}
Feature feature;
Map<Integer, Geometry> geometries = new HashMap<Integer, Geometry>();
while (iterator.hasNext()){
feature = iterator.next();
geometries.put((Integer)((SimpleFeatureImpl)feature).getAttribute("item_id"), (Geometry) feature.getDefaultGeometryProperty().getValue());
}
iterator.close();
return geometries;
}
public Map<Integer, Geometry> getBulkGeometriesInLayer(List<Integer> idList, String layerName, String refSysName) throws DaoException{
try {
//SQL STACK LIMIT!
//change to iterative if we have performance issue later
int QUERY_SIZE = 500;
if(idList.size() > QUERY_SIZE){
Map<Integer, Geometry> res = getBulkGeometriesInLayer(idList.subList(0, QUERY_SIZE), layerName, refSysName);
res.putAll(getBulkGeometriesInLayer(idList.subList(QUERY_SIZE, idList.size()), layerName, refSysName));
return res;
}
FeatureSource contents = getFeatureSource();
FilterFactory2 ff = CommonFactoryFinder.getFilterFactory2();
//build ref sys clause
PropertyName refSysProperty = ff.property(getRefSysAttributeName());
Filter refSysFilter = ff.equals(refSysProperty, ff.literal(refSysName));
// build layer-related clause
PropertyName layerProperty = ff.property(getLayerAttributeName());
Filter layerFilter = ff.equals(layerProperty, ff.literal(layerName));
List<Filter> idFilters = new java.util.LinkedList<Filter>();
PropertyName idProperty = ff.property(getItemIdAttributeName());
for(Integer i : idList){
idFilters.add(ff.equals(idProperty, ff.literal(i)));
}
Filter idFilter = ff.or(idFilters);
List<Filter> filters = new java.util.LinkedList<Filter>();
filters.add(refSysFilter);
filters.add(layerFilter);
filters.add(idFilter);
Filter finalFilter = ff.and(filters);
FeatureCollection collection = contents.getFeatures(finalFilter);
if (collection.size() == 0) return null;
FeatureIterator iterator = collection.features();
if(!iterator.hasNext()){
iterator.close();
return null;
}
Feature feature;
Map<Integer, Geometry> geometries = new HashMap<Integer, Geometry>();
while (iterator.hasNext()){
feature = iterator.next();
geometries.put((Integer)((SimpleFeatureImpl)feature).getAttribute("item_id"), (Geometry) feature.getDefaultGeometryProperty().getValue());
}
iterator.close();
return geometries;
}catch(Exception e){
throw new DaoException(e);
}
}
private void checkPostgisVersion() throws DaoException {
}
private void initialize(Map<String, Object> manualParameters) throws DaoException {
try {
PostGISVersionChecker versionChecker = new PostGISVersionChecker();
versionChecker.verifyVersion(manualParameters);
store = (JDBCDataStore)DataStoreFinder.getDataStore(manualParameters);
if (needsToBeInitialized()){ // needs to be initialized
LOG.info("Initializing spatial database tables");
try {
SimpleFeatureTypeBuilder builder = new SimpleFeatureTypeBuilder();
builder.setName(SPATIAL_DB_NAME);
builder.add(ITEM_ID_FIELD_NAME, Integer.class);
builder.add(LAYER_FIELD_NAME, String.class);
builder.add(REF_SYS_FIELD_NAME, String.class);
builder.add(GEOM_FIELD_NAME, Geometry.class);
SimpleFeatureType featureType = builder.buildFeatureType();
store.createSchema(featureType);
// note: gist index is created automatically above
Index regIndex = new Index(SPATIAL_DB_NAME, "rs_layer_type", true, ITEM_ID_FIELD_NAME,LAYER_FIELD_NAME,REF_SYS_FIELD_NAME);
Index itemIdIndex = new Index(SPATIAL_DB_NAME, "geometries_item_id", false, ITEM_ID_FIELD_NAME);
store.createIndex(itemIdIndex);
}catch(Exception e){
throw new DaoException(e);
}
}else{
LOG.info("Spatial database tables have already been initialized");
}
}catch(Exception e){
throw new DaoException(e);
}
}
public PostGISDB(Map<String, Object> manualParameters) throws DaoException{
initialize(manualParameters);
}
public JDBCDataStore getRawDataStore() {
return store;
}
public Query getQuery(){
return new Query(SPATIAL_DB_NAME);
}
public FeatureSource getFeatureSource() throws DaoException{
try {
return store.getFeatureSource(SPATIAL_DB_NAME);
}catch(IOException e){
throw new DaoException(e);
}
}
public SimpleFeatureType getSchema() throws DaoException{
try {
return store.getSchema(SPATIAL_DB_NAME);
}catch(IOException e) {
throw new DaoException(e);
}
}
public String getGeometryAttributeName(){
return GEOM_FIELD_NAME;
}
public String getLayerAttributeName(){return LAYER_FIELD_NAME;}
public String getRefSysAttributeName() {return REF_SYS_FIELD_NAME;}
public String getItemIdAttributeName() {return ITEM_ID_FIELD_NAME;}
private boolean needsToBeInitialized() throws DaoException{
try {
// loop through all table names to see if the db name is one of them
for (String typeName : store.getTypeNames()){
if (typeName.equals(SPATIAL_DB_NAME)) return false;
}
return true;
}catch(IOException e){
throw new DaoException(e);
}
}
public void optimize() throws DaoException {
// Hack: wrap the datasource in a WpDataSource to access the helper method.
// We don't close the WpDataSource because it doesn't own the connection.
WpDataSource src = new WpDataSource(store.getDataSource());
src.optimize(SPATIAL_DB_NAME);
}
public static class Provider extends org.wikibrain.conf.Provider<PostGISDB> {
public Provider(Configurator configurator, Configuration config) throws ConfigurationException {
super(configurator, config);
}
@Override
public Class getType() {
return PostGISDB.class;
}
@Override
public String getPath() {
return "spatial.dao.dataSource";
}
@Override
public PostGISDB get(String name, Config config, Map<String, String> runtimeParams) throws ConfigurationException {
try {
if (PostGISDB.instance == null) {
// This loads the parameters directly into a map that can be used by JDBCDataStores in Geotools
// The format in the conf file must match the the JDBCDataStore format
// http://docs.geotools.org/stable/userguide/library/jdbc/datastore.html
Map<String, Object> params = Maps.newHashMap();
ConfigObject cObject = config.root();
for (String key : cObject.keySet()) {
params.put(key, cObject.get(key).unwrapped());
}
PostGISDB.instance = new PostGISDB(params);
}
return PostGISDB.instance;
} catch (DaoException e) {
throw new ConfigurationException(e);
}
}
}
}