Autor Zpráva
danhill
Profil
Ahoj všem,
mám takový trochu nestandardní dotaz.
Potřeboval bych vědět, zda v MYSQLi lze sestavit dotaz, který mi vybere souřadnice z databáze a rovnou je převede do tvaru jaký potřebuji.
Normálně tohle řeším až v php funkci, ale dost by mi pomohlo při vykreslování tabulek přes jquery a hlavně vyhledávání v nich, kdyby se mi v některých případech vracela už požadovaná hodnota.
Jde o to, že mám dva sloupce 'lat' a 'lon' a v nich jsou uloženy hodnoty zeměpisné šířky a délky v hodnotách DOUBLE.
Tedy mám hodnotu
lat= 49.3368
a
lon=16.91605
a nejen, že je přes CONTACT potřebuji sloučit do jednoho sloupce, to zvládnu i sám,
ale hlavně tyto hodnoty potřebuji rovnou konvertovat do DM tvaru,
tedy v tomto případě by výsledek byl:
N 49° 20.208 E 016° 54.963

V příklad uvedu jak to převedu v php:
ifunction double2degmin ($lat,$lon) {
    if (($lat!=0) AND ($lon!=0)) {
        if ($lat>0) {
            $ns = 'N';
        }
        else if ($lat<0) {
            $ns = 'S';
        }
        if ($lon>0) {
            $ew = 'E';
        }
        else if ($lon<0) {
            $ew = 'W';
        }
        $lat = abs($lat);
        $lon = abs($lon);
        
        $vars_lat = explode(".",$lat);
        $deg_lat = $vars_lat[0];
        if ($deg_lat<10) {
        $deg_lat = "0".$deg_lat;
        }    
        $min_lat = "0.".$vars_lat[1];
        $min_lat = $min_lat * 60;
        $min_lat = number_format($min_lat, 3, '.', '');
        if ($min_lat<10) {
        $min_lat = "0".$min_lat;
        }    
        
        $vars_lon = explode(".",$lon);
        $deg_lon = $vars_lon[0];
        if ($deg_lon<100 AND $deg_lon>10) {
            $deg_lon = "0".$deg_lon;
        }
        else if ($deg_lon<10) {
            $deg_lon = "00".$deg_lon;
        }
        $min_lon = "0.".$vars_lon[1];
        $min_lon = $min_lon * 60;
        $min_lon = number_format($min_lon, 3, '.', '');
        if ($min_lon<10) {
            $min_lon = "0".$min_lon;
        }
        return $degmin = $ns.' '.$deg_lat.'° '.$min_lat.' '.$ew.' '.$deg_lon.'° '.$min_lon;
    }
    else {
        return $degmin ='';
    }
}

Nedokázal by prosím někdo z vás náhodou tuhle funkci imlepentovat už rovnou do dotazu?
Děkuji moc.
Keeehi
Profil
Tak samozřejmě že se to dá, jen to není nic pěkného. Nějak ale nechápu, v čem je s jQuery problém. Vždyť to bude volat nějaký PHP kód který jí vrátí data z databáze. Tak proč by to přitom nemohl rovnou i naformátovat. Kód pro to evidentně máš.
DarkMeni
Profil
SELECT
    @lat := lat,
    @lon := lon,
    
    @ns := IF(@lat > 0, 'N', 'S'),
    @deg_lat := LPAD(ABS(FLOOR(@lat)), 2, '0'),
    @min_lat := LPAD(ABS(MOD(@lat, 1)) * 60, 6, '0'),
    @dm_lat := CONCAT(@ns, ' ', @deg_lat, '° ', @min_lat),
    
    @ew := IF(@lon > 0, 'E', 'W'),
    @deg_lon := LPAD(ABS(FLOOR(@lon)), 3, '0'),
    @min_lon := LPAD(ABS(MOD(@lon, 1)) * 60, 6, '0'),
    @dm_lon := CONCAT(@ew, ' ', @deg_lon, '° ', @min_lon),
    
    CONCAT(@dm_lat, ' ', @dm_lon) dm_coords
 ...

Nevím teda jak moc bude MySQLi kamarádit s proměnnýma

Jinak souhlasím s Keeehi, kdybys to potřeboval pro nějakou subquery tak ok, ale jinak je zbytečné tímto zatěžovat databázi
TomášK
Profil
Nevím teda jak moc bude MySQLi kamarádit s proměnnýma
Pokud vím, tak moc ne, není zaručené pořadí, v jakém se to vyhodnotí. Ale proměnných se dá zbavit - dosadit za ně výrazy, které představují. Krásné to moc nebude …
danhill
Profil
Wooow, tak to čumím teda, jste fakt borci, hned to jdu vyzkoušet...

No, já vím, je to nejspíše moje blbost, že na to nemůžu příjít, ale mě to napadlo jako rychlé řešení (s vaší pomocí samozřejmě) pro mou situaci než vymyslím něco lepšího.
Popis problému asi patří do jiného vlákna,ale pro vysvětlení to aspoň nastíním.
Jde o to, že používám datatables se serverside kvůli velkému množství dat (100tis řádků).
Tedy javascript vytvoří tabulku na základě sestaveného dotazu, ten se provede na serveru a ten vrátí data zpět do js a ten vybleje krásnou tabulku se sortováním, vyhledáváním atd.

Ta myšlenka je super, od té doby co jsem začal datatables používat mi odpadlo mnoho starostí a mám to rád - ovšem jiné přibyly :D .

Problém ale nastává,kdy je to složitější tabulka.
Kdy ve sloupci není skutečná hodnota z databáze, nebo je jich více v jednom sloupci.
Typický příklad je jméno a příjmení, kdy každá hodnota je ve vlastním sloupci v db,
nebo například, kdy v db je hodnota,ale v tabulce se vykresluje ikonka, nebo jeden sloupec je hodnota a druhý je její title atd ...
to jsou situace,kdy mé dovednosti s js obecně končí a celý jquery plugin se pod mou hloupostí hroutí. Přestane fungovat vyhledávání, sortování atp...

Možná to datatables ani neumí, ale to se mi nezdá, spíše to fakt neumím ...
Teď jdu vyzkoušet ten zázračný dotaz ...

Moc děkuji za váš čas a rychlost!


DarkMeni:
Ještě jednou moc děkuji, tohle funguje přesně jak jsem potřeboval.
Dokonce to převede přes 50tis. souřadnic za 0.0004 sekund, což je až neuvěřitelné ...
Ale vrací to všechny sloupce vytvořených proměnných, to nejde aby se vrátil jen ten jeden s výsledkem? (ale to asi úplně nevadí, vyberu si jen ten poslední, jen mě to zajímá)
A taky @min_lat a @min_lon vrací špatně nulu, když je menší než 10. Tedy místo 01.234 vrátí 1.2340. To je zvláštní když u @deg_lat to chodí správně ??
DarkMeni
Profil
danhill:
A taky @min_lat a @min_lon vrací špatně nulu
To bylo z důvodu, že to pracovalo nejdřív matematicky, a potom se to převedlo na řetězec, a nebyl nastavený požadovaný počet desetinných míst.
Stačí třeba funkcí TRUNCATE nastavit 3 desetinná místa a mělo by to být v pohodě.
Btw, funkce TRUNCATE se chová jako zaokrouhlení dolů pro desetinná místa, pokud bude potřeba klasický zaokrouhlení (dolů do 0,4 a od 0,5 nahoru) tak se dá místo TRUNCATE použít ROUND

A taky mě teď napadlo že by pro získání cifer za des. tečkou mohlo být lon - FLOOR(lon) rychlejší než MOD(@lon, 1) (zbytek po dělení jedničkou)

Aby to nevracelo hodně sloupců, tak se to dá udělat takle:
SELECT
    CONCAT(
        /* latitude  */
        /* ns        */ IF(lat > 0, 'N', 'S'), ' ',
        /* deg_lat   */ LPAD(ABS(FLOOR(lat)), 2, '0'), '° ',
        /* min_lat   */ LPAD(ABS(TRUNCATE(lat - FLOOR(lat), 3)) * 60, 6, '0'), ' ',
        /* longitude */
        /* ew        */ IF(lon > 0, 'E', 'W'), ' ',
        /* deg_lon   */ LPAD(ABS(FLOOR(lon)), 3, '0'), '° ',
        /* min_lon   */ LPAD(ABS(TRUNCATE(lon - FLOOR(lon), 3)) * 60, 6, '0')
    )
    AS dm_coords
danhill
Profil
No jak vidím, hrubě jsem studium MYSQL podcenil :)
Ušetřil bych si mnoho práce v php, kdybych se o tyhle funkce zajímal dříve.

DarkMeni:
Moc děkuji, funguje to bezvadně a na tomto příkladu jsi mi krásně demonstroval, jak se tyhle databázové funkce dají používat.
danhill
Profil
Ještě se k tomu vrátím ...
To odčítání se chová velmi zvláštně. Způsobuje to nepřesné výsledky.
Když udělám:

lat=49.8412

SELECT
TRUNCATE(lat,0) AS cele_cislo,
(lat - TRUNCATE(lat,0)) AS zbytek

Dotaz vrátí správně cele_cislo=49
ale zbytek=0.8412000000000006

To přece není možné,ne?
U lat = 50.032116666667 vrátí zbytek=0.03211666666700097
U lat = 50.0195 vrátí zbytek=0.01950000000000074
atd.

Jak je to možné?
Kajman
Profil
Časté potíže, zajímavosti a poučné debaty » Výsledkem výpočtu 1 - 0.9 není přesně 0.1
danhill
Profil
Hm, to je hustý teda...
No jde o to, že pokud mám v například db :
lat = 50.034766666667, lon = 14.496966666667

je správný výsledek : N 50° 02.086 E 014° 29.818

Ale dotaz DarkMeni vrátí N 50° 02.100 E 014° 29.820

Což je sice super, že to funguje, ale to zaokrouhlování tam dělá velký bordel teda a zpětné vyhledávání použít nelze, protože se ke stejnému číslu zpátky nijak nedostanu ...
Kajman
Profil
Pokud potrebujete v tom formatu i vyhledat, pridejte si dva sloupce s predpocitanym formatem nebo vyhledavejte s prepoctem na puvodni ciselne souradnice s pridanou toleranci.
DarkMeni
Profil
Nebo, nevím jak moc je to spolehlivé, ale v tomto případě pomohlo třeba TRUNCATE(TRUNCATE( ... , 12), 3) příp. může být ROUND nebo kombinace ROUND a TRUNCATE.
MariaDB [(none)]> SELECT TRUNCATE(TRUNCATE(@lat - FLOOR(@lat), 12) * 60, 3);
+----------------------------------------------------+
| TRUNCATE(TRUNCATE(@lat - FLOOR(@lat), 12) * 60, 3) |
+----------------------------------------------------+
|                                              2.086 |
+----------------------------------------------------+
1 row in set (0.00 sec)

Edit: Ale když na to tak koukám, tak už se z toho stává docela prasárna, a já už taky ztrácím představu co ten kód vlastně dělá, takže to vyhledávání s tolerancí zní fajn, jakože přesnější to už asi nebude - vždy se může stát že se nějaká souřadnice převede o 1 des. číslo vedle a když nebude žádná tolerance, tak se to už zpětně nenajde.

Vaše odpověď

Mohlo by se hodit


Prosím používejte diakritiku a interpunkci.

Ochrana proti spamu. Napište prosím číslo dvě-sta čtyřicet-sedm:

0