Pages: [1] :: one page |
|
Author |
Thread Statistics | Show CCP posts - 1 post(s) |
Volratheron
|
Posted - 2011.03.23 11:48:00 -
[1]
Hello,
I'm trying to make a small mining tool with a possibility for a releas but unfortunatly I'm rather weak in SQL Querys so I would like to ask for some help.
Select sn.itemName ,COUNT(sn.itemName) as belt, m.security from mapDenormalize m inner join eveNames sn on m.solarSystemID=sn.itemID where m.itemName like '%Asteroid Belt%' or m.itemName like '%Ice Field%' GROUP BY sn.itemName, m.security ORDER BY m.security DESC;
I made such a query that results in a name of a system, the count of its asteroid belts and ice fields and its security system. Now I would like to add a parameter mySystem that would describe system I'm in, seckond parameter that would be maxJumpCount and to modify this somehow to show us the systems using above script in range of maxJumpCount from mySystem.
Got no freaking idea how that should look so I would be realy thankfull for help. Thank You in advance.
|
Lutz Major
|
Posted - 2011.03.23 12:12:00 -
[2]
I remember, that we tried to implement Dijkstra's algorithm back in 1999 in SQL at the university. Unfortunatly we had to give up, because the database back then had too much limitations.
But I found this page. Maybe you can adapt it accordingly.
|
Volratheron
|
Posted - 2011.03.23 12:41:00 -
[3]
Thank You :) This is helpfull :)
|
|
CCP Prism X
Gallente C C P C C P Alliance
|
Posted - 2011.03.24 10:51:00 -
[4]
The only sane way of going about this on a DB level is to have a pre-calculated table for jumps between A and B. That means <system> many records for every system. That does also not allow you to have an avoidance list due to the pre-calculated nature. To take an avoidance list into account you'll need to implement Dijkstra (or something similar) on the application level.
~ CCP Prism X EVE Database Developer and Acting API Dude |
|
Adria Eqviis
Dark Shadow Industries Wildly Inappropriate.
|
Posted - 2011.03.24 12:13:00 -
[5]
Originally by: Lutz Major I remember, that we tried to implement Dijkstra's algorithm back in 1999 in SQL at the university. Unfortunatly we had to give up, because the database back then had too much limitations.
But I found this page. Maybe you can adapt it accordingly.
Dijkstra in SQL... you just made my day ;D
I would probably just create a temporary table and flood fill it with systems reachable up to a certain jump limit, if that serves the purpose. Insert start system at distance 0, fill all systems at distance 1, fill all at dist 2 that aren't in the table yet, rinse, repeat...
Note that if you need the hop count between 2 given systems, it is more efficient to start searching at both ends (two level n neighborhoods instead of one level 2n).
|
Lutz Major
|
Posted - 2011.03.24 13:04:00 -
[6]
Originally by: Adria Eqviis Dijkstra in SQL... you just made my day ;D
Weird, I know, but it was intended as proof-of-concept for us students
I'd personally tar and feather anybody who would put such an implementation on a productive machine
|
Zolka Lando
|
Posted - 2011.03.24 17:16:00 -
[7]
A friend of mine made this example, hope it helps: Linkage
|
Jercy Fravowitz
School of Applied Knowledge
|
Posted - 2011.03.25 14:15:00 -
[8]
... another random drop ...
ancient brute force tool-jumpmap.pl generator, which runs about two hours here.
rows are pairs of from/to systemIDs, jumpcount and a route-is-highsec. long highsec trumps short non-highsec. unique key on sysid pairs. both directions present. no jumpcount cutoff.
the full jumpmap is about 27mil rows, with 510MB on-disk footprint (140 data, 370 index) in mysql here. the zipped addon-mapJumps.sql.bz2 is about 80MB. script is mysql, but again basic sql inserts so adapting to other sql flavors is trivial.
if you need to cut down the size, you may consider just deleting all rows with more than 50 jumps or so. and dont forget to defrag/pack the table.
(and i have actualy run into sqlserver-side dijkstra before ... while investigating why a shared mysql server with some thousand users was doing about 150000 queries-per-second more than it was supposed to ... because someone forgot to add proper exit conditions ... :)
|
|
|
|
Pages: [1] :: one page |
First page | Previous page | Next page | Last page |