Hi guys,
I'm trying do do a simple spatial query using PostGIS 1.1.2, Google Maps
and EJB3... but it gets pretty frustrating :) Probably because I'm new
to PostGIS.
I would like to get all the points of interest that are in a
rectangular area (the visible map area for the user).
My POI are stored using a custom projection with the SRID 101 in my
database.
The map bounds are in latitude and longitude (I have 2 points :
southWest and sorthEast).
First, I red postgis documentation (ยง3.3) and tried to perform a native
query using a postgresql client :
select poi_gid, astext(geometry) as geom
from poi AS p
where transform(
GeomFromText(
'POLYGON((
48.88190472849405 2.3015928268432617,
48.88190472849405 2.3398303985595703,
48.86417836241082 2.3015928268432617,
48.86417836241082 2.3398303985595703,
48.88190472849405 2.3015928268432617))',
4326)
, 101) && p.geometry
and Contains(p.geometry,
transform(
GeomFromText(
'POLYGON((
48.88190472849405 2.3015928268432617,
48.88190472849405 2.3398303985595703,
48.86417836241082 2.3015928268432617,
48.86417836241082 2.3398303985595703,
48.88190472849405 2.3015928268432617))',
4326)
, 101)
);
I got no error but no result neither.
Then I tried the EJB3 way (but I don't really know how to use the
spatial.XXX methods - is there any documentation about it?):
public String localSearch(String what, double northEastLat,
double northEastLng, double southWestLat, double southWestLng) {
// Get the projected coordinates - TODO: Use a nicer way,
probably with the org.postgis.Point class
String neStr = this.getProjectedPoint(northEastLat,
northEastLng, 101);
neStr = neStr.replace("POINT(", "").replace(")", "");
String swStr = this.getProjectedPoint(southWestLat,
southWestLng, 101);
swStr = swStr.replace("POINT(", "").replace(")", "");
double neX = Double.parseDouble(neStr.split(" ")[0]);
double neY = Double.parseDouble(neStr.split(" ")[1]);
double swX = Double.parseDouble(swStr.split(" ")[0]);
double swY = Double.parseDouble(swStr.split(" ")[1]);
System.out.println(swStr);
System.out.println(neX);
System.out.println(neY);
// Query POI in the bounded area
Query query = em.createQuery("SELECT p FROM POI p "
+ "WHERE spatial.X(p.location) >=:neX "
+ "AND spatial.X(p.location) <=:swX "
+ "AND spatial.Y(p.location) >=:swY "
+ "AND spatial.Y(p.location) <=:neY ");
query.setParameter("neX", neX);
query.setParameter("swX", swX);
query.setParameter("swY", swY);
query.setParameter("neY", neY);
List<POI> poiResults = query.setMaxResults(20).getResultList();
System.out.println("> " + poiResults.size());
System.out.println(northEastLat + " " + southWestLng);
System.out.println(northEastLat + " " + northEastLng);
System.out.println(southWestLat + " " + southWestLng);
System.out.println(southWestLat + " " + northEastLng);
return "Hello";
}
private String getProjectedPoint(double lat, double lng, int
destProjId) {
int srcProjId = 4326;
String query = "select AsText(transform(GeomFromText('POINT("
+ lat + " " + lng + ")'," + srcProjId + ")," + destProjId
+ "))";
Query q = em.createNativeQuery(query);
return (String) q.getSingleResult();
}
I got no error but no result neither.
I know that this code could be significantly improved with more
knowledge on the technologies (postgis and ejb3 spatial).
Could you give me some tips and guidelines to help me out ?
Best Regards
Romain
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
