I have a Postgres table with categorys and timespans (columns valid_from and valid_to). This represents road sign that are installed for certain time span. I want to compute how many road signs there are necessary. A road sign can be used multiple times if it is not in the same timespan.
Take the following example. For the first two rows, only two signs are needed, since the signs in the first can be moved.
For the other rows, only sign is needed.
I came up with this code, but it does not compute the correct number:
SELECT category, MAX(num_concurrent_objects) AS max_concurrent_objects
FROM (
SELECT v1.category, COUNT(*) AS num_concurrent_objects
FROM signs v1
JOIN signs v2
ON v1.category= v2.category
AND v1.valid_from <= v2.valid_to
AND v1.valid_to >= v2.valid_from
GROUP BY v1.category, v1.valid_from, v1.valid_to
) AS concurrent_objects
GROUP BY category;
How can I compute the number of concurrently used signs for any combination? Any help would be appreciated.
The road signs also have positions (column geom in PostGIS). For multiple-use signs, I would like to compute the ideal movement, meaning the same sign should be moved to its new place based on the smallest distance.
edit:
CREATE TABLE signs (
id serial NOT NULL,
category int4 NULL,
valid_from timestamp NULL,
valid_to timestamp NULL,
geom public.geometry NULL
);
INSERT INTO signs (id, category, valid_from, valid_to, geom) VALUES(6, 8, '2024-04-29 10:32:00.000', '2024-05-31 10:32:00.000', 'SRID=2056;POINT (2602921.5000000005 1201049.4999999998)'::public.geometry);
INSERT INTO signs (id, category, valid_from, valid_to, geom) VALUES(7, 6, '2024-04-29 00:00:00.000', '2024-06-09 14:40:00.000', 'SRID=2056;POINT (2602824.1597646 1201053.8597646)'::public.geometry);
INSERT INTO signs (id, category, valid_from, valid_to, geom) VALUES(4, 3, '2024-05-01 09:35:00.000', '2024-05-30 09:35:00.000', 'SRID=2056;POINT (2602888.000000001 1201083.5)'::public.geometry);
INSERT INTO signs (id, category, valid_from, valid_to, geom) VALUES(9, 2, '2024-05-10 00:00:00.000', '2024-05-24 00:00:00.000', 'SRID=2056;POINT (2602971.500000001 1201055.2499999995)'::public.geometry);
INSERT INTO signs (id, category, valid_from, valid_to, geom) VALUES(5, 2, '2024-04-16 00:00:00.000', '2024-06-06 00:00:00.000', 'SRID=2056;POINT (2602961.500000001 1201084.9999999995)'::public.geometry);
INSERT INTO signs (id, category, valid_from, valid_to, geom) VALUES(8, 2, '2024-04-15 00:00:00.000', '2024-05-15 00:00:00.000', 'SRID=2056;POINT (2602954 1201128.5)'::public.geometry);
INSERT INTO signs (id, category, valid_from, valid_to, geom) VALUES(10, 2, '2024-06-07 00:00:00.000', '2024-07-05 00:00:00.000', 'SRID=2056;POINT (2602856.500000001 1201014.7499999995)'::public.geometry);
