[postgis-users] [Spatial Query] Experiencing difficulties with Postgis, Google Maps and EJB3.

[easyCity Team]

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