Discussion Searching for systems with multiple factions in a given state

Is there a way to search for a solar system that has x number of factions that are in the same state? Like for example if I wanted to search for a system with multiple factions currently in a state of outbreak, is there an easy way to do that? I know it's possible to find systems with at least 1 faction in a given state, but I want to find 3, or 4, or 5 factions in a system, all of which are in the same given state.

for example:
1613663823738.png
 
It's easy if you have your own database.
SQL:
select System.name, count(*) as "outbreak_count"
from SystemFaction
join System using(system_id)
where SystemFaction.activestates_flags & (1<<10)
group by SystemFaction.system_id
having count (*) > 1
order by outbreak_count desc
nameoutbreak_count
HIP 202773
Calennero2
Nuenets2
Smethells 1192
18 Puppis2
LHS 28132
Thanatos2
Onileut2
LTT 171492
Turbacobo2
Gadagese2
Samontundji2
 
It's easy if you have your own database.
SQL:
select System.name, count(*) as "outbreak_count"
from SystemFaction
join System using(system_id)
where SystemFaction.activestates_flags & (1<<10)
group by SystemFaction.system_id
having count (*) > 1
order by outbreak_count desc
nameoutbreak_count
HIP 202773
Calennero2
Nuenets2
Smethells 1192
18 Puppis2
LHS 28132
Thanatos2
Onileut2
LTT 171492
Turbacobo2
Gadagese2
Samontundji2
That looks great. I need to learn how to do that. How do I do that? Can you link me your software?
 
That looks great. I need to learn how to do that. How do I do that? Can you link me your software?
It's just standard SQL. I'm using a SQLite database which is updated with my own private EDDN-Listener.
I can't really help you to learn SQL but I'm sure there are plenty of tutorials around.
As for the database: You may be able to build your own with one of the dumps (EDSM, Spansh or EDDB). If you don't listen to EDDN for updates, you'll need to update it regularly from the dump if you are interessted in BGS staff (normaly one tick per day).
 
It's just standard SQL. I'm using a SQLite database which is updated with my own private EDDN-Listener.
I can't really help you to learn SQL but I'm sure there are plenty of tutorials around.
As for the database: You may be able to build your own with one of the dumps (EDSM, Spansh or EDDB). If you don't listen to EDDN for updates, you'll need to update it regularly from the dump if you are interessted in BGS staff (normaly one tick per day).

But did you import the json file to SQLite, or does it support writing SQL queries over json data?
 
But did you import the json file to SQLite, or does it support writing SQL queries over json data?
I build my own (empty) sqlite-database in 2018 and keep it up to date with my own EDDN listener ever since. I once made a station import from EDSM in the early days.
BTW: SQL over json data doesn't sound very efficient.
 
Last edited:
BTW: SQL over json data doesn't sound very efficient.
Didn't even sound possible, that's why I was intrigued :) It's been over 10 years since I worked with MS SQL Server (2005 was the last version I worked with), I saw your screenshot with the ED data json file opened in SQL Server Management Studio and I thought maybe MS had meanwhile implemented some kind of querying functionality over json.

Anyway thanks for your reply, and nice work!
 
I build my own (empty) sqlite-database in 2018 and keep it up to date with my own EDDN listener ever since. I once made a station import from EDSM in the early days.
BTW: SQL over json data doesn't sound very efficient.
It was very inefficient. I had SQL reading through what seemed to be the entire json dump file for every cell in my table. I might look into CVS instead.
 
Top Bottom