grails executeQuery with Acos/having/cross-Join

Question

I want to execute a query for zip-search. I've got a domain-model like this:

class Zip_data {
int zc_loc_id 
String zc_zip 
String zc_location_name 
double zc_lat 
double zc_lon

static constraints = {
    zc_loc_id()
    zc_zip()
    zc_location_name()
    zc_lat()
    zc_lon()
}
}

And now I try to execute the following query via the method "Zip_data.executeQuery(query)":

SELECT 
        dest.zc_zip, 
        dest.zc_location_name,
        ACOS(
             SIN(RADIANS(src.zc_lat)) * SIN(RADIANS(dest.zc_lat)) 
             + COS(RADIANS(src.zc_lat)) * COS(RADIANS(dest.zc_lat))
             * COS(RADIANS(src.zc_lon) - RADIANS(dest.zc_lon))
        ) * 6380 AS distance
    FROM zip_data dest
    CROSS JOIN zip_data src
    WHERE src.zc_zip = 20537
    AND dest.zc_loc_id <> src.zc_loc_id
    HAVING distance < 10
    ORDER BY distance

But all the time i get some errors because of the "CROSS JOIN" or "HAVING" is not allowed, or "zip_data is not mapped" or anything like this...

I thought it is possible to execute a random query and get back all the coloums of the selectof the query... Is there a default-domain-model where i can do such things?

Can anybody help me how to execute this query?

Thanks for help!

Greetz

V

Edit: I want to use the data of "http://opengeodb.org/wiki/OpenGeoDB". Is it not possible to execute a random query to the database on a default-object or any kind of that and get an array of hashes back? Or is it possible to create a sub-select to map the same table twice? I tried to create this query in a more-simpler way:

SELECT 
dest.zc_zip, 
dest.zc_location_name,
(Select (ACOS(
     SIN(RADIANS(src.zc_lat)) * SIN(RADIANS(dest.zc_lat)) 
     + COS(RADIANS(src.zc_lat)) * COS(RADIANS(dest.zc_lat))
     * COS(RADIANS(src.zc_lon) - RADIANS(dest.zc_lon))
) * 6380) FROM zip_data as src where src.zc_loc_id<>dest.zc_loc_id and src.zc_loc_id = 20537)
AS distance
FROM zip_data dest

I know that's not the same SQL but i also get the error: zip_data is not mapped. Is there a way to map it und use it like this?

I need this query for a zip-search. I want to get all zips with a distance of 10 kilometers around the zip '20537'. I have a database with all these information where i usualy can make this search with this query. I know how i can avoid the use of the "CROSS JOIN" with a normal "INNER JOIN" but than i'm not able to join the table zip_data again... And it seemed to be that i am not able to use having or how could i use it? — Der_V, Sep 01 '13 at 20:02

score 1 · Answer 1 · answered Sep 05 '13 at 05:26

Ok now i found a solution. I don't know why, but it seemed to be not possible in one sql because of the join... I'm not sure if there are another mistake in the query (perhaps the case of the tablename or anything else), but i get it with two request: First I get the data of the source zip (the place i want to start the radius-search)

def sourcePlz = Zip_data.findByZc_zip('20537')

After that I can make a search with an example radius of 10 kilometeres:

def query = """FROM Zip_data as dest WHERE dest.zc_lat IS NOT NULL AND dest.zc_lon IS
                    NOT NULL
                    AND 6371.0*ACOS(
                    COS(RADIANS(dest.zc_lat))*COS(RADIANS(:qlat))*COS(RADIANS(dest.zc_lon)-RADIANS(:qlon))
                    + SIN(RADIANS(dest.zc_lat))*SIN(RADIANS(:qlat)) ) < :qradius 
                    """
        plz_objs = Zip_data.executeQuery(query, [qlat:sourcePlz.zc_lat, qlon:sourcePlz.zc_lon, qradius:umkreis.toDouble()])

And it's all :) I get all zips of the sourceZip in a radius of 10 kilometers. I hope that helps everybody who tried the same.

Greetz

V

grails executeQuery with Acos/having/cross-Join

V

1 Answers1