JOIN Where Bedingung für Ergebnis von SubQuery

[denyo]

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:

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

 

[BerndB]

Hi,

kannst Du deine Tabellen mal posten.

Gruss

Bernd

 

[denyo]

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

 

[BerndB]

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

Gruss

Bernd

 

[BerndB]

So, oder ähnlich

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

 

[denyo]

http://www.xup.in/dl,31811528/inserat.sql/
http://www.xup.in/dl,60065747/coordinates.sql/

 

[BerndB]

Danke,

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

Gruss

Bernd

 

[denyo]

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.

 

[BerndB]

Super danke

Bis später

Bernd

 

[denyo]

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

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.

 

[akretschmer]

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.

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

 

[BerndB]

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

02163 / 5719653

Gruss

Bernd

 

[BerndB]

Hi,

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

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