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

[easyCity Team]

Thank you Paul :)

I think I miss entered the coordinates in the POLYGON WKT.

I tried with :

select     poi_name, astext(transform(geometry, 4326)) as geometry
from     poi AS p
where
               GeomFromText(
                   'POLYGON((
                       90 -180,
                       90 180,
                       -90 180,
                       -90 -180,
                       90 -180))',
                   4326)
           && transform(geometry, 4326)
          and Contains(
                 GeomFromText(
                   'POLYGON((
                       90 -180,
                       90 180,
                       -90 180,
                       -90 -180,
                       90 -180))',
                   4326), transform(geometry, 4326)          );

... and it did the job :)

Thanks again for your help.

Do you know what is the nicest way to do it with EJB3 and where I can 
find documentation ?

I know only this place at the moment :
* http://svn.refractions.net/postgis/trunk/java/ejb3
* 
http://lists.refractions.net/pipermail/postgis-users/attachments/20070131/c4ea64d4/hql.bin

Greetings

Romain

Paul Ramsey a écrit :
> You've got Contains(point, polygon) in this, which will always return 
> nothing... flip your contains arguments around.
>
> P
>
> easyCity Team wrote:
>> 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
>> postgis-users at postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>