Discussion Searching for systems with multiple factions in a given state

[Flying Scorpion]

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:

 

[gazelle]

It's easy if you have your own database.

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

name	outbreak_count
HIP 20277	3
Calennero	2
Nuenets	2
Smethells 119	2
18 Puppis	2
LHS 2813	2
Thanatos	2
Onileut	2
LTT 17149	2
Turbacobo	2
Gadagese	2
Samontundji	2

 

[Flying Scorpion]

It's easy if you have your own database.

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

name	outbreak_count
HIP 20277	3
Calennero	2
Nuenets	2
Smethells 119	2
18 Puppis	2
LHS 2813	2
Thanatos	2
Onileut	2
LTT 17149	2
Turbacobo	2
Gadagese	2
Samontundji	2

That looks great. I need to learn how to do that. How do I do that? Can you link me your software?

 

[Flying Scorpion]

-_-

 

[Chase Quinnell]

You made your own SQL database for this? That's pretty cool. Sounds like a fun project

 

[Flying Scorpion]

I have no idea what I'm doing

 

[gazelle]

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

 

[askavir]

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?

 

[gazelle]

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.

 

[askavir]

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!

 

[Flying Scorpion]

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.