/*******************************************************************************
* Gisgraphy Project
*
* This library is free software; you can redistribute it and/or
* modify it under the terms of the GNU Lesser General Public
* License as published by the Free Software Foundation; either
* version 2.1 of the License, or (at your option) any later version.
*
* This library is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
* Lesser General Public License for more details.
*
* You should have received a copy of the GNU Lesser General Public
* License along with this library; if not, write to the Free Software
* Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307, USA
*
* Copyright 2008 Gisgraphy project
* David Masclet <davidmasclet@gisgraphy.com>
*
*
*******************************************************************************/
package com.gisgraphy.domain.repository;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;
import javax.persistence.PersistenceException;
import org.apache.commons.lang.ArrayUtils;
import org.apache.solr.client.solrj.SolrQuery;
import org.apache.solr.client.solrj.SolrServerException;
import org.apache.solr.client.solrj.response.QueryResponse;
import org.apache.solr.common.SolrDocument;
import org.hibernate.Criteria;
import org.hibernate.Hibernate;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.criterion.ProjectionList;
import org.hibernate.criterion.Restrictions;
import org.hibernate.type.CustomType;
import org.hibernate.type.Type;
import org.hibernate.util.ArrayHelper;
import org.hibernatespatial.GeometryUserType;
import org.opengis.feature.type.GeometryType;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Required;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.util.Assert;
import com.gisgraphy.domain.geoloc.entity.City;
import com.gisgraphy.domain.geoloc.entity.GisFeature;
import com.gisgraphy.domain.geoloc.entity.OpenStreetMap;
import com.gisgraphy.domain.geoloc.entity.ZipCode;
import com.gisgraphy.domain.geoloc.entity.ZipCodesAware;
import com.gisgraphy.domain.geoloc.entity.event.EventManager;
import com.gisgraphy.domain.geoloc.entity.event.GisFeatureDeleteAllEvent;
import com.gisgraphy.domain.geoloc.entity.event.GisFeatureDeletedEvent;
import com.gisgraphy.domain.geoloc.entity.event.GisFeatureStoredEvent;
import com.gisgraphy.domain.geoloc.entity.event.PlaceTypeDeleteAllEvent;
import com.gisgraphy.domain.valueobject.Constants;
import com.gisgraphy.domain.valueobject.GisFeatureDistance;
import com.gisgraphy.domain.valueobject.SRID;
import com.gisgraphy.fulltext.FullTextFields;
import com.gisgraphy.fulltext.IsolrClient;
import com.gisgraphy.helper.GeolocHelper;
import com.gisgraphy.helper.GisHelper;
import com.gisgraphy.helper.IntrospectionHelper;
import com.gisgraphy.hibernate.criterion.DistanceRestriction;
import com.gisgraphy.hibernate.criterion.ProjectionOrder;
import com.gisgraphy.hibernate.criterion.ResultTransformerUtil;
import com.gisgraphy.hibernate.projection.ProjectionBean;
import com.gisgraphy.hibernate.projection.SpatialProjection;
import com.gisgraphy.importer.ImporterConfig;
import com.vividsolutions.jts.geom.Point;
/**
* Generic Dao for Gis Object (java-5 meaning) It suppose that the PK is of type
* long because its goal is to be used with class gisfeatures and class that
* extends GisFeature. if it is note the case. it is possible to create an other
* inteface<br>
* it adds some method to the GenericDao in order to acess GIS objects
*
* @see GenericDao
* @param <T>
* the type of the object the Gis Dao apply
* @author <a href="mailto:david.masclet@gisgraphy.com">David Masclet</a>
*/
/**
* @author gisgraphy
*
* @param <T>
*/
public class GenericGisDao<T extends GisFeature> extends
GenericDao<T, java.lang.Long> implements IGisDao<T> {
public static final Type GEOMETRY_TYPE = new CustomType(new GeometryUserType());
public static final int MAX_FULLTEXT_RESULTS = 100;
@Autowired
@Qualifier("solrClient")
private IsolrClient solrClient;
private EventManager eventManager;
/**
* Constructor
*
* @param persistentClass
* The specified Class for the GenericGisDao
*/
public GenericGisDao(final Class<T> persistentClass) {
super(persistentClass);
}
/*
* (non-Javadoc)
*
* @see com.gisgraphy.domain.repository.IGisDao#getNearestAndDistanceFrom(com.gisgraphy.domain.geoloc.entity.GisFeature,
* double, int, int)
*/
public List<GisFeatureDistance> getNearestAndDistanceFromGisFeature(
final GisFeature gisFeature, final double distance,
final int firstResult, final int maxResults, boolean includeDistanceField) {
return getNearestAndDistanceFrom(gisFeature.getLocation(), gisFeature
.getId(), distance, firstResult, maxResults, includeDistanceField, persistentClass, false);
}
/*
* (non-Javadoc)
*
* @see com.gisgraphy.domain.repository.IGisDao#getNearestAndDistanceFrom(com.gisgraphy.domain.geoloc.entity.GisFeature,
* double)
*/
public List<GisFeatureDistance> getNearestAndDistanceFromGisFeature(
GisFeature gisFeature, double distance, boolean includeDistanceField) {
return getNearestAndDistanceFrom(gisFeature.getLocation(), gisFeature
.getId(), distance, -1, -1, includeDistanceField, persistentClass, false);
}
/*
* (non-Javadoc)
*
* @see com.gisgraphy.domain.repository.IGisDao#getNearestAndDistanceFrom(com.vividsolutions.jts.geom.Point,
* double)
*/
public List<GisFeatureDistance> getNearestAndDistanceFrom(Point point,
double distance) {
return getNearestAndDistanceFrom(point, 0L, distance, -1, -1, true,
persistentClass, false);
}
/* (non-Javadoc)
* @see com.gisgraphy.domain.repository.IGisDao#getNearestAndDistanceFrom(com.vividsolutions.jts.geom.Point, double, int, int, boolean, boolean)
*/
public List<GisFeatureDistance> getNearestAndDistanceFrom(Point point,
double distance, int firstResult, int maxResults, boolean includeDistanceField, boolean isMunicipality) {
return getNearestAndDistanceFrom(point, 0L, distance, firstResult,
maxResults,includeDistanceField, persistentClass, isMunicipality);
}
/**
* base method for all findNearest*
*
* @param point
* The point from which we want to find GIS Object
* @param pointId
* the id of the point that we don't want to be include, it
* is used to not include the gisFeature from which we want
* to find the nearest
* @param distance
* distance The radius in meters
* @param firstResult
* the firstResult index (for pagination), numbered from 1,
* if < 1 : it will not be taken into account
* @param maxResults
* The Maximum number of results to retrieve (for
* pagination), if <= 0 : it will not be taken into acount
* @param requiredClass
* the class of the object to be retireved
* @param isMunicipality whether we should filter on city that are flag as 'municipality'.
act as a filter, if false it doesn't filters( false doesn't mean that we return non municipality)
* @return A List of GisFeatureDistance with the nearest elements or an
* emptylist (never return null), ordered by distance.<u>note</u>
* the specified gisFeature will not be included into results
* @see GisFeatureDistance
* @return a list of gisFeature (never return null but an empty list)
*/
@SuppressWarnings("unchecked")
protected List<GisFeatureDistance> getNearestAndDistanceFrom(
final Point point, final Long pointId, final double distance,
final int firstResult, final int maxResults,
final boolean includeDistanceField,
final Class<? extends GisFeature> requiredClass, final boolean isMunicipality) {
Assert.notNull(point);
return (List<GisFeatureDistance>) this.getHibernateTemplate().execute(
new HibernateCallback() {
public Object doInHibernate(Session session)
throws PersistenceException {
Criteria criteria = session
.createCriteria(requiredClass);
if (maxResults > 0) {
criteria = criteria.setMaxResults(maxResults);
}
if (firstResult >= 1) {
criteria = criteria.setFirstResult(firstResult - 1);
}
criteria = criteria.add(new DistanceRestriction(point,
distance));
List<String> fieldList = IntrospectionHelper
.getFieldsAsList(requiredClass);
ProjectionList projections = ProjectionBean.fieldList(
fieldList,true);
if (includeDistanceField){
projections.add(
SpatialProjection.distance_sphere(point,GisFeature.LOCATION_COLUMN_NAME).as(
"distance"));
}
criteria.setProjection(projections);
if (pointId != 0) {
// remove The From Point
criteria = criteria.add(Restrictions.not(Restrictions.idEq(pointId)));
}
if (includeDistanceField){
criteria.addOrder(new ProjectionOrder("distance"));
}
if (isMunicipality && (requiredClass == City.class || requiredClass == GisFeature.class)){
criteria.add(Restrictions.eq(City.MUNICIPALITY_FIELD_NAME, isMunicipality));
}
criteria.setCacheable(true);
List<Object[]> queryResults = criteria.list();
String[] aliasList;
if (includeDistanceField){
aliasList = (String[]) ArrayUtils
.add(
IntrospectionHelper
.getFieldsAsArray(requiredClass),
"distance");
} else {
aliasList = IntrospectionHelper
.getFieldsAsArray(requiredClass);
}
int idPropertyIndexInAliasList=0;
for (int i=0;i<aliasList.length;i++){
if (aliasList[i]=="id"){
idPropertyIndexInAliasList = i;
break;
}
}
boolean hasZipCodesProperty = ZipCodesAware.class.isAssignableFrom(requiredClass);
Map<Long, Set<String>> idToZipCodesMap = null;
if (hasZipCodesProperty && queryResults.size()>0){
List<Long> ids = new ArrayList<Long>();
for (Object[] tuple: queryResults){
ids.add((Long)tuple[idPropertyIndexInAliasList]);
}
String zipCodeQuery = "SELECT code as code,gisfeature as id FROM "+ZipCode.class.getSimpleName().toLowerCase() +" zip where zip.gisfeature in (:ids)" ;
Query qry = session.createSQLQuery(zipCodeQuery).addScalar("code", Hibernate.STRING).addScalar("id", Hibernate.LONG);
qry.setCacheable(true);
qry.setParameterList("ids", ids);
List<Object[]> zipCodes = (List<Object[]>) qry.list();
if (zipCodes.size() > 0) {
idToZipCodesMap = new HashMap<Long, Set<String>>();
for (Object[] zipCode : zipCodes){
Long idFromZipcode = (Long) zipCode[1];
Set<String> zipCodesFromMap = idToZipCodesMap.get(idFromZipcode);
if (zipCodesFromMap == null){
Set<String> zipCodesToAdd = new HashSet<String>();
idToZipCodesMap.put(idFromZipcode, zipCodesToAdd);
zipCodesFromMap = zipCodesToAdd;
}
zipCodesFromMap.add((String)zipCode[0]);
}
}
}
List<GisFeatureDistance> results = ResultTransformerUtil
.transformToGisFeatureDistance(
aliasList,
queryResults,idToZipCodesMap,requiredClass);
return results;
}
});
}
/*
* (non-Javadoc)
*
* @see com.gisgraphy.domain.repository.IGisDao#findByName(java.lang.String)
*/
@SuppressWarnings("unchecked")
public List<T> listByName(final String name) {
Assert.notNull(name);
return (List<T>) this.getHibernateTemplate().execute(
new HibernateCallback() {
public Object doInHibernate(Session session)
throws PersistenceException {
String queryString = "from "
+ persistentClass.getSimpleName()
+ " as c where c.name= ?";
Query qry = session.createQuery(queryString);
qry.setCacheable(true);
qry.setParameter(0, name);
List<T> results = (List<T>) qry.list();
if (results == null) {
results = new ArrayList<T>();
}
return results;
}
});
}
/*
* (non-Javadoc)
*
* @see com.gisgraphy.domain.repository.IGisDao#getByFeatureId(java.lang.Long)
*/
@SuppressWarnings("unchecked")
public T getByFeatureId(final Long featureId) {
Assert.notNull(featureId);
return (T) this.getHibernateTemplate().execute(new HibernateCallback() {
public Object doInHibernate(Session session)
throws PersistenceException {
String queryString = "from " + persistentClass.getSimpleName()
+ " as g where g.featureId= ?";
Query qry = session.createQuery(queryString);
qry.setCacheable(true);
qry.setParameter(0, featureId);
T result = (T) qry.uniqueResult();
return result;
}
});
}
@SuppressWarnings("unchecked")
public T getByOpenStreetMapId(final Long openstreetmapId) {
Assert.notNull(openstreetmapId);
return (T) this.getHibernateTemplate().execute(
new HibernateCallback() {
public Object doInHibernate(Session session)
throws PersistenceException {
String queryString = "from "
+ persistentClass.getSimpleName()
+ " as c where c.openstreetmapId= ?";
Query qry = session.createQuery(queryString);
qry.setMaxResults(1);
//we need to limit to 1 because a street can be in two countries
qry.setCacheable(true);
qry.setParameter(0, openstreetmapId);
T result = (T) qry.uniqueResult();
return result;
}
});
}
/*
* (non-Javadoc)
*
* @see com.gisgraphy.domain.repository.IGisDao#getDirty()
*/
@SuppressWarnings("unchecked")
public List<T> getDirties() {
return (List<T>) this.getHibernateTemplate().execute(
new HibernateCallback() {
public Object doInHibernate(final Session session)
throws PersistenceException {
final String queryString = "from "
+ persistentClass.getSimpleName()
+ " as g where g.featureCode ='"
+ ImporterConfig.DEFAULT_FEATURE_CODE
+ "' or g.location=? or g.featureClass='"
+ ImporterConfig.DEFAULT_FEATURE_CLASS + "'";
final Query qry = session.createQuery(queryString);
qry.setParameter(0, GeolocHelper.createPoint(0F, 0F),
GEOMETRY_TYPE);
qry.setCacheable(true);
List<T> result = (List<T>) qry.list();
if (result == null) {
result = new ArrayList<T>();
}
return result;
}
});
}
/*
* (non-Javadoc)
*
* @see com.gisgraphy.domain.repository.GenericDao#save(java.lang.Object)
*/
@Override
public T save(T GisFeature) {
T savedgisFeature = super.save(GisFeature);
GisFeatureStoredEvent CreatedEvent = new GisFeatureStoredEvent(
GisFeature);
eventManager.handleEvent(CreatedEvent);
return savedgisFeature;
}
/*
* (non-Javadoc)
*
* @see com.gisgraphy.domain.repository.GenericDao#remove(java.lang.Object)
*/
@Override
public void remove(T gisFeature) {
super.remove(gisFeature);
GisFeatureDeletedEvent gisFeatureDeletedEvent = new GisFeatureDeletedEvent(
gisFeature);
eventManager.handleEvent(gisFeatureDeletedEvent);
}
/*
* (non-Javadoc)
*
* @see com.gisgraphy.domain.repository.IGisDao#getByFeatureIds(java.util.List)
*/
@SuppressWarnings("unchecked")
public List<T> listByFeatureIds(final List<Long> ids) {
if (ids == null || ids.size() == 0) {
return new ArrayList<T>();
}
return (List<T>) this.getHibernateTemplate().execute(
new HibernateCallback() {
public Object doInHibernate(final Session session)
throws PersistenceException {
final String queryString = "from "
+ persistentClass.getSimpleName()
+ " as g where g.featureId in (:ids)";
final Query qry = session.createQuery(queryString);
qry.setParameterList("ids", ids);
qry.setCacheable(true);
List<T> result = (List<T>) qry.list();
if (result == null) {
result = new ArrayList<T>();
}
return result;
}
});
}
/*
* (non-Javadoc)
*
* @see com.gisgraphy.domain.repository.IGisDao#getFromText(java.lang.String,
* boolean)
*/
public List<T> listFromText(String name, boolean includeAlternateNames) {
return listFromText(name, includeAlternateNames, persistentClass);
}
/**
* Do a full text search for the given name. The search will be case,
* iso-latin, comma-separated insensitive<br>
* search for 'saint-André', 'saint-Andre', 'SaInT-andré', 'st-andré', etc
* will return the same results. <u>note</u> : search for zipcode too
* Polymorphism is not supported, e.g : if clazz=GisFeature : the results
* will only be of that type and no other feature type (e.g : City that
* extends gisFeature...etc) will be returned. The results will be sort by
* relevance.
*
* @param name
* The name to search for
* @param includeAlternateNames
* Wether we search in the alternatenames too
* @param clazz
* specify the features we want to search for, if null : no
* restriction is apply
* @return a list of gisFeatures of type of the class for the given text.
* the max list size is {@link GenericGisDao#MAX_FULLTEXT_RESULTS};
* @see IGisFeatureDao#listAllFeaturesFromText(String, boolean)
*/
protected List<T> listFromText(String name, boolean includeAlternateNames,
Class<T> clazz) {
logger.debug("getFromText " + name);
// Set up a simple query
// Check for a null or empty string query
if (name == null || name.length() == 0) {
return new ArrayList<T>();
}
SolrQuery query = new SolrQuery();
String namefield = FullTextFields.ALL_NAME.getValue();
if (!includeAlternateNames) {
namefield = FullTextFields.NAME.getValue();
}
String queryString = "(" + namefield + ":\"" + name + "\" OR "
+ FullTextFields.ZIPCODE.getValue() + ":\"" + name + "\")";
if (clazz != null) {
queryString += " AND placetype:" + persistentClass.getSimpleName();
}
query.setQuery(queryString);
query.setQueryType(Constants.SolrQueryType.advanced.toString());
query.setFields(FullTextFields.FEATUREID.getValue());
query.setRows(MAX_FULLTEXT_RESULTS);
QueryResponse results = null;
try {
results = solrClient.getServer().query(query);
} catch (SolrServerException e) {
throw new RuntimeException(e);
}
List<Long> ids = new ArrayList<Long>();
for (SolrDocument doc : results.getResults()) {
ids.add((Long) doc.getFieldValue(FullTextFields.FEATUREID
.getValue()));
}
// log
List<T> gisFeatureList = this.listByFeatureIds(ids);
if (logger.isDebugEnabled()) {
logger.debug("search on " + name + " returns "
+ gisFeatureList.size());
for (GisFeature gisFeature : gisFeatureList) {
logger.debug("search on " + name + " returns "
+ gisFeature.getName());
}
}
return gisFeatureList;
}
/*
* (non-Javadoc)
*
* @see com.gisgraphy.domain.repository.GenericDao#deleteAll(java.util.List)
*/
@Override
public void deleteAll(List<T> list) {
super.deleteAll(list);
GisFeatureDeleteAllEvent gisFeatureDeleteAllEvent = new GisFeatureDeleteAllEvent(
list);
eventManager.handleEvent(gisFeatureDeleteAllEvent);
}
/*
* (non-Javadoc)
*
* @see com.gisgraphy.domain.repository.GenericDao#deleteAll()
*/
@Override
public int deleteAll() {
int numberOfGisDeleted = super.deleteAll();
PlaceTypeDeleteAllEvent placeTypeDeleteAllEvent = new PlaceTypeDeleteAllEvent(
this.getPersistenceClass());
eventManager.handleEvent(placeTypeDeleteAllEvent);
return numberOfGisDeleted;
}
/*
* (non-Javadoc)
*
* @see com.gisgraphy.domain.repository.IGisDao#getEager(java.lang.Long)
*/
@SuppressWarnings("unchecked")
public T getEager(final Long id) {
Assert.notNull(id, "Can not retrieve an Ogject with a null id");
T returnValue = null;
try {
return (T) this.getHibernateTemplate().execute(
new HibernateCallback() {
public Object doInHibernate(Session session)
throws PersistenceException {
String queryString = "from "
+ persistentClass.getSimpleName()
+ " o where o.id=" + id;
Query qry = session.createQuery(queryString);
qry.setCacheable(true);
GisFeature feature = (GisFeature) qry
.uniqueResult();
feature.getAdm().getAdm1Code();
feature.getAlternateNames().size();
return feature;
}
});
} catch (Exception e) {
logger.info("could not retrieve object of type "
+ persistentClass.getSimpleName() + " with id " + id, e);
}
return returnValue;
}
@Required
public void setEventManager(EventManager eventManager) {
this.eventManager = eventManager;
}
/* (non-Javadoc)
* @see com.gisgraphy.domain.repository.IGisDao#createGISTIndexForLocationColumn()
*/
public void createGISTIndexForLocationColumn() {
this.getHibernateTemplate().execute(
new HibernateCallback() {
public Object doInHibernate(Session session)
throws PersistenceException {
session.flush();
logger.info("will create GIST index for "+persistentClass.getSimpleName());
String locationIndexName = "locationIndex"+persistentClass.getSimpleName();
logger.info("checking if "+locationIndexName+" exists");
String checkingLocationIndex= "SELECT 1 FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace WHERE c.relname = '"+locationIndexName+"'";
Query checkingLocationIndexQuery = session.createSQLQuery(checkingLocationIndex);
Object locationIndexExists = checkingLocationIndexQuery.uniqueResult();
if (locationIndexExists != null){
logger.info("will create GIST index for the "+OpenStreetMap.SHAPE_COLUMN_NAME+" column");
String createIndex = "CREATE INDEX "+locationIndexName+" ON "+persistentClass.getSimpleName().toLowerCase()+" USING GIST (location)";
Query createIndexQuery = session.createSQLQuery(createIndex);
createIndexQuery.executeUpdate();
} else {
logger.info("won't create GIST index for "+persistentClass.getSimpleName()+" because it already exists");
}
return null;
}
});
}
@SuppressWarnings("unchecked")
public T getNearest(final Point location,final String countryCode,final boolean filterMunicipality,final int distance) {
Assert.notNull(location);
return (T) this.getHibernateTemplate().execute(new HibernateCallback() {
public Object doInHibernate(Session session)
throws PersistenceException {
String pointAsString = "ST_GeometryFromText('POINT("+location.getX()+" "+location.getY()+")',"+SRID.WGS84_SRID.getSRID()+")";
/* String bbox = "st_setSRID(cast ('BOX3D(39.875947845588854 -6.649904839690944,57.85738955675488 11.316505067046899)'as box3d), 4326)";
String bbox2 = "ST_MakeEnvelope(39.875947845588854, -6.649904839690944,57.85738955675488, 11.316505067046899, 4326)";*/
String queryString = "from " + persistentClass.getSimpleName()
+ " as c where st_distance_sphere(c.location,"+pointAsString+") < "+distance
//+" AND st_contains("+bbox2+",c.location)=true ";
+ " AND "+GisHelper.makeEnvelope("c", location.getY(),location.getX(), distance)
//GisHelper.getBoundingBox(criteriaQuery.getSQLAlias(criteria), this.point
// .getY(), this.point.getX(), distance)
;//left outer join c.zipCodes z
if (filterMunicipality){
queryString+=" and c.municipality=true";
}
if (countryCode!=null ){
queryString+=" and c.countryCode='"+countryCode+"'";
}
queryString = queryString+ " order by st_distance_sphere(c.location,"+pointAsString+")";
Query qry = session.createQuery(queryString).setMaxResults(1);
//qry.setParameter("point2", location);
T result = (T) qry.uniqueResult();
return result;
}
});
}
@SuppressWarnings("unchecked")
public List<T> getNearests(final Point location,final String countryCode,final boolean filterMunicipality,final int distance,final int limit) {
Assert.notNull(location);
return (List<T>) this.getHibernateTemplate().execute(new HibernateCallback() {
public Object doInHibernate(Session session)
throws PersistenceException {
String pointAsString = "ST_GeometryFromText('POINT("+location.getX()+" "+location.getY()+")',"+SRID.WGS84_SRID.getSRID()+")";
/* String bbox = "st_setSRID(cast ('BOX3D(39.875947845588854 -6.649904839690944,57.85738955675488 11.316505067046899)'as box3d), 4326)";
String bbox2 = "ST_MakeEnvelope(39.875947845588854, -6.649904839690944,57.85738955675488, 11.316505067046899, 4326)";*/
String queryString = "from " + persistentClass.getSimpleName()
+ " as c where st_distance_sphere(c.location,"+pointAsString+") < "+distance
//+" AND st_contains("+bbox2+",c.location)=true ";
+ " AND "+GisHelper.makeEnvelope("c", location.getY(),location.getX(), distance)
//GisHelper.getBoundingBox(criteriaQuery.getSQLAlias(criteria), this.point
// .getY(), this.point.getX(), distance)
;//left outer join c.zipCodes z
if (filterMunicipality){
queryString+=" and c.municipality=true";
}
if (countryCode!=null ){
queryString+=" and c.countryCode='"+countryCode+"'";
}
queryString = queryString+ " order by st_distance_sphere(c.location,"+pointAsString+")";
Query qry = session.createQuery(queryString);
if (limit >0){
qry.setMaxResults(limit);
}
//qry.setParameter("point2", location);
List<T> results = (List<T>) qry.list();
if (results == null) {
results = new ArrayList<T>();
}
return results;
}
});
}
public void createGISTIndexForShapeColumn() {
this.getHibernateTemplate().execute(
new HibernateCallback() {
public Object doInHibernate(Session session)
throws PersistenceException {
session.flush();
logger.info("will create GIST index for "+persistentClass.getSimpleName().toLowerCase()+" shape column");
String shapeIndexName = "shapeIndex"+persistentClass.getSimpleName();
logger.info("checking if "+shapeIndexName+" exists");
String checkingShapeIndex= "SELECT 1 FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace WHERE c.relname = '"+shapeIndexName+"'";
Query checkingShapeIndexQuery = session.createSQLQuery(checkingShapeIndex);
Object shapeIndexExists = checkingShapeIndexQuery.uniqueResult();
if (shapeIndexExists != null){
logger.info("will create GIST index for the "+OpenStreetMap.SHAPE_COLUMN_NAME+" column");
String createIndex = "CREATE INDEX "+shapeIndexName+" ON "+persistentClass.getSimpleName().toLowerCase()+" USING GIST ("+GisFeature.SHAPE_COLUMN_NAME+")";
Query createIndexQuery = session.createSQLQuery(createIndex);
createIndexQuery.executeUpdate();
} else {
logger.info("won't create GIST index for "+persistentClass.getSimpleName()+" because it already exists");
}
return null;
}
});
}
public String getShapeAsWKTByFeatureId(final Long featureId) {
if (featureId ==null){
return null;
}
return (String) this.getHibernateTemplate().execute(
new HibernateCallback() {
public Object doInHibernate(Session session)
throws PersistenceException {
String queryString = "select ST_AsText("+GisFeature.SHAPE_COLUMN_NAME+") from " + persistentClass.getSimpleName()
+ " as g where g.featureId= ?";
Query qry = session.createQuery(queryString);
qry.setParameter(0, featureId);
qry.setCacheable(true);
return (String) qry.uniqueResult();
}
});
}
}