open All Channels
seplocked EVE Technology Lab
blankseplocked [SQL Help] Query for jump counting.
 
This thread is older than 90 days and has been locked due to inactivity.


 
Author Topic

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. Neutral

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.

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. Neutral

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 YARRRR!!


I'd personally tar and feather anybody who would put such an implementation on a productive machine Twisted Evil

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 ... :)



 

This thread is older than 90 days and has been locked due to inactivity.


 


The new forums are live

Please adjust your bookmarks to https://forums.eveonline.com

These forums are archived and read-only