EVE Technology Lab

Hi Tech & Research,

I've been trying to use Grismar's EvE Explorer and DOTLAN in conjunction in order to find a system that:

• Is a 0.1 or 0.2 system
• Has 1 or more stations
• Has a factory service
• Is < 10 jumps from either Amarr, Dodixie, Hek or Jita
• Is > 4j from high sec
• Has less than 200j a day on average
• Is a dead end system

Grismar + DOTLAN offer me the luxury of being able to find out systems that meet all of these prerequisites with the exception of the last one (a dead end system). It's fairly important that it's a dead end system, hence why I've come here looking for aid.

I found an SQL query that goes through the latest static data dump to find 'low sec dead-end systems bordering high sec', which can be found here http://wiki.eve-id.net/Low_sec_dead-end_systems_bordering_high_sec and is the following:

SELECT a.solarSystemName AS lowsec, ra.regionName AS region
FROM (
SELECT m.`fromSolarSystemID` AS fromID, m.`toSolarSystemID` AS toID
FROM mapSolarSystemJumps m
GROUP BY fromID
HAVING count(fromID)=1)
AS deadEnds,
mapSolarSystems a,
mapSolarSystems b,
mapRegions ra
WHERE a.solarSystemID=deadEnds.fromID
AND a.`security` < 0.45
AND a.`security` > 0
AND b.solarSystemID=deadEnds.toID
AND b.`security` >= 0.45
AND ra.regionID=a.regionID
ORDER BY a.solarSystemName;

This is good but not perfect :) I'd like one of you to modify this query or make your own, that uses a dynamic api data dump, to also meet all of the prerequisites that I've listed at the beginning of this post and eve mail me the list of systems produced.

Will pay 50mil to the first person who can EvEmail me the results,

Thanks,
Teebling

Would you mind clarifying what you mean by "dynamic api data dump"? I am curious to see if your question has an answer, but first I'll need to be certain that I understand the question well enough to check.

Tonto - thank you for the reference, but I'm not a big fan of adding layers to my architecture. In this case, I'm somewhat interested in the perverse joy of making it work with just stock T-SQL (a language I've used just once before). Honestly though, since I'm not the customer this time (see http://gaming.stackexchange.com/a/103876/9760), I wanted to get a better handle on the real customer's requirements before spending the next tens of hours building a solution that answers the question I thought was asked.

Teebling - Yes, we've established that this is not a "simple" SQL query. My most significant concern after digging into this further is that I might not be able to do this without a cron, storing historical "jumps in last hour' (http://wiki.eveonline.com/en/wiki/EVE_API_Maps_Jumps) into a table and using that table for the "simple" SQL query.

Therein lies the crux of the acceptance criteria issue - average jumps per day. I was going to offer dropping that one filter in favor of manual inspection - I don't expect a great many systems will match your other criteria. Unfortunately I was able to identify only a single public source for historical jump count records - DOTLAN. Unfortunately, the information is provided in jumps per hour, not jumps per day, and the average over whichever interesting time period would have to be calculated by hand. Additionally, I am not personally super interested in hosting historical API responses myself - I am ... not the most successful systems administrator on the planet. Plus, it would take some days just to gather a small representative sample.

SO - we have a couple of options at this point. One - you might be willing to run the cron and host the database locally. I'm not quite prepared to go there myself, but another player might be willing to help you using something like Pheal. I also might change my mind, but I doubt that this eventuality has a high probability. If you have an alliance, you might have alliance servers and sysadmins to help you out here, but you might have asked them first, and quite possibly gotten rebuffed as here.

Second - we might speak with Wollari about adding average jumps per day to his site. This information would likely be interesting to an audience broader than yourself, and he does already have the data (unlike any other public source a brief search turned up). Again, if you're in a big alliance, you might have a private source, but this seems improbable. I might suggest a small donation, a PLEX or two, might go a long way towards securing his cooperation.

Again though, if average daily jump count isn't critical, I have a decent idea how to put the rest of a (super ugly) "query" (udf) together, and I've checked that the information exists in the static data dump.

Edit: Just checked out your corp, looks like you might have a webmaster after all. If you want to go the own cron route, might want to speak with them first.

I do believe this should be possible.
Although, constructing list of station facilities from the DB proved to be convoluted, I don't see any apparent roadblocks.
You can start from staStations as your beginning point, and couple it with mapSolarsystems.security to fill first two criteria. 'Have a station' is a major criteria, as they are all listed in a separate table.



12 systems. 19 stations. Anyone else? I didn't included the check for manufacturing facilities, though.

EDIT: Modified query to include station ID's for further buildup.

You shouldn't start from mapSolarSystems, as I mentioned earlier. It's inefficeint.

Heh, with all of the two people who bother to read this :). I was worried at first about giving away Sivala as the center of high sec when I found it out - but realized shortly afterwards that nobody else cared ;).