improved waypoint monitoring

Increasing the mining drone count to 20 eventually caused the price of all ore in the system to bottom out and I had to stop the automation as it was slowly losing credits. I’ll need to improve how I monitor the market across waypoints so I’ve added a bunch of code to do this.

Ultimately i’ll use probes to periodically check each waypoint, but I need to write the probe automation to do this. I’ll have to purchase probes over time and slowly send them out across the system, starting with the waypoints closest to the ENGINEERED_ASTEROID. A quick route to euclidian distance in Postgres is to use the cube extension:

with originCTE as (
  select
    cube(array[x,y]) as position
  from
    waypoint
  where
    reset_date ='2024-03-24' 
  and
    type = 'ENGINEERED_ASTEROID'
), distanceCTE as (
  select 
    waypoint.*,
    cube(array[waypoint.x, waypoint.y]) <-> originCTE.position as distance
  from
    waypoint
  join
    originCTE on 1=1
  where
      reset_date ='2024-03-24'
)
select
    *
from
    distanceCTE
where 'SHIPYARD' = any(traits)
or
    'MARKETPLACE' = any(traits)
order by
    distance;