View Javadoc
1   /*******************************************************************************
2    *   Gisgraphy Project 
3    * 
4    *   This library is free software; you can redistribute it and/or
5    *   modify it under the terms of the GNU Lesser General Public
6    *   License as published by the Free Software Foundation; either
7    *   version 2.1 of the License, or (at your option) any later version.
8    * 
9    *   This library is distributed in the hope that it will be useful,
10   *   but WITHOUT ANY WARRANTY; without even the implied warranty of
11   *   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
12   *   Lesser General Public License for more details.
13   * 
14   *   You should have received a copy of the GNU Lesser General Public
15   *   License along with this library; if not, write to the Free Software
16   *   Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307, USA
17   * 
18   *  Copyright 2008  Gisgraphy project 
19   *  David Masclet <davidmasclet@gisgraphy.com>
20   *  
21   *  
22   *******************************************************************************/
23  package com.gisgraphy.domain.repository;
24  
25  import java.util.ArrayList;
26  import java.util.HashMap;
27  import java.util.HashSet;
28  import java.util.List;
29  import java.util.Map;
30  import java.util.Set;
31  
32  import javax.persistence.PersistenceException;
33  
34  import org.apache.commons.lang.ArrayUtils;
35  import org.apache.solr.client.solrj.SolrQuery;
36  import org.apache.solr.client.solrj.SolrServerException;
37  import org.apache.solr.client.solrj.response.QueryResponse;
38  import org.apache.solr.common.SolrDocument;
39  import org.hibernate.Criteria;
40  import org.hibernate.Hibernate;
41  import org.hibernate.Query;
42  import org.hibernate.Session;
43  import org.hibernate.criterion.ProjectionList;
44  import org.hibernate.criterion.Restrictions;
45  import org.hibernate.type.CustomType;
46  import org.hibernate.type.Type;
47  import org.hibernatespatial.GeometryUserType;
48  import org.springframework.beans.factory.annotation.Autowired;
49  import org.springframework.beans.factory.annotation.Qualifier;
50  import org.springframework.beans.factory.annotation.Required;
51  import org.springframework.orm.hibernate3.HibernateCallback;
52  import org.springframework.util.Assert;
53  
54  import com.gisgraphy.domain.geoloc.entity.City;
55  import com.gisgraphy.domain.geoloc.entity.GisFeature;
56  import com.gisgraphy.domain.geoloc.entity.OpenStreetMap;
57  import com.gisgraphy.domain.geoloc.entity.ZipCode;
58  import com.gisgraphy.domain.geoloc.entity.ZipCodesAware;
59  import com.gisgraphy.domain.geoloc.entity.event.EventManager;
60  import com.gisgraphy.domain.geoloc.entity.event.GisFeatureDeleteAllEvent;
61  import com.gisgraphy.domain.geoloc.entity.event.GisFeatureDeletedEvent;
62  import com.gisgraphy.domain.geoloc.entity.event.GisFeatureStoredEvent;
63  import com.gisgraphy.domain.geoloc.entity.event.PlaceTypeDeleteAllEvent;
64  import com.gisgraphy.domain.valueobject.Constants;
65  import com.gisgraphy.domain.valueobject.GisFeatureDistance;
66  import com.gisgraphy.domain.valueobject.SRID;
67  import com.gisgraphy.fulltext.FullTextFields;
68  import com.gisgraphy.fulltext.IsolrClient;
69  import com.gisgraphy.helper.GeolocHelper;
70  import com.gisgraphy.helper.IntrospectionHelper;
71  import com.gisgraphy.hibernate.criterion.DistanceRestriction;
72  import com.gisgraphy.hibernate.criterion.ProjectionOrder;
73  import com.gisgraphy.hibernate.criterion.ResultTransformerUtil;
74  import com.gisgraphy.hibernate.projection.ProjectionBean;
75  import com.gisgraphy.hibernate.projection.SpatialProjection;
76  import com.gisgraphy.importer.ImporterConfig;
77  import com.vividsolutions.jts.geom.Point;
78  
79  /**
80   * Generic Dao for Gis Object (java-5 meaning) It suppose that the PK is of type
81   * long because its goal is to be used with class gisfeatures and class that
82   * extends GisFeature. if it is note the case. it is possible to create an other
83   * inteface<br>
84   * it adds some method to the GenericDao in order to acess GIS objects
85   * 
86   * @see GenericDao
87   * @param <T>
88   *                the type of the object the Gis Dao apply
89   * @author <a href="mailto:david.masclet@gisgraphy.com">David Masclet</a>
90   */
91  /**
92   * @author gisgraphy
93   *
94   * @param <T>
95   */
96  public class GenericGisDao<T extends GisFeature> extends
97  	GenericDao<T, java.lang.Long> implements IGisDao<T> {
98  
99      public static final Type GEOMETRY_TYPE = new CustomType(
100 	    GeometryUserType.class, null);
101 
102     public static final int MAX_FULLTEXT_RESULTS = 100;
103 
104     @Autowired
105     @Qualifier("solrClient")
106     private IsolrClient solrClient;
107 
108     private EventManager eventManager;
109 
110     /**
111      * Constructor
112      * 
113      * @param persistentClass
114      *                The specified Class for the GenericGisDao
115      */
116     public GenericGisDao(final Class<T> persistentClass) {
117 	super(persistentClass);
118     }
119 
120     /*
121      * (non-Javadoc)
122      * 
123      * @see com.gisgraphy.domain.repository.IGisDao#getNearestAndDistanceFrom(com.gisgraphy.domain.geoloc.entity.GisFeature,
124      *      double, int, int)
125      */
126     public List<GisFeatureDistance> getNearestAndDistanceFromGisFeature(
127 	    final GisFeature gisFeature, final double distance,
128 	    final int firstResult, final int maxResults, boolean includeDistanceField) {
129 	return getNearestAndDistanceFrom(gisFeature.getLocation(), gisFeature
130 		.getId(), distance, firstResult, maxResults, includeDistanceField, persistentClass, false);
131     }
132 
133     /*
134      * (non-Javadoc)
135      * 
136      * @see com.gisgraphy.domain.repository.IGisDao#getNearestAndDistanceFrom(com.gisgraphy.domain.geoloc.entity.GisFeature,
137      *      double)
138      */
139     public List<GisFeatureDistance> getNearestAndDistanceFromGisFeature(
140 	    GisFeature gisFeature, double distance, boolean includeDistanceField) {
141 	return getNearestAndDistanceFrom(gisFeature.getLocation(), gisFeature
142 		.getId(), distance, -1, -1, includeDistanceField, persistentClass, false);
143     }
144 
145     /*
146      * (non-Javadoc)
147      * 
148      * @see com.gisgraphy.domain.repository.IGisDao#getNearestAndDistanceFrom(com.vividsolutions.jts.geom.Point,
149      *      double)
150      */
151     public List<GisFeatureDistance> getNearestAndDistanceFrom(Point point,
152 	    double distance) {
153 	return getNearestAndDistanceFrom(point, 0L, distance, -1, -1, true,
154 		persistentClass, false);
155     }
156 
157     
158     /* (non-Javadoc)
159      * @see com.gisgraphy.domain.repository.IGisDao#getNearestAndDistanceFrom(com.vividsolutions.jts.geom.Point, double, int, int, boolean, boolean)
160      */
161     public List<GisFeatureDistance> getNearestAndDistanceFrom(Point point,
162 	    double distance, int firstResult, int maxResults, boolean includeDistanceField, boolean isMunicipality) {
163 	return getNearestAndDistanceFrom(point, 0L, distance, firstResult,
164 		maxResults,includeDistanceField, persistentClass, isMunicipality);
165     }
166 
167     /**
168      * base method for all findNearest* 
169      * 
170      * @param point
171      *                The point from which we want to find GIS Object
172      * @param pointId
173      *                the id of the point that we don't want to be include, it
174      *                is used to not include the gisFeature from which we want
175      *                to find the nearest
176      * @param distance
177      *                distance The radius in meters
178      * @param firstResult
179      *                the firstResult index (for pagination), numbered from 1,
180      *                if < 1 : it will not be taken into account
181      * @param maxResults
182      *                The Maximum number of results to retrieve (for
183      *                pagination), if <= 0 : it will not be taken into acount
184      * @param requiredClass
185      *                the class of the object to be retireved
186      * @param isMunicipality whether we should filter on city that are flag as 'municipality'.
187 						act as a filter, if false it doesn't filters( false doesn't mean that we return non municipality)
188      * @return A List of GisFeatureDistance with the nearest elements or an
189      *         emptylist (never return null), ordered by distance.<u>note</u>
190      *         the specified gisFeature will not be included into results
191      * @see GisFeatureDistance
192      * @return a list of gisFeature (never return null but an empty list)
193      */
194     @SuppressWarnings("unchecked")
195     protected List<GisFeatureDistance> getNearestAndDistanceFrom(
196 	    final Point point, final Long pointId, final double distance,
197 	    final int firstResult, final int maxResults,
198 	    final boolean includeDistanceField,
199 	    final Class<? extends GisFeature> requiredClass, final boolean isMunicipality) {
200 	Assert.notNull(point);
201 	return (List<GisFeatureDistance>) this.getHibernateTemplate().execute(
202 		new HibernateCallback() {
203 
204 		    public Object doInHibernate(Session session)
205 			    throws PersistenceException {
206 			Criteria criteria = session
207 				.createCriteria(requiredClass);
208 			
209 			if (maxResults > 0) {
210 			    criteria = criteria.setMaxResults(maxResults);
211 			}
212 			if (firstResult >= 1) {
213 			    criteria = criteria.setFirstResult(firstResult - 1);
214 			}
215 			criteria = criteria.add(new DistanceRestriction(point,
216 				distance));
217 			List<String> fieldList = IntrospectionHelper
218 				.getFieldsAsList(requiredClass);
219 			ProjectionList projections = ProjectionBean.fieldList(
220 				fieldList,true);
221 			if (includeDistanceField){
222 			    projections.add(
223 				SpatialProjection.distance_sphere(point,GisFeature.LOCATION_COLUMN_NAME).as(
224 					"distance"));
225 			}
226 			criteria.setProjection(projections);
227 			if (pointId != 0) {
228 			    // remove The From Point
229 			    criteria = criteria.add(Restrictions.not(Restrictions.idEq(pointId)));
230 			}
231 			if (includeDistanceField){
232 			    criteria.addOrder(new ProjectionOrder("distance"));
233 			}
234 			if (isMunicipality && (requiredClass == City.class || requiredClass == GisFeature.class)){
235 				criteria.add(Restrictions.eq(City.MUNICIPALITY_FIELD_NAME, isMunicipality));
236 			}
237 			
238 			criteria.setCacheable(true);
239 			List<Object[]> queryResults = criteria.list();
240 			
241 			String[] aliasList;
242 			if (includeDistanceField){
243 			aliasList = (String[]) ArrayUtils
244 				.add(
245 					IntrospectionHelper
246 						.getFieldsAsArray(requiredClass),
247 					"distance");
248 			} else {
249 			    aliasList = IntrospectionHelper
250 				.getFieldsAsArray(requiredClass);
251 			}
252 			int idPropertyIndexInAliasList=0;
253 			for (int i=0;i<aliasList.length;i++){
254 			    if (aliasList[i]=="id"){
255 				idPropertyIndexInAliasList = i;
256 				break;
257 			    }
258 			}
259 			
260 			
261 			boolean hasZipCodesProperty = ZipCodesAware.class.isAssignableFrom(requiredClass);
262 			Map<Long, Set<String>> idToZipCodesMap = null;
263 			if (hasZipCodesProperty && queryResults.size()>0){
264 			List<Long> ids = new ArrayList<Long>();
265 			for (Object[] tuple: queryResults){
266 			    ids.add((Long)tuple[idPropertyIndexInAliasList]);
267 			}
268 			String zipCodeQuery = "SELECT code as code,gisfeature as id FROM "+ZipCode.class.getSimpleName().toLowerCase() +" zip where zip.gisfeature in (:ids)" ;
269 			Query qry = session.createSQLQuery(zipCodeQuery).addScalar("code", Hibernate.STRING).addScalar("id", Hibernate.LONG);
270 			qry.setCacheable(true);
271 
272 			qry.setParameterList("ids", ids);
273 			List<Object[]> zipCodes = (List<Object[]>) qry.list();
274 			
275 			if (zipCodes.size() > 0) {
276 			    idToZipCodesMap = new HashMap<Long, Set<String>>();
277 			    for (Object[] zipCode : zipCodes){
278 				Long idFromZipcode = (Long) zipCode[1];
279 				Set<String> zipCodesFromMap  = idToZipCodesMap.get(idFromZipcode);
280 				if (zipCodesFromMap == null){
281 				    Set<String> zipCodesToAdd = new HashSet<String>();
282 				    idToZipCodesMap.put(idFromZipcode, zipCodesToAdd);
283 				    zipCodesFromMap = zipCodesToAdd;
284 				} 
285 				zipCodesFromMap.add((String)zipCode[0]);
286 			    }
287 			}
288 			}
289 			List<GisFeatureDistance> results = ResultTransformerUtil
290 			.transformToGisFeatureDistance(
291 					aliasList,
292 				queryResults,idToZipCodesMap,requiredClass);
293 			return results;
294 		    }
295 		});
296 
297     }
298 
299     /*
300      * (non-Javadoc)
301      * 
302      * @see com.gisgraphy.domain.repository.IGisDao#findByName(java.lang.String)
303      */
304     @SuppressWarnings("unchecked")
305     public List<T> listByName(final String name) {
306 	Assert.notNull(name);
307 	return (List<T>) this.getHibernateTemplate().execute(
308 		new HibernateCallback() {
309 
310 		    public Object doInHibernate(Session session)
311 			    throws PersistenceException {
312 			String queryString = "from "
313 				+ persistentClass.getSimpleName()
314 				+ " as c where c.name= ?";
315 
316 			Query qry = session.createQuery(queryString);
317 			qry.setCacheable(true);
318 
319 			qry.setParameter(0, name);
320 			List<T> results = (List<T>) qry.list();
321 			if (results == null) {
322 			    results = new ArrayList<T>();
323 			}
324 			return results;
325 		    }
326 		});
327     }
328 
329     /*
330      * (non-Javadoc)
331      * 
332      * @see com.gisgraphy.domain.repository.IGisDao#getByFeatureId(java.lang.Long)
333      */
334     @SuppressWarnings("unchecked")
335     public T getByFeatureId(final Long featureId) {
336 	Assert.notNull(featureId);
337 	return (T) this.getHibernateTemplate().execute(new HibernateCallback() {
338 
339 	    public Object doInHibernate(Session session)
340 		    throws PersistenceException {
341 		String queryString = "from " + persistentClass.getSimpleName()
342 			+ " as g where g.featureId= ?";
343 
344 		Query qry = session.createQuery(queryString);
345 		qry.setCacheable(true);
346 
347 		qry.setParameter(0, featureId);
348 		T result = (T) qry.uniqueResult();
349 
350 		return result;
351 	    }
352 	});
353     }
354 
355     /*
356      * (non-Javadoc)
357      * 
358      * @see com.gisgraphy.domain.repository.IGisDao#getDirty()
359      */
360     @SuppressWarnings("unchecked")
361     public List<T> getDirties() {
362 	return (List<T>) this.getHibernateTemplate().execute(
363 		new HibernateCallback() {
364 
365 		    public Object doInHibernate(final Session session)
366 			    throws PersistenceException {
367 			final String queryString = "from "
368 				+ persistentClass.getSimpleName()
369 				+ " as g where g.featureCode ='"
370 				+ ImporterConfig.DEFAULT_FEATURE_CODE
371 				+ "' or g.location=? or g.featureClass='"
372 				+ ImporterConfig.DEFAULT_FEATURE_CLASS + "'";
373 
374 			final Query qry = session.createQuery(queryString);
375 			qry.setParameter(0, GeolocHelper.createPoint(0F, 0F),
376 				GEOMETRY_TYPE);
377 			qry.setCacheable(true);
378 
379 			List<T> result = (List<T>) qry.list();
380 			if (result == null) {
381 			    result = new ArrayList<T>();
382 			}
383 
384 			return result;
385 		    }
386 		});
387     }
388 
389     /*
390      * (non-Javadoc)
391      * 
392      * @see com.gisgraphy.domain.repository.GenericDao#save(java.lang.Object)
393      */
394     @Override
395     public T save(T GisFeature) {
396 	T savedgisFeature = super.save(GisFeature);
397 	GisFeatureStoredEvent CreatedEvent = new GisFeatureStoredEvent(
398 		GisFeature);
399 	eventManager.handleEvent(CreatedEvent);
400 	return savedgisFeature;
401     }
402 
403     /*
404      * (non-Javadoc)
405      * 
406      * @see com.gisgraphy.domain.repository.GenericDao#remove(java.lang.Object)
407      */
408     @Override
409     public void remove(T gisFeature) {
410 	super.remove(gisFeature);
411 	GisFeatureDeletedEvent gisFeatureDeletedEvent = new GisFeatureDeletedEvent(
412 		gisFeature);
413 	eventManager.handleEvent(gisFeatureDeletedEvent);
414     }
415 
416     /*
417      * (non-Javadoc)
418      * 
419      * @see com.gisgraphy.domain.repository.IGisDao#getByFeatureIds(java.util.List)
420      */
421     @SuppressWarnings("unchecked")
422     public List<T> listByFeatureIds(final List<Long> ids) {
423 	if (ids == null || ids.size() == 0) {
424 	    return new ArrayList<T>();
425 	}
426 	return (List<T>) this.getHibernateTemplate().execute(
427 		new HibernateCallback() {
428 
429 		    public Object doInHibernate(final Session session)
430 			    throws PersistenceException {
431 			final String queryString = "from "
432 				+ persistentClass.getSimpleName()
433 				+ " as g where g.featureId in (:ids)";
434 
435 			final Query qry = session.createQuery(queryString);
436 			qry.setParameterList("ids", ids);
437 			qry.setCacheable(true);
438 
439 			List<T> result = (List<T>) qry.list();
440 			if (result == null) {
441 			    result = new ArrayList<T>();
442 			}
443 
444 			return result;
445 		    }
446 		});
447     }
448 
449     /*
450      * (non-Javadoc)
451      * 
452      * @see com.gisgraphy.domain.repository.IGisDao#getFromText(java.lang.String,
453      *      boolean)
454      */
455     public List<T> listFromText(String name, boolean includeAlternateNames) {
456 	return listFromText(name, includeAlternateNames, persistentClass);
457     }
458 
459     /**
460      * Do a full text search for the given name. The search will be case,
461      * iso-latin, comma-separated insensitive<br>
462      * search for 'saint-André', 'saint-Andre', 'SaInT-andré', 'st-andré', etc
463      * will return the same results. <u>note</u> : search for zipcode too
464      * Polymorphism is not supported, e.g : if clazz=GisFeature : the results
465      * will only be of that type and no other feature type (e.g : City that
466      * extends gisFeature...etc) will be returned. The results will be sort by
467      * relevance.
468      * 
469      * @param name
470      *                The name to search for
471      * @param includeAlternateNames
472      *                Wether we search in the alternatenames too
473      * @param clazz
474      *                specify the features we want to search for, if null : no
475      *                restriction is apply
476      * @return a list of gisFeatures of type of the class for the given text.
477      *         the max list size is {@link GenericGisDao#MAX_FULLTEXT_RESULTS};
478      * @see IGisFeatureDao#listAllFeaturesFromText(String, boolean)
479      */
480     protected List<T> listFromText(String name, boolean includeAlternateNames,
481 	    Class<T> clazz) {
482 	logger.debug("getFromText " + name);
483 	// Set up a simple query
484 	// Check for a null or empty string query
485 	if (name == null || name.length() == 0) {
486 	    return new ArrayList<T>();
487 	}
488 
489 	SolrQuery query = new SolrQuery();
490 	String namefield = FullTextFields.ALL_NAME.getValue();
491 	if (!includeAlternateNames) {
492 	    namefield = FullTextFields.NAME.getValue();
493 	}
494 	String queryString = "(" + namefield + ":\"" + name + "\" OR "
495 		+ FullTextFields.ZIPCODE.getValue() + ":\"" + name + "\")";
496 	if (clazz != null) {
497 	    queryString += " AND placetype:" + persistentClass.getSimpleName();
498 	}
499 	query.setQuery(queryString);
500 	query.setQueryType(Constants.SolrQueryType.advanced.toString());
501 	query.setFields(FullTextFields.FEATUREID.getValue());
502 	query.setRows(MAX_FULLTEXT_RESULTS);
503 
504 	QueryResponse results = null;
505 	try {
506 	    results = solrClient.getServer().query(query);
507 	} catch (SolrServerException e) {
508 	    throw new RuntimeException(e);
509 	}
510 
511 	List<Long> ids = new ArrayList<Long>();
512 	for (SolrDocument doc : results.getResults()) {
513 	    ids.add((Long) doc.getFieldValue(FullTextFields.FEATUREID
514 		    .getValue()));
515 	}
516 	// log
517 	List<T> gisFeatureList = this.listByFeatureIds(ids);
518 	if (logger.isDebugEnabled()) {
519 	    logger.debug("search on " + name + " returns "
520 		    + gisFeatureList.size());
521 	    for (GisFeature gisFeature : gisFeatureList) {
522 		logger.debug("search on " + name + " returns "
523 			+ gisFeature.getName());
524 	    }
525 	}
526 
527 	return gisFeatureList;
528     }
529 
530     /*
531      * (non-Javadoc)
532      * 
533      * @see com.gisgraphy.domain.repository.GenericDao#deleteAll(java.util.List)
534      */
535     @Override
536     public void deleteAll(List<T> list) {
537 	super.deleteAll(list);
538 	GisFeatureDeleteAllEvent gisFeatureDeleteAllEvent = new GisFeatureDeleteAllEvent(
539 		list);
540 	eventManager.handleEvent(gisFeatureDeleteAllEvent);
541     }
542 
543     /*
544      * (non-Javadoc)
545      * 
546      * @see com.gisgraphy.domain.repository.GenericDao#deleteAll()
547      */
548     @Override
549     public int deleteAll() {
550 	int numberOfGisDeleted = super.deleteAll();
551 	PlaceTypeDeleteAllEvent placeTypeDeleteAllEvent = new PlaceTypeDeleteAllEvent(
552 		this.getPersistenceClass());
553 	eventManager.handleEvent(placeTypeDeleteAllEvent);
554 	return numberOfGisDeleted;
555     }
556 
557     /*
558      * (non-Javadoc)
559      * 
560      * @see com.gisgraphy.domain.repository.IGisDao#getEager(java.lang.Long)
561      */
562     @SuppressWarnings("unchecked")
563     public T getEager(final Long id) {
564 	Assert.notNull(id, "Can not retrieve an Ogject with a null id");
565 	T returnValue = null;
566 	try {
567 	    return (T) this.getHibernateTemplate().execute(
568 		    new HibernateCallback() {
569 
570 			public Object doInHibernate(Session session)
571 				throws PersistenceException {
572 			    String queryString = "from "
573 				    + persistentClass.getSimpleName()
574 				    + " o where o.id=" + id;
575 
576 			    Query qry = session.createQuery(queryString);
577 			    qry.setCacheable(true);
578 			    GisFeature feature = (GisFeature) qry
579 				    .uniqueResult();
580 
581 			    feature.getAdm().getAdm1Code();
582 			    feature.getAlternateNames().size();
583 			    return feature;
584 
585 			}
586 		    });
587 	} catch (Exception e) {
588 	    logger.info("could not retrieve object of type "
589 		    + persistentClass.getSimpleName() + " with id " + id, e);
590 	}
591 	return returnValue;
592     }
593     
594     
595 
596     @Required
597     public void setEventManager(EventManager eventManager) {
598 	this.eventManager = eventManager;
599     }
600     
601     
602     /* (non-Javadoc)
603      * @see com.gisgraphy.domain.repository.IGisDao#createGISTIndexForLocationColumn()
604      */
605     public void createGISTIndexForLocationColumn() {
606 	 this.getHibernateTemplate().execute(
607 			 new HibernateCallback() {
608 
609 			    public Object doInHibernate(Session session)
610 				    throws PersistenceException {
611 				session.flush();
612 				
613 				logger.info("will create GIST index for  "+persistentClass.getSimpleName());
614 				String locationIndexName = "locationIndex"+persistentClass.getSimpleName();
615 				logger.info("checking if "+locationIndexName+" exists");
616 				String checkingLocationIndex= "SELECT 1 FROM   pg_class c  JOIN   pg_namespace n ON n.oid = c.relnamespace WHERE  c.relname = '"+locationIndexName+"'";
617 				Query checkingLocationIndexQuery = session.createSQLQuery(checkingLocationIndex);
618 				Object locationIndexExists = checkingLocationIndexQuery.uniqueResult();
619 				if (locationIndexExists != null){
620 					logger.info("will create GIST index for  the "+OpenStreetMap.SHAPE_COLUMN_NAME+" column");
621 					String createIndex = "CREATE INDEX "+locationIndexName+" ON "+persistentClass.getSimpleName().toLowerCase()+" USING GIST (location)";  
622 					Query createIndexQuery = session.createSQLQuery(createIndex);
623 					createIndexQuery.executeUpdate();
624 				} else {
625 					logger.info("won't create GIST index for "+persistentClass.getSimpleName()+" because it already exists");
626 				}
627 				
628 				return null;
629 			    }
630 			});
631     }
632     
633     @SuppressWarnings("unchecked")
634 	public T getNearest(final Point location,final String countryCode,final boolean filterMunicipality,final int distance) {
635 		Assert.notNull(location);
636 		return (T) this.getHibernateTemplate().execute(new HibernateCallback() {
637 
638 		    public Object doInHibernate(Session session)
639 			    throws PersistenceException {
640 		    String pointAsString = "ST_GeometryFromText('POINT("+location.getX()+" "+location.getY()+")',"+SRID.WGS84_SRID.getSRID()+")";
641 			String queryString = "from " + persistentClass.getSimpleName()
642 				+ " as c  where st_distance_sphere(c.location,"+pointAsString+") < "+distance;//left outer join c.zipCodes z
643 			if (filterMunicipality){
644 				queryString+=" and c.municipality=true";
645 			}
646 			if (countryCode!=null ){
647 				queryString+=" and c.countryCode='"+countryCode+"'";
648 			}
649 			queryString = queryString+ " order by st_distance_sphere(c.location,"+pointAsString+")";
650 
651 			Query qry = session.createQuery(queryString).setMaxResults(1);
652 
653 			//qry.setParameter("point2", location);
654 			City result = (City) qry.uniqueResult();
655 
656 			return result;
657 		    }
658 		});
659 	}
660 
661     public void createGISTIndexForShapeColumn() {
662 		 this.getHibernateTemplate().execute(
663 				 new HibernateCallback() {
664 
665 				    public Object doInHibernate(Session session)
666 					    throws PersistenceException {
667 					session.flush();
668 					
669 					logger.info("will create GIST index for "+persistentClass.getSimpleName().toLowerCase()+" shape column");
670 					String shapeIndexName = "shapeIndex"+persistentClass.getSimpleName();
671 					logger.info("checking if "+shapeIndexName+" exists");
672 					String checkingShapeIndex= "SELECT 1 FROM   pg_class c  JOIN   pg_namespace n ON n.oid = c.relnamespace WHERE  c.relname = '"+shapeIndexName+"'";
673 					Query checkingShapeIndexQuery = session.createSQLQuery(checkingShapeIndex);
674 					Object shapeIndexExists = checkingShapeIndexQuery.uniqueResult();
675 					if (shapeIndexExists != null){
676 						logger.info("will create GIST index for  the "+OpenStreetMap.SHAPE_COLUMN_NAME+" column");
677 						String createIndex = "CREATE INDEX "+shapeIndexName+" ON "+persistentClass.getSimpleName().toLowerCase()+" USING GIST ("+GisFeature.SHAPE_COLUMN_NAME+")";  
678 						Query createIndexQuery = session.createSQLQuery(createIndex);
679 						createIndexQuery.executeUpdate();
680 					} else {
681 						logger.info("won't create GIST index for "+persistentClass.getSimpleName()+" because it already exists");
682 					}
683 					
684 					return null;
685 				    }
686 		
687 	});
688 	}
689 
690 	public String getShapeAsWKTByFeatureId(final Long featureId) {
691 		if (featureId ==null){
692 			return null;
693 		}
694 		return (String) this.getHibernateTemplate().execute(
695 				new HibernateCallback() {
696 
697 				    public Object doInHibernate(Session session)
698 					    throws PersistenceException {
699 					String queryString = "select ST_AsText("+GisFeature.SHAPE_COLUMN_NAME+") from " + persistentClass.getSimpleName()
700 			+ " as g where g.featureId= ?";
701 
702 					Query qry = session.createQuery(queryString);
703 					qry.setParameter(0, featureId);
704 					qry.setCacheable(true);
705 					return (String) qry.uniqueResult();
706 				    }
707 				});
708 	}
709     
710 
711     
712 }