These forums have been archived and are now read-only.

The new forums are live and can be found at https://forums.eveonline.com/

EVE Technology Lab

 
  • Topic is locked indefinitely.
 

System names and all the exits gates names?
Author
Previous Topic Next Topic
DSpite Culhach
#1 - 2014-02-25 02:29:38 UTC
What is involved in creating a table of just comma delimited info regarding systems and their exit system names; ie:

"Jita, Muvolainel, Maurasai, Ikushi, Perimeter, Niyabainen, New Caldari, Sobaseki"

I'm just trying to code a little personal GPS and travel tracking program, and have been entering system info in by hand (I just did one test system to make sure it was working), discovered it cant track jumps when you use Jump Bridges because Jump Bridges don't put a jump entry into the logfiles Lol BAD CCP! BAD!.

PS: I've been entering the table by hand because I'm just learning to fiddle in VB2010.

Is it a matter for me to get the EVE DB file and just learn some SQL commands to get that to spit out to a file, or is it more involved then that? SQL stuff is still hazy for me.
I was told there might be an XML file that has exactly that info it that belonged to a map program, but I can't locate it.

I'm just asking for a push in the right direction, I'm just not sure what the best way is or if there is a file out there that might contain what i want already as part of another open source program. Thought someone might know.

I apparently have no idea what I'm doing.
Amun Shazih
Industria Traho Incorporated
#2 - 2014-02-25 08:32:57 UTC  |  Edited by: Amun Shazih
You would need an SQL datadump, setting up SQL server is fairly simple, it requires more than a bouble-click, but it's not intellectually demanding. Just follow one of the tutorials out there.
As for the actual query, it would look something like:

Quote:

SELECT
ss.solarSystemName as 'FromSolarSystem'
,ss.solarSystemID as 'FromID'

,target.solarSystemName as 'ToSolarSystem'
,target.solarSystemID as 'ToSolarSystemID'


FROM [dbo].[mapSolarSystems] as ss
inner join [dbo].[mapSolarSystemJumps] as jump
on ss.solarSystemID= jump.fromSolarSystemID
inner join [dbo].[mapSolarSystems] as target
on jump.toSolarSystemID= target.solarSystemID



Here is the output, you will need to parse it to remove duplication - that's how SQL works unless you want to wrestle with nested queries.

http://4ge.co/meh/SystemJumps.ods

The jumpbridge problem is there indeed, If anyone knows a way to get some jumpbridge info, that would be cool. I imagine some people want to keep it secret too :D
Rob Crowley
State War Academy
#3 - 2014-02-25 08:44:54 UTC
I don't know if this already exists somewhere, but anyway it isn't terribly difficult to do if you're not afraid of SQL and a programming language of your choice.

As you said you wanna get the SDE and pull the necessary info with an SQL command, it's possible that this might even be simple enough to do it entirely in SQL, but since I'm not that much of an SQL pro I'd do most of the logic in a proper programming language, you mentioned VB which would do fine.

Now to get you started here's an example of how to get info about Jita jumpgates:

SELECT ssj.*,ss.solarSystemName FROM mapSolarSystemJumps ssj LEFT JOIN mapSolarSystems ss ON ssj.toSolarSystemID=ss.solarSystemID WHERE ssj.fromSolarSystemID=30000142

If you let your program cycle this through all solar systems you should be able to create your list pretty easily.

Disclaimer: This query works for the Rubicon 1.1 or older SDE, CCP changed this kind of universe data in the Rubicon 1.2 SDE, I haven't yet had time to check out the changes, but it's quite possible that those tables don't exist in the same form anymore. So either don't use the newest SDE (data content shouldn't have changed), or figure out where that info is in the new SDE, or check out Desmont's tool.
KnifeOrSpoon
Contraposition Industries Inc
#4 - 2014-02-25 08:47:00 UTC
http://dl.eve-files.com/media/1402/New_Eden_Stargates.zip - CSV file included with all systemNames, and their destSystem names comma delimited.

How I did it (quick and dirty)

SQL:

SELECT sourceSystem, GROUP_CONCAT(DISTINCT destSystem ORDER BY destSystem ASC SEPARATOR ', ') as destSystems
FROM (
SELECT mapSystem.solarSystemName as sourceSystem, jumpSystem.solarSystemName as destSystem
FROM mapSolarSystems as mapSystem
INNER JOIN mapSolarSystemJumps as systemJumps ON systemJumps.fromSolarSystemID=mapSystem.solarSystemID
INNER JOIN mapSolarSystems as jumpSystem on jumpSystem.solarSystemID=systemJumps.toSolarSystemID
) as Q
GROUP BY sourceSystem
DSpite Culhach
#5 - 2014-02-25 15:48:40 UTC
All this will be great info to get me going. Much appreciated from everyone.

I'll make sure when my program is running it will broadcast in local how much loot i'm carrying and my exact gate position Lol

I apparently have no idea what I'm doing.
Steve Ronuken
Fuzzwork Enterprises
Vote Steve Ronuken for CSM
#6 - 2014-02-25 21:18:44 UTC
https://www.fuzzwork.co.uk/dump/rubicon-1.1-94321/rubicon11.sqlite.bz2 may be of interest, if you don't want to deal with MS SQL server.

Woo! CSM XI!

Fuzzwork Enterprises

Twitter: @fuzzysteve on Twitter