1

Temat: Relacje wiele do wielu- problem z zapytaniem

Witam
Mam problem ze skonstruowaniem zapytania, mam trzy bazy:
1)mapaDodaj( id_punkt, lat, lng, nazwa)
np. 123|| 23.000 || 19.000 || wierchomla

2)ulubione(id, obiekt, uzytkownik)
np. 1 || 123 || Michal
//kolumna OBIEKT przechowuje numery ID obiektow z bazy mapaDodaj

3)rejestracja (id_user, login)
np. 12|| Michal

Potrzebuje teraz zrobic takie zapytanie:
SELECT lat::float, lng::float, nazwa, grubosctrasa, gruboscpozatrasa, ikona, kamera,
( 6371 * acos( cos( radians("+latLok+")) * cos( radians( lat::float ) ) * cos( radians( lng::float ) - radians("+lngLok+") ) + sin( radians("+latLok+") ) * sin( radians( lat::float ) ) ))
AS dystans 
FROM mapaDodaj
WHERE id_punkt= ID_punktu_z_mapaDodaj
ORDER BY dystans
LIMIT 6
te cosinusy i sinusy to rownanie matematyczne przeliczajace koordy geograficzne na stopnie i wstawiajace je do kolumny DYSTANS.

Co potrzebuje:
Zeby wyswietlily mi sie obiekty z tabeli mapaDodaj z obiektami ktorych ID znajduje sie w tabeli ulubione, wybralo je, a nastepnie za pomoca zrobionej kolumny "dystans" posortowalo sie wg. najblizszej lokalizacji

Jakiekolwiek pomysly?

2

Odp: Relacje wiele do wielu- problem z zapytaniem

W uproszczeniu:

SELECT
    id_punkt,
    lat,
    lng,
    nazwa,
    ( 6371 * acos( cos( radians(23.000)) * cos( radians( lat::float ) )
    * cos( radians( lng::float ) - radians(19.000) )
    + sin( radians(23.000) ) * sin( radians( lat::float ) ) )) AS dystans 
FROM
    mapaDodaj INNER JOIN (SELECT DISTINCT obiekt FROM ulubione) o
        ON id_punkt = obiekt
ORDER BY
    dystans ASC

Drugi sposób:

SELECT
    id_punkt,
    lat,
    lng,
    nazwa,
    ( 6371 * acos( cos( radians(23.000)) * cos( radians( lat::float ) )
    * cos( radians( lng::float ) - radians(19.000) )
    + sin( radians(23.000) ) * sin( radians( lat::float ) ) )) AS dystans
FROM
    mapaDodaj
WHERE
    id_punkt IN (SELECT obiekt FROM ulubione)
ORDER BY
    dystans ASC

Ostatnio edytowany przez gszpetkowski (2011-08-24 23:12:51)

3

Odp: Relacje wiele do wielu- problem z zapytaniem

Poprawna odpowiedź :-) dziękuję gszpetkowski

4

Odp: Relacje wiele do wielu- problem z zapytaniem

Takie jeszcze pytanie jak dorobic jeszcze cos takiego żeby dodatkowo (oprocz juz zrobionych rzeczy) wybieralo rekordy tam gdzie login='Michal'
bo WHERE login='Michal' nie dziala tzn niewiem gdzie mozna wstawic WHERE w takim kodzie sql smile
!!Pole login jest kolumna w bazie mapaDodaj

gszpetkowski wymyslisz cos?

5

Odp: Relacje wiele do wielu- problem z zapytaniem

Jesteś pewien, że kolumna login znajduje się w tabeli mapaDodaj ? Gdyby tak było, to w pierwszym zapytaniu wystarczyłoby dodać

WHERE login = 'Michal'

a w drugim uzupełnić do

id_punkt IN (SELECT obiekt FROM ulubione) AND login = 'Michal' 

Jednakże z tego co umieściłeś na początku wynika jasno, że kolumna login znajduje się w tabeli rejestracja, przy czym rozumiem, że sprawniej byłoby użyć kolumny uzytkownik z ulubione, przez tj. w pierwszym zapytaniu zamienić na:

mapaDodaj INNER JOIN (SELECT DISTINCT obiekt FROM ulubione WHERE uzytkownik = 'Michal') o

a w drugim

id_punkt IN (SELECT obiekt FROM ulubione WHERE uzytkownik = 'Michal')

Ostatnio edytowany przez gszpetkowski (2011-09-01 21:51:20)

6

Odp: Relacje wiele do wielu- problem z zapytaniem

Rozumiem, że chciałeś docelowo uzyskać relację wiele do wielu i proponowałbym następujące rozwiązanie:

Drop Table If Exists ulubione;
Drop Table If Exists mapaDodaj;
Drop Table If Exists rejestracja;

Create Table mapaDodaj (
    id_punkt integer Primary Key,
    lat real,
    lng real,
    nazwa text
);

Create Table rejestracja (
    id integer Primary Key,
    login text
);

Create Table ulubione(
    obiekt integer References mapaDodaj(id_punkt),
    uzytkownik integer References rejestracja(id),
    Primary Key(obiekt, uzytkownik)
);

Przykładowe dane:

Insert Into mapaDodaj (id_punkt, lat, lng, nazwa) Values
    (123, 23.000, 19.000, 'wiechomla'),
    (124, 33.000, 20.000, 'swidnica'),
    (125, 44.000, 22.000, 'turbacz'),
    (126, 55.000, 24.000, 'Kasprowy');
    
Insert Into rejestracja (id, login) Values
    (12, 'Michal'), (13, 'Bartek'), (14, 'Zygmunt');
    
Insert Into ulubione (uzytkownik, obiekt) Values
    (12, 124), (13, 123), (14, 125), (12, 123), (14, 124);

W takim przypadku docelowe zapytanie wygląda tak:

SELECT
    id_punkt,
    lat,
    lng,
    nazwa,
    ( 6371 * acos( cos( radians(23.000)) * cos( radians( lat::float ) )
    * cos( radians( lng::float ) - radians(19.000) )
    + sin( radians(23.000) ) * sin( radians( lat::float ) ) )) AS dystans
FROM
    mapaDodaj
WHERE
    id_punkt IN
        (SELECT obiekt
        FROM ulubione u INNER JOIN rejestracja r
            ON u.uzytkownik = r.id AND login = 'Michal')
ORDER BY
    dystans; -- implicit ASC, więc nie trzeba pisać

Wynik:

 id_punkt | lat | lng |   nazwa   |     dystans
----------+-----+-----+-----------+------------------
      123 |  23 |  19 | wiechomla |                0
      124 |  33 |  20 | swidnica  | 1116.25490067431
(2 rows)

Ostatnio edytowany przez gszpetkowski (2011-09-01 22:26:33)

7

Odp: Relacje wiele do wielu- problem z zapytaniem

Dzięki wielkie gszpetkowski  nie wiem czy dało by się lepiej odpisać na moje pytanie :-)
Niestety
nie mogę zmienić już bazy, początkowo planując bazę postawiłem na banalność zapytania miały być proste ( WHERE, SELECT, ....)
Dopiero jak chciałem rozbudować aplikację zauważyłem takie niedociągnięcia dlatego muszę ratować się twoimi pierwszymi radami,  a inne (tworzenie nowej bazy) są już skopiowane i leżą na dysku czekając na lepsze dni hehe smile
Z wyrazami szacunku za spędzony czas w pełni odpowiadając na moje pytanie- DZIĘKUJĘ :-)

P.S wybrałem sposób pierwszy i kod wygląda następująco:
SELECT
    id_punkt,
    lat,
    lng,
    nazwa,
    ( 6371 * acos( cos( radians(23.000)) * cos( radians( lat::float ) )
    * cos( radians( lng::float ) - radians(19.000) )
    + sin( radians(23.000) ) * sin( radians( lat::float ) ) )) AS dystans
FROM
    mapaDodaj INNER JOIN (SELECT DISTINCT obiekt FROM ulubione WHERE uzytkownik = 'Michal') o
        ON id_punkt = obiekt
ORDER BY
    dystans ASC
P.S II: wszystko jest dla mnie zrozumiałe ale to "o" obok
= 'Michal') o ON
Co ono oznacza do czego służy?

8

Odp: Relacje wiele do wielu- problem z zapytaniem

9

Odp: Relacje wiele do wielu- problem z zapytaniem

Zrozumiałem dziękuję smile
Pozdrawiam
Michał

10

Odp: Relacje wiele do wielu- problem z zapytaniem

Witam wszystkich
Mam kolejny problem i nie chcę zaczynac nowego tematu bo mysle ze zapytanie powyzsze ma duzo wspolnego z zapytaniem wymaganym.

Posiadam trzy bazy:
mapaStok (stok, lat, lng)
np. (Jaworzyna, 50.2323, 23.33333)
      (Wierchomla, 90.2323, 70.33333)

rejestracja (user, lat, lng)
np. (Michal, 10.22222, 30.0121)
      (Bartek, 13.2322, 40.0331)

ulubioneStoki (id, obiekt, uzytkownik)

LAT i LNG to wspolrzedne geograficzne.

Potrzebuje ZAPYTANIE o uzytkowników ktorzy maja dany stok w ulubionych i sa najblizej GO polozeni (obliczanie polozenia na poczatku tematu)
zeby wygladalo miedzyinnymi tak:
Jaworzyna:
10km | Michal |
20KM | gszpetkowski |
30KM | obama|
(pamietajac ze wszyscy powyzsi uzytkownicy maja w ulubionych stok Jaworzyna)

Zapytanie powyzej chyba pasuje do tego co potrzebje ale nie moge wymyslec jak je przerobic.
Jakies pomysly? gszpetkowski :-)?

(np.)
Obliczanie odleglosci i sortowanie od najblizszej odleglosci (ALE wartosci sa wpisywane statycznie ( bez WHERE STOK=Jaworzyna)
SELECT lat, lng, login,
( 6371 * acos( cos( radians(50.01)) * cos( radians( lat::float ) ) * cos( radians( lng::float ) - radians(19.01) )
   + sin( radians(50.01) ) * sin( radians( lat::float ) ) )) AS dystans

  FROM rejestracja ORDER BY dystans ASC

P.S: Poprostu jak by mozna bylo tam wstawic za

11

Odp: Relacje wiele do wielu- problem z zapytaniem

Select
    coordinateDistance(49.3, 19.95, lat, lng) As "Dystans [Km]",
    login AS "Użytkownik"
From rejestracja
Where
    login In (Select uzytkownik From ulubionestoki Where obiekt = 'Zakopane')
Order By "Dystans [Km]";

Ostatnio edytowany przez gszpetkowski (2011-10-10 23:32:52)

12

Odp: Relacje wiele do wielu- problem z zapytaniem

Dzięki gszpetkowski Postgisa mam zainstalowanego ale jeszcze nie wgłębiałem się w niego w takich zapytaniach smile
Nie wiedziałem o tym "user" na przyszłość napewno będę pamiętać
Pozdrawiam
Michał

13

Odp: Relacje wiele do wielu- problem z zapytaniem

gszpetkowski Po wykonaniu twojego kodu ( deklaracja funkji + zapytanie) zwrocilo mi error:

Problem jest w tym ze wszystkie lat i lng mam varchar(40) dlatego za kazdym razem musze w sposob nieuczciwy konwertowac za pomoca lat::float ....
w tym przypadku jednak nawet hakjerskie sztuczki nie podolaly
jakies pomysly?

ERROR:  invalid input syntax for type double precision: ""

********** Błąd **********

ERROR: invalid input syntax for type double precision: ""
Stan SQL:22P02

14

Odp: Relacje wiele do wielu- problem z zapytaniem

Select
    coordinateDistance(49.3, 19.95, lat::float, lng::float) As "Dystans [Km]",
    login AS "Użytkownik"
From rejestracja
Where
    login In (Select uzytkownik From ulubionestoki Where obiekt = 'Zakopane')
Order By "Dystans [Km]";

15

Odp: Relacje wiele do wielu- problem z zapytaniem

Wciaz powtarza sie to samo ehh moze wytlumacze bardzo dokladnie moje tabele bo juz niewiem gdzie moze siedziec blad

tabela mapaDodaj:
CREATE TABLE mapadodaj
(
  id_punkt serial NOT NULL,
  lat character varying(40),
  lng character varying(40),
  nazwa character varying(20) NOT NULL,
  grubosctrasa character varying(4),
  gruboscpozatrasa character varying(4),
  temperatura character varying(4),
  opis text,
  ikona character varying(100),
  uzytkownik character varying(20),
  datawpisu character varying(10),
  kamera text,
  CONSTRAINT mapadodaj_pkey PRIMARY KEY (id_punkt)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE mapadodaj OWNER TO postgres;



/*-----------------------------------------*/
TABELA REJESTRACJA

CREATE TABLE rejestracja
(
  id_user serial NOT NULL,
  typ_user character varying(2),
  "login" character varying(20) NOT NULL,
  haslo character varying(20) NOT NULL,
  email character varying(30),
  wojewodztwo character varying(20),
  imie character varying(20),
  nr_gg character varying(20),
  data_wpisu character varying(10),
  lat character varying(40),
  lng character varying(40),
  zdjecie character varying(200),
  CONSTRAINT rejestracja_pkey PRIMARY KEY (id_user)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE rejestracja OWNER TO postgres;


/*-----------------------------------------------*/
TABELA ULUBIONE

CREATE TABLE ulubione
(
  id serial NOT NULL,
  obiekt integer,
  uzytkownik character varying(40),
  CONSTRAINT ulubione_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE ulubione OWNER TO postgres;



dane sa napewno wpisane dlatego niewiem dlaczego wychodzi ten ""...

gszpetkowski w tobie ostatnia nadzieja

16

Odp: Relacje wiele do wielu- problem z zapytaniem

Dobrze, że podałeś DDL'a. Adaptując mój poprzedni przykład do tych tabel postać samej funkcji jest taka sama, reszta wygląda tak:

Insert Into mapadodaj (nazwa, lat, lng) Values
    ('Zakopane', '49.3', '19.95'),
    ('Kielce', '50.883333', '20.616667');
Insert Into rejestracja ("login", haslo, lat, lng) Values
    ('Michal', '123', '50.0613892', '19.938333'), -- Kraków
    ('Bartek', '123', '52.406667', '16.923611'), -- Poznań
    ('Zenon', '123', '54.366667', '18.633333'); -- Gdańsk
Insert Into ulubione (obiekt, uzytkownik) Values
    (1, 'Michal'),
    (1, 'Bartek'),
    (2, 'Zenon'),
    (2, 'Michal');

W zapytaniu jest kilka drobnych zmian, ale generalnie wygląda tak samo:

Select
    coordinateDistance(49.3, 19.95, lat::float, lng::float) As "Dystans [Km]",
    "login" AS "Użytkownik"
From rejestracja
Where
    "login" In (Select uzytkownik From ulubione Where obiekt = 1)
Order By "Dystans [Km]";

Wynik (identyczny jak w poprzednim przykładzie):

   Dystans [Km]   | Użytkownik 
------------------+------------
   84.66677720873 | Michal
 405.461460646987 | Bartek
(2 rows)

Wygląda więc na to, że problem leży w danych. Nota bene w tabeli ulubione możesz dodać klucze obce, żeby zachować więzy integralności. Co do tych pustych string'ów możesz to sprawdź np. przez:

select id_user, "login", lat, lng from rejestracja where lat = '' or lng = '';

Jeśli to możliwe zmień design, trzymanie double precision (15 cyfr znaczących vide IEEE 754) jako text, to trochę jakby proszenie się o kłopoty. Możesz też założyć constaint na kolumny lng i lat, żeby pusty string nie był akceptowany (zauważ, że wartość specjalna NULL jest czymś totalnie różnym o pustego string'u, toteż samo ograniczenie NOT NULL nie wystarczy):

ALTER TABLE rejestracja ADD CONSTRAINT lat_non_empty CHECK (lat <> '');
ALTER TABLE rejestracja ADD CONSTRAINT lng_non_empty CHECK (lng <> '');

17

Odp: Relacje wiele do wielu- problem z zapytaniem

gszpetkowski dajesz mi bardzo duzo madrych i przdatnych rad za ktore dziekuje
Okazalo sie, ze mialem w jednym miejscu zamiast LAT i LNG wlasnie puste Stringi -"" nie widzialem tego wczesniej, pewnie w czasie wczesniejszych testow ( update zmian) musialo sie to zrobic przypadkowo.
Zastosuje sie do tego co napisales.
Pozdrawiam i dziękuję
Michał