Problem z zapytaniem - liczenie odległości w promieniu od punktu - Harvestine formula, Jak wyciągnąć z bazy odpowiednie wartości do wzoru Opcje

[Kulfon]

Cześć! Męczę się i męczę i chyba nie dojdę do tego jak powinno to być...

Znalazłem/wymyśliłem kilka innych rozwiązań ale traktuję je póki co jako drogę na około, a nie prawdiłową drogę działania dlatego chciałbym zapytać jeszcze tutaj - moze uda się to zrobić jak należy, a nie na około.

Chcę liczyć odległość od punktu. Na to mamy odpowiedni wzór. Zapytanie do bazy też nie powinno przysparzać problemów ale tutaj sprawa się komplikuje. Otóż postawione na Wordpressie. I moje pytanie odnosi się tylko do zapytania. Tabela wygląda tak:
id post_id meta_key meta_value
640 11 lat 50.0952545
641 11 lng 20.078393600000027
642 11 address Kwiatowa 12
(tabela wp_postmeta)

Czyli ogólnie to co potrzebuję to:
- wyciągnąć z bazy wartości lat i lng
- policzyć dystans
- tam gdzie dystans jest mniejszy niż podany do zapytania zwrócić np. pole address

Na moment obecny mam takie coś:

[SQL] pobierz, plaintext 
$wpdb->get_results(sprintf("SELECT meta_value, post_id, ( 3959 * acos( cos( radians('%s') ) * cos( radians( '%s' ) ) * cos( radians( '%s' ) - radians('%s') ) + sin( radians('%s') ) * sin( radians( '%s' ) ) ) ) 
AS distance FROM wp_postmeta HAVING distance < '%s' ORDER BY distance LIMIT 0 , 200",
  mysql_real_escape_string($center_lat), // podana przez usera lat
  $wpdb->get_var("SELECT meta_value FROM wp_postmeta WHERE meta_key = 'lat'"), //brana z bazy wartosc lat
  $wpdb->get_var("SELECT meta_value FROM wp_postmeta WHERE meta_key = 'lng'"), //brana z bazy wartosc lng
  mysql_real_escape_string($center_lng), // podana przez usera lng
  mysql_real_escape_string($center_lat), podana przez usera lat
  $wpdb->get_var("SELECT meta_value FROM wp_postmeta WHERE meta_key = 'lat'"), //brana z bazy lat
  mysql_real_escape_string($radius)))//podany przez usera dystans
[SQL] pobierz, plaintext 

I nie wiem jak to doprowadzić do działania. Ogólnie wydaje mi się, że błąd popełniłem przy wybieraniu wlasnie z bazy tych lat i lng natomiast nie do końca wiem jak to zrobić. Czy mógłby mi ktoś powiedzieć jak powinno wyglądać prawidłowe zapytanie?




Edit:
Tak, siedzenie po nocach nie pomaga... Tutaj powinien być jakiś INNER JOIN zamiast sprintf'a prawda? (IMG:style_emoticons/default/biggrin.gif)

Ten post edytował Kulfon 18.02.2014, 14:01:26

[mar1aczi]

Możesz wystawić zrzut kawałka tej tabeli dla 5-10 postów.

[Kulfon]

Mam 1 testowy post. Było ich więcej ale na moment obecny działam na jednym. Wygląda to tak:

[SQL] pobierz, plaintext 
--
-- Struktura tabeli dla tabeli `wp_postmeta`
--
 
CREATE TABLE `wp_postmeta` (
  `meta_id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `post_id` bigint(20) UNSIGNED NOT NULL DEFAULT '0',
  `meta_key` varchar(255) DEFAULT NULL,
  `meta_value` longtext,
  PRIMARY KEY  (`meta_id`),
  KEY `post_id` (`post_id`),
  KEY `meta_key` (`meta_key`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;
 
--
-- Zrzut danych tabeli `wp_postmeta`
--
 
INSERT INTO `wp_postmeta` (`meta_id`, `post_id`, `meta_key`, `meta_value`) VALUES
(411, 75, 'name', 'Miejsce 1'),
(412, 75, 'address', 'Dywizjonu 303 11/505 30-123 KrakĂłw'),
(420, 75, 'publicOrPrivate', 'public'),
(423, 75, 'telefon', '123123123'),
(426, 75, 'custom_logo', 'custom_logo_75.jpg'),
(427, 75, 'custom_photo1', 'custom_photo1_75.jpg'),
(428, 75, 'custom_photo2', 'custom_photo2_75.jpg'),
(658, 75, 'lat', '50.08491009999999'),
(659, 75, 'lng', '20.003797200000008');
[SQL] pobierz, plaintext 

[mar1aczi]

W jednym wierszu post_id, lat, lng i odległość (musisz podstawić do wzoru):

[SQL] pobierz, plaintext 
SELECT r.post_id,
	sum(IF(meta_key = 'lat', meta_value, 0)) AS lat,
    sum(IF(meta_key = 'lng', meta_value, 0)) AS lng,
    'obliczenia' AS distance
FROM (
SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE meta_key IN ('lat', 'lng')) r
GROUP BY r.post_id
[SQL] pobierz, plaintext 

Z resztą powinieneś sobie poradzić (IMG:style_emoticons/default/wink.gif)

Ten post edytował mar1aczi 18.02.2014, 14:34:22

[Kulfon]

Aaa czekaj w sensie jak podstawić: "W jednym wierszu post_id, lat, lng i odległość (musisz podstawić do wzoru):".
O tyle o ile wydaje mi się, że rozumiem zapytanie to nie wiem o co Ci chodziło (IMG:style_emoticons/default/biggrin.gif)

Ten post edytował Kulfon 18.02.2014, 14:39:53

[mar1aczi]

Tam gdzie ja wstawiłem 'obliczenia' musisz zastosować wzór na odległość między punktami. Współrzędne punktu od użytkownika pobierasz przed wykonaniem zapytania i "wkładasz" do wzoru w zapytaniu z odpowiednim where/having.

Ten post edytował mar1aczi 18.02.2014, 14:43:11

[Kulfon]

I wyszło coś takiego:

[PHP] pobierz, plaintext 
sprintf("SELECT r.post_id,
	sum(IF(meta_key = 'lat', meta_value, 0)) AS lat,
    sum(IF(meta_key = 'lng', meta_value, 0)) AS lng,
    ( 3959 * acos( cos( radians('%s') ) * cos( radians( lat ) ) * cos( radians( lng ) - radians('%s') ) + sin( radians('%s') ) * sin( radians( lat ) ) ) )
     AS distance HAVING distance < '%s'
FROM (SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE meta_key IN ('lat', 'lng')) r
GROUP BY r.post_id LIMIT 0, 20",
  mysql_real_escape_string($center_lat),
  mysql_real_escape_string($center_lng),
  mysql_real_escape_string($center_lat),
  mysql_real_escape_string($radius))
[PHP] pobierz, plaintext 

Natomiast wynik zapytania to pusta tablica - nic mi nie zwraca :/

Dobra... to szukam błędu
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'HAVING distance < '100'
FROM (SELECT post_id, meta_key, meta_value FROM wp_postm' at line 5

Ten post edytował Kulfon 18.02.2014, 14:55:23

[mar1aczi]

Podstaw wartości "od użytkownika" ręcznie do zapytania i sprawdź czy zapytanie wykona Ci się poprawnie "z palca" np. w phpmyadmin.

[Kulfon]

To niestety też jest błąd - dokładnie taki sam
Przykładowo zapytanie wygląda tak:

[SQL] pobierz, plaintext 
SELECT r.post_id,
	sum(IF(meta_key = 'lat', meta_value, 0)) AS lat,
    sum(IF(meta_key = 'lng', meta_value, 0)) AS lng,
    ( 3959 * acos( cos( radians(50.0952545) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(20.078393600000027) ) + sin( radians(50.0952545) ) * sin( radians( lat ) ) ) )
     AS distance HAVING distance < 100
FROM (SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE meta_key IN ('lat', 'lng')) r
GROUP BY r.post_id LIMIT 0, 20
[SQL] pobierz, plaintext 

Ok, delikatnie zmieniłem zapytanie:

[SQL] pobierz, plaintext 
SELECT r.post_id,
	sum(IF(meta_key = 'lat', meta_value, 0)) AS lat,
    sum(IF(meta_key = 'lng', meta_value, 0)) AS lng,
    ( 3959 * acos( cos( radians(50.0952545) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(20.078393600000027) ) + sin( radians(50.0952545) ) * sin( radians( lat ) ) ) )
     AS distance 
FROM (SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE meta_key IN ('lat', 'lng')) r HAVING distance < 100
GROUP BY r.post_id LIMIT 0, 20
[SQL] pobierz, plaintext 

i teraz jeszcze:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP BY r.post_id' at line 7

[mar1aczi]

Tak:

[SQL] pobierz, plaintext 
SELECT rr.post_id, rr.lat, rr.lng,
    ( 3959 * acos( cos( radians(50.0952545) ) * cos( radians( rr.lat ) ) * cos( radians( rr.lng ) - radians(20.078393600000027) ) + sin( radians(50.0952545) ) * sin( radians( rr.lat ) ) ) ) AS distance
FROM (
SELECT r.post_id,
	sum(IF(meta_key = 'lat', meta_value, 0)) AS lat,
    sum(IF(meta_key = 'lng', meta_value, 0)) AS lng
FROM (SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE meta_key IN ('lat', 'lng')) r
GROUP BY r.post_id) rr
HAVING ( 3959 * acos( cos( radians(50.0952545) ) * cos( radians( rr.lat ) ) * cos( radians( rr.lng ) - radians(20.078393600000027) ) + sin( radians(50.0952545) ) * sin( radians( rr.lat ) ) ) ) < 100
[SQL] pobierz, plaintext 

Poczytaj o składni zapytań sql, przyda Ci się taka lektura (IMG:style_emoticons/default/wink.gif)

Ten post edytował mar1aczi 18.02.2014, 15:29:27

[Kulfon]

Tak, racja trochę zaniedbałem bazy danych (IMG:style_emoticons/default/wstydnis.gif)

Hmm tego kodu nie można zmienić na taki? Czemu powtarzać wzór skoro mamy 'AS distance'. To poniżej również działa (IMG:style_emoticons/default/smile.gif)

[SQL] pobierz, plaintext 
SELECT rr.post_id, rr.lat, rr.lng,
    ( 3959 * acos( cos( radians(50.0952545) ) * cos( radians( rr.lat ) ) * cos( radians( rr.lng ) - radians(20.078393600000027) ) + sin( radians(50.0952545) ) * sin( radians( rr.lat ) ) ) ) AS distance
FROM (
SELECT r.post_id,
	sum(IF(meta_key = 'lat', meta_value, 0)) AS lat,
    sum(IF(meta_key = 'lng', meta_value, 0)) AS lng
FROM (SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE meta_key IN ('lat', 'lng')) r
GROUP BY r.post_id) rr
HAVING distance < 100
[SQL] pobierz, plaintext 

Jeszcze jedno małe pytanko. (IMG:style_emoticons/default/smile.gif)

O tyle o ile dane z innej tabeli wyciągnę sobie dodając na koniec tego zapytania:

[SQL] pobierz, plaintext 
LEFT JOIN `wp_ratings` ON (rr.post_id = `wp_ratings`.post_id)
[SQL] pobierz, plaintext 

i po pierwszym SELECT nazwa_kolumny
to zupełnie nie wiem jak dzięki temu zapytaniu z postu powyżej wyciągnąć wartość dla innego meta_key np wartość dla address - czyli żeby oprócz lat i lng jak do tej pory wypluwało również address...

[mar1aczi]

Np.

[SQL] pobierz, plaintext 
SELECT rr.post_id, rr.lat, rr.lng,
( 3959 * acos( cos( radians(50.0952545) ) * cos( radians( rr.lat ) ) * cos( radians( rr.lng ) - radians(20.078393600000027) ) + sin( radians(50.0952545) ) * sin( radians( rr.lat ) ) ) ) AS distance, (SELECT meta_value FROM wp_postmeta p WHERE p.post_id = rr.post_id AND p.meta_key = 'address') AS address
FROM (
SELECT r.post_id,
sum(IF(meta_key = 'lat', meta_value, 0)) AS lat,
sum(IF(meta_key = 'lng', meta_value, 0)) AS lng
FROM (SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE meta_key IN ('lat', 'lng')) r
GROUP BY r.post_id) rr
HAVING distance < 100
[SQL] pobierz, plaintext 

[Kulfon]

No tak, próbowałem w ten sposób:

[SQL] pobierz, plaintext 
SELECT meta_value FROM wp_postmeta WHERE meta_key = 'address' AS address
[SQL] pobierz, plaintext 

Wielkie dzięki (IMG:style_emoticons/default/smile.gif)