Ankündigung

Einklappen
Keine Ankündigung bisher.

von SQL erstelltes "SELECT Value" in Joins nutzen

Einklappen

Neue Werbung 2019

Einklappen
X
  • Filter
  • Zeit
  • Anzeigen
Alles löschen
neue Beiträge

  • von SQL erstelltes "SELECT Value" in Joins nutzen

    Hi, ich habe 2 Tabellen
    Tabelle 1: cities id_city lat lng
    Tabelle 2: user id_user id_city radius
    Ich nutze derzeit den SQL Befehl von Google, um den Radius (bzw. Distance) zu berechnen, der auch funktioniert.

    PHP-Code:
    $sql "  SELECT id, ( 3959 * acos( cos( radians(37) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(-122) ) + sin( radians(37) ) * sin( radians( lat ) ) ) ) AS distance  FROM cities HAVING distance < 25  ORDER BY distance LIMIT 0 , 20; "
    Nun möchte ich "distance" gerne in einen Join nutzen, um diesen mit den User-Radius vergleichen zu können. Wie stelle ich das am besten an? "distance" einfach in Joins reinsetzen funktioniert ja nicht, muss ich da komplett anders vorgehen?

    Ich sage schonmal Danke für jegliche Hilfe und Tipps

  • #2
    Du musst die Formel statt den Alias verwenden.

    Kommentar


    • #3
      Danke, funktioniert

      Kommentar


      • #4
        Was mir grade noch auffällt. Wenn du mit dem Query joinst, nimm die HAVING Klausel weg und mach das im WHERE. Die Datenbank macht ansonsten u.U. sehr viele unnötige joins. Bei Mysql musst du im WHERE aber auch die Formel verwenden.

        Es bietet sich dann sowas an:
        PHP-Code:
        $distance " ( 3959 * acos( cos( radians(37) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(-122) ) + sin( radians(37) ) * sin( radians( lat ) ) ) ) ";
        $sql "  SELECT id, $distance AS distance  FROM cities WHERE $distance < 25  ORDER BY distance LIMIT 0 , 20; "

        Kommentar


        • #5
          Btw.: würde der Fragesteller PostgreSQL/PostGIS verwenden, könnte er eine indexbasierte Umkreissuche nutzen. So wie die Abfrage da ist und mit den begrenzten Mitteln von MySQL dürfte das bei schon mittleren Datenmengen schnell nicht-schnell werden...
          PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

          Kommentar


          • #6
            Du irrst.

            Kommentar


            • #7
              Zitat von protestix Beitrag anzeigen
              Du irrst.
              Danke für die Richtigstellung und ausführliche Erklärung.
              PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

              Kommentar


              • #8
                Zitat von akretschmer Beitrag anzeigen

                Danke für die Richtigstellung und ausführliche Erklärung.
                Das Postgres bei GIS die bessere Wahl ist will ich nicht anzweifeln aber so langsam wie du es darstellst ist Mysql nun auch wieder nicht, zwar ist Postgres schneller aber mit Mysql kann man durchaus leben, wenn man keine andere Wahl hat. Der Geschwindigskeitsunterschied liegt so im Bereich von 5-10% zugunsten von Postgres.
                https://programm.froscon.de/2016/sys...S_Features.pdf

                Kommentar


                • #9
                  Zitat von protestix Beitrag anzeigen

                  Das Postgres bei GIS die bessere Wahl ist will ich nicht anzweifeln aber so langsam wie du es darstellst ist Mysql nun auch wieder nicht, zwar ist Postgres schneller aber mit Mysql kann man durchaus leben, wenn man keine andere Wahl hat. Der Geschwindigskeitsunterschied liegt so im Bereich von 5-10% zugunsten von Postgres.
                  https://programm.froscon.de/2016/sys...S_Features.pdf

                  Wir schauen mal. Wir bleiben bei reinem Postgresql und bauen eine Tabelle mit Punkten. Zuerst einmal eine kleine mit nur 10 Datensätzen:

                  Code:
                  test=# create table points (id serial primary key, p point);
                  CREATE TABLE
                  test=*# insert into points (p) select point(random()*1000, random()*1000) from generate_series(1, 10) s;
                  INSERT 0 10
                  test=*# select * from points;
                   id |                  p                  
                  ----+-------------------------------------
                    1 | (500.200330745429,887.458802666515)
                    2 | (821.308790706098,653.028879314661)
                    3 | (285.913105588406,369.427011813968)
                    4 | (93.744495883584,137.371032964438)
                    5 | (730.39063392207,706.43318304792)
                    6 | (549.570751842111,34.675260540098)
                    7 | (470.727629493922,716.635721269995)
                    8 | (474.171690642834,448.039500042796)
                    9 | (701.421444769949,794.212560169399)
                   10 | (151.605553459376,54.211110342294)
                  (10 Zeilen)
                  
                  test=*# select *, p <-> point(500,500) from points order by p <-> point(500,500) ;
                   id |                  p                  |     ?column?     
                  ----+-------------------------------------+------------------
                    8 | (474.171690642834,448.039500042796) | 58.0258142558299
                    7 | (470.727629493922,716.635721269995) | 218.604454220897
                    3 | (285.913105588406,369.427011813968) | 250.763840301249
                    5 | (730.39063392207,706.43318304792)   | 309.345281622833
                    2 | (821.308790706098,653.028879314661) | 355.889276165096
                    9 | (701.421444769949,794.212560169399) | 356.555225700936
                    1 | (500.200330745429,887.458802666515) | 387.458854455769
                    6 | (549.570751842111,34.675260540098)  | 467.957661110082
                    4 | (93.744495883584,137.371032964438)  |  544.55789624076
                   10 | (151.605553459376,54.211110342294)  | 565.779484006444
                  (10 Zeilen)
                  Der Abstands-Operator <-> ermittelt den Abstand zweier Punkte, hier einem Fixpunkt (500,500) und dem, was in der Tabelle gespeichert ist. Das sieht schon mal nett aus denke, weil dieser Operator ist recht einfach nutzbar. Aber da geht mehr.

                  Wir vergrößern die Tabelle:

                  Code:
                  test=*# insert into points (p) select point(random()*1000, random()*1000) from generate_series(1, 100000) s;
                  INSERT 0 100000
                  und fragen die 5 nächsten Punkte ab. Ich mache das mit EXPLAIN ANALYSE, um den Plan und damit das Problem zu zeigen:

                  Code:
                  test=*# explain analyse select *, p <-> point(500,500) from points order by p <-> point(500,500) limit 5;
                                                                          QUERY PLAN                                                         
                  ---------------------------------------------------------------------------------------------------------------------------
                   Limit  (cost=3795.23..3795.24 rows=5 width=20) (actual time=65.756..65.758 rows=5 loops=1)
                     ->  Sort  (cost=3795.23..4066.38 rows=108460 width=20) (actual time=65.755..65.756 rows=5 loops=1)
                           Sort Key: ((p <-> '(500,500)'::point))
                           Sort Method: top-N heapsort  Memory: 25kB
                           ->  Seq Scan on points  (cost=0.00..1993.75 rows=108460 width=20) (actual time=0.075..44.128 rows=100010 loops=1)
                   Planning time: 0.109 ms
                   Execution time: 65.792 ms
                  (7 Zeilen)
                  PostgreSQL (und das wäre in MySQL nicht anders) muß nun mit "rows=100010" mal diese Berechnung ausführen und diese dann noch sortieren. Das dauert ca. 65Millisekunden. MySQL wird das ganz sicher in einer vergleichbaren Zeit auch schaffen.

                  Aber da geht noch was: wir erstellen einen Index:

                  Code:
                  test=*# create index ind_points on points using gist (p);
                  CREATE INDEX
                  und führen die Abfrage erneut aus:

                  Code:
                  test=*# explain analyse select *, p <-> point(500,500) from points order by p <-> point(500,500) limit 5;
                                                                              QUERY PLAN                                                            
                  ----------------------------------------------------------------------------------------------------------------------------------
                   Limit  (cost=0.28..0.69 rows=5 width=20) (actual time=0.117..0.182 rows=5 loops=1)
                     ->  Index Scan using ind_points on points  (cost=0.28..8220.48 rows=100010 width=20) (actual time=0.115..0.176 rows=5 loops=1)
                           Order By: (p <-> '(500,500)'::point)
                   Planning time: 0.185 ms
                   Execution time: 0.236 ms
                  (5 Zeilen)
                  Aus 65 Millisekunden werden nun 0,24 Millisekunden. Das ist eine Verbesserung um doch etwas mehr als 5-10%... und wir haben 'nur' ca. 100000 Datensätze.
                  PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

                  Kommentar

                  Lädt...
                  X