MySQL Umkreissuche - brauche Hilfe bei SQL Statement

[beta20]

Hallo zusammen,
ich möchte eine Umkreissuche abbilden, brauche aber Hilfe bei einem HQL Statement:
Ich nutze JPA / Hibernate und habe einen Syntaxfehler:

SELECT count(m) FROM Mandatory m
WHERE  m.mandatoryAddressMain EXISTS
    ( SELECT * FROM
            ( SELECT *, ( 6371 * ACOS( COS( RADIANS( 0.0 ) ) * COS( RADIANS( ma.latitude ) ) * COS( RADIANS( ma.longitude ) - RADIANS( 0.0) ) + SIN( RADIANS( 0.0 ) ) * SIN( RADIANS( ma.latitude ) ) ) ) AS distance
              FROM MandatoryAddress ma) c
              WHERE distance < 200 AND (ma.latitude <> 0.0 AND ma.longitude <> 0.0)
              ORDER BY distance)
ORDER BY m.id DESC

Derzeit habe ich ein Error bei:
unexpected token: ,: line 1:126: unexpected token: ,
-> Also sprich vor "6371"....

Kann mir bitte jemand helfen...
Ich habe das Statement mit bereits Werten ausgeführt... Also sprich hier:
distance < 200

m.mandatoryAddressMain = 1:1 Beziehung.
Also sprich die Hauptadresse des Mandanten

Hat jemand Ideen?

 

[mihe7]

WHERE m.mandatoryAddressMain EXISTS gibt keinen Sinn, das müsste einfach nur WHERE EXISTS heißen.

Nachtrag: Du verknüpft auch die Tabellen nicht, das ORDER BY dürfte auch sinnfrei sein. Was soll die Abfrage eigentlich liefern?

 

[Thallius]

Mal davon abgesehen, dass die Performance dieser Abfrage jenseits von gut und böse sein wird...

 

[beta20]

Die Query soll mir die Mandanten liefern, die eine gewisse Distanz weit wegwohnen... (Mit dem COUNT dann eben die Anzahl)

Hier mal die WHERE - Clausel:
-> Wobei ich das so aufgebaut habe, dass es so aussieht:
WHERE
<hier können noch andere Bedingungen sein>
AND

                where.append(" EXISTS (SELECT * FROM ( SELECT *, ( 6371 * ACOS( COS( RADIANS( "
                        + loginMandatory.getMandatoryAddressMain().getLatitude()
                        + " ) ) * COS( RADIANS( ma.latitude ) ) * COS( RADIANS( ma.longitude ) - RADIANS( "
                        + loginMandatory.getMandatoryAddressMain().getLongitude() + ") ) + SIN( RADIANS( "
                        + loginMandatory.getMandatoryAddressMain().getLatitude()
                        + " ) ) * SIN( RADIANS( ma.latitude ) ) ) ) AS distance FROM MandatoryAddress ma) c WHERE distance < "
                        + o.getSearchValue() + " AND (ma.latitude <> "
                        + loginMandatory.getMandatoryAddressMain().getLatitude() + " AND ma.longitude <> "
                        + loginMandatory.getMandatoryAddressMain().getLongitude() + ") ORDER BY distance)");

Ich habe es nun abgeändert (wie oben beschrieben).
Bekomme nun aber ebenfalls einen Syntaxfehler:
unexpected token: line 1:105:

 

[mihe7]

1. An welcher Stelle im SQL hakt es? (mit der Angabe line 1:105 kann man hier wenig anfangen)
2. Wenn man Abfrageparameter hat, baut man diese nicht manuell in einen SQL-String ein sondern nimmt ein PreparedStatement.

 

[Thallius]

Ist es wirklich nötig die distanz so genau zu berechnen? Diese cos/sin Berechnung ist doch eigentlich nur nötig wenn die erdkrümmung eine entscheidende Rolle spielt und das wird es erst bei mehreren hundert Kilometern. Ich würde hier einen einfachen pythagoras nehmen. Damit bist du schonmal mehr als dreimal so schnell unterwegs

Dann verstehe ich die ganze Abfrage nicht. Warum nicht einfach ein

WHERE sqr((lat2 - lat1) ^ 2 + (long2 - long1) ^ 2) < maxdistance.

Gruß

Claus

 

[beta20]

Wenn dies ebenfalls so funktioniert, dann ist das auch so ok für mich?
Wie sollte die Query dann aussehen?

Was ist dann bei dir lat2 und lat1?
Was ist long2 und was ist long1 ?

 

[mihe7]

In etwa so:

String query = "SELECT * FROM MandatoryAddress ma WHERE sqrt(pow(ma.latitude-?,2) + pow(ma.longitude-?,2)) < ?";
try(PreparedStatement stmt = conn.prepareStatement(query)) {
     stmt.setDouble(1, loginMandatory.getMandatoryAddressMain().getLatitude());
     stmt.setDouble(2, loginMandatory.getMandatoryAddressMain().getLongitude());
     stmt.setDouble(3, distance);
     try(ResultSet rs = stmt.executeQuery()) {
         while (rs.next()) {
             ....
         }
     }
}

 

[beta20]

@mihe7
Danke, aber ich will doch die Mandatory (Mandanten) ausgeben und nicht die MandatoryAddress ?

 

[mihe7]

Dann joinst Du die halt einfach dazu.

 

[stg]

String query = "SELECT * FROM MandatoryAddress ma WHERE sqrt(pow(ma.latitude-?,2) + pow(ma.longitude-?,2)) < ?";

Sofern der Ansatz in Frage kommt, solltest du dann auch direkt die Abstandsquadrate vergleichen, anstatt der eigentlichen Abstände. dann sparst du dir die teure Wurzelberechnung.

 

[beta20]

String query = "SELECT * FROM MandatoryAddress ma WHERE sqrt(pow(ma.latitude-?,2) + pow(ma.longitude-?,2)) < ?";

Sofern der Ansatz in Frage kommt, solltest du dann auch direkt die Abstandsquadrate vergleichen, anstatt der eigentlichen Abstände. dann sparst du dir die teure Wurzelberechnung.

Ok, danke. Aber eine Frage habe ich noch was das SQL angeht. Ich verstehe nicht so ganz was , 2 ist?
String query = "SELECT * FROM MandatoryAddress ma WHERE sqrt(pow(ma.latitude-?,2) + pow(ma.longitude-?,2)) < ?

Sehe ich das richtig, dass das so ist:

• ? = Parameter für die Distanz
• sqrt(pow(ma.latitude-?,2) -> 2 ist dann die latidute des eingeloggten Mandanten?
• pow(ma.longitude-?,2)). -> 2 ist dann die longitude des eingeloggten Mandanten?

Danke für jede Hilfe

 

[stg]

pow(a,b) meint "a hoch b"
In deinem Fall also: pow(ma.longitude-?,2)) = (ma.longitude - ?) zum Quadrat