Information ausblenden
Willkommen im Forum für alle Datenbanken! Registriere Dich kostenlos und diskutiere über DBs wie Mysql, MariaDB, Oracle, Sql-Server, Postgres, Access uvm

JOIN Where Bedingung für Ergebnis von SubQuery

Dieses Thema im Forum "MySQL und MariaDB" wurde erstellt von denyo, 11 Mai 2015.

  1. denyo

    denyo Benutzer

    Angenommen ich möchte alle Inserate anzeigen lassen, die im Umkreis sind.

    2 Tabellen:
    - inserate (mit Verknüpfung zum Ort per geo_id)
    - coordinates

    Mein Ansatz:
    Code:
    SELECT test.id, test.name, test.geo_id
    FROM inserat test
        JOIN coordinates test2
        ON test.geo_id = test2.id
    WHERE test2.id IN(
                                        SELECT
                                       dest.zip,
                                       dest.location_name,
                                       ACOS(
                                       SIN(RADIANS(src.lat)) * SIN(RADIANS(dest.lat))
                                       + COS(RADIANS(src.lat)) * COS(RADIANS(dest.lat))
                                       * COS(RADIANS(src.lon) - RADIANS(dest.lon))
                                       ) * 6380 AS distance
                                       FROM coordinates dest
                                       CROSS JOIN coordinates src
                                       WHERE src.id = 9024
                                       HAVING distance < 10
                                       ORDER BY distance
    );
    IN ist da der falsche Ansatz, wie gehe ich da ran, es muss ja nicht nur eine id verglichen werden, sondern z.B. alle Inserate angezeigt werden von 10 Orten, also 10 id's
     
    Zuletzt von einem Moderator bearbeitet: 11 Mai 2015
  2. BerndB

    BerndB Datenbank-Guru

    Hi,

    kannst Du deine Tabellen mal posten.

    Gruss

    Bernd
     
  3. denyo

    denyo Benutzer

    zip_coordinates
    Column Type
    id (Primary) int(11)
    loc_id int(11)
    zip varchar(10)
    location_name varchar(255)
    lat double
    lon double
    Indexes
    Keyname Type Unique Packed Column Cardinality Collation Null
    PRIMARY BTREE Yes No id 17307 A No

    inserat
    Column Type
    id (Primary) int(11)
    geo_id int(11)
    name varchar(50)

    Indexes
    Keyname Type Unique Packed Column Cardinality Collation Null
    PRIMARY BTREE Yes No id 8342 A No
     
    Zuletzt von einem Moderator bearbeitet: 11 Mai 2015
  4. BerndB

    BerndB Datenbank-Guru

    lustig. schick mal ein dump mit ein paar Daten, damit ich auch probieren kann.

    Gruss

    Bernd
     
  5. BerndB

    BerndB Datenbank-Guru

    So, oder ähnlich

    Code:
    SELECT
      test.id, test.name, test.geo_id
    FROM inserat test
    LEFT JOIN coordinates src ON  src.id = 9024
    LEFT JOIN (
      SELECT
        zip , location_name ,
        SIN(RADIANS(src.lat)) * SIN(RADIANS(dest.lat))
      + COS(RADIANS(src.lat)) * COS(RADIANS(dest.lat))
      * COS(RADIANS(src.lon) - RADIANS(dest.lon))
      ) * 6380 AS distance
      FROM coordinates
      )  dest ON dest.distance < 10
    
      WHERE  dest.id NOT NULL;
    
    Gruss

    Bernd
     
  6. denyo

    denyo Benutzer

  7. BerndB

    BerndB Datenbank-Guru

    Danke,

    meld mich später noch. Muss kurz zur Firma.

    Gruss

    Bernd
     
  8. denyo

    denyo Benutzer

    Also, nochmal etwas anderes ausgedrückt.
    Ich gebe Standort Aachen an (id 4)
    es sollen alle id's von der tabelle coordinates gesucht werden im Umkreis von 10km
    Ergebnis z.B 20 id's
    anhand dieser id's soll eine liste ausgegeben von der Tabelle Inserat wo Inserat.geo_id = id's von der zuvorigen Suche ist.
     
  9. BerndB

    BerndB Datenbank-Guru

    Super danke

    Bis später

    Bernd
     
  10. denyo

    denyo Benutzer

    Ich bin mit Optimierungen nicht sehr bewandt, wäre es schneller und leichter, in jedem Inserat die zugehörigen Koordinaten abzuspeichern und so die umkreisabfrage zu starten.
    Coordinaten bzw plz habe ich ungefähr 17.000 (crossjoin 17.000^2 Einträge) , Inserate circa 5.000 - 10.000

    Code:
    SELECT
    dest.zip,
    dest.name,
    ACOS(
    SIN(RADIANS(src.lat)) * SIN(RADIANS(dest.lat))
    + COS(RADIANS(src.lat)) * COS(RADIANS(dest.lat))
    * COS(RADIANS(src.lon) - RADIANS(dest.lon))
    ) * 6380 AS distance
    FROM inserat dest
    CROSS JOIN inserat src
    WHERE src.geo_id = 6
    HAVING distance < 10
    ORDER BY distance
    );
    Angenommen die neue Tabelle Inserat schaut so aus:
    id, geo_id, name, lat, lon usw.

    natürlich muss zuvor bei der Eingabe von z.B. Aachen erstmal aus Coordinates die ID gefunden werden.
     
    Zuletzt von einem Moderator bearbeitet: 11 Mai 2015
  11. akretschmer

    akretschmer Datenbank-Guru

    Es wäre zumindest um einige Größenordnungen schneller, wenn die Umkreissuche indexbasiert wäre. Stickwort KNN, Stichwort PostgreSQL / PostGIS, Link als Beispiel :
    http://www.hagander.net/talks/Find your neighbours.pdf
     
  12. BerndB

    BerndB Datenbank-Guru

    Bin wieder da. Kannst mal anrufen wenn du möchtest.

    02163 / 5719653

    Gruss

    Bernd
     
  13. BerndB

    BerndB Datenbank-Guru

    Hi,

    schau dir das mal an. Musst die Felder nur noch was bearbeiten

    Code:
    SELECT ins.*,
          src.zip , src.location_name ,    
          ACOS(
            SIN(RADIANS(src.lat)) * SIN(RADIANS(dest.lat))
            + COS(RADIANS(src.lat)) * COS(RADIANS(dest.lat))
            * COS(RADIANS(src.lon) - RADIANS(dest.lon))
          ) * 6380 AS distance
      FROM coordinates dest
      LEFT JOIN (SELECT  * FROM coordinates WHERE id = 13 LIMIT 1) AS src ON src.id > 0
      LEFT JOIN inserat ins ON ins.geo_id = dest.id
      WHERE ins.geo_id  IS NOT null
      HAVING distance < 10
    ORDER BY distance ASC
    Index setzen und dann sollte es das sein

    Gruss

    Bernd
     
    Walter gefällt das.
Die Seite wird geladen...

Diese Seite empfehlen

  1. Diese Seite verwendet Cookies. Wenn du dich weiterhin auf dieser Seite aufhältst, akzeptierst du unseren Einsatz von Cookies.
    Information ausblenden