Useful queries

Useful queries

Reservation module

SELECT
    COUNT(r.id)
FROM t044_reservations r
    INNER JOIN t046_reservation_transactions t ON t.id=r.transaction_id
WHERE t.cancellation_time IS NULL AND t.booking_time > '2011-01-01'
    AND ((strftime('%s',r.departure_time)-strftime('%s',t.booking_time))/60) < 120;

SELECT
    departure_time,departure_place_name, arrival_place_name, SUM(seats)
FROM "t044_reservations"
    inner join t046_reservation_transactions ON t046_reservation_transactions.id=transaction_id 
WHERE
    departure_time like '2011-03-22%' and line_code='106' and service_code='237' and cancellation_time IS NULL
GROUP BY
    departure_place_name, arrival_place_name
ORDER BY
    departure_time, departure_place_name

Stop extractions

select
    s.id,
    X(coalesce(geometry, Transform(MakePoint(x, y, 27572),4326))) AS x,
    Y(coalesce(geometry, Transform(MakePoint(x, y, 27572),4326))) AS y,
    c.name,
    i.name AS city
from
    t012_physical_stops s
    INNER JOIN t007_connection_places c ON c.id=s.place_id
    INNER JOIN t006_cities i ON i.id=c.city_id;

StopAreas for which physical stops are too far

SELECT
    S1.id,
    C.name,
    P.name
FROM
    t012_physical_stops S1,
    t012_physical_stops S2,
    t007_connection_places P,
    t006_cities C
WHERE
    S1.place_id = s2.place_id
    AND S1.place_id = P.id
    AND P.city_id = C.id
    AND distance(S1.geometry, S2.geometry) > 0.05
GROUP BY
    S1.place_id

Cleaning queries

Corrupted objects

DELETE FROM t016_scheduled_services WHERE path_id NOT IN (SELECT id FROM t009_lines);
DELETE FROM t017_continuous_services WHERE path_id NOT IN (SELECT id FROM t009_lines);
DELETE FROM t010_line_stops WHERE line_id NOT IN (SELECT id FROM t009_lines);
DELETE FROM t009_lines WHERE commercial_line_id NOT IN (SELECT id FROM t042_commercial_lines);

Cleaning empty lines

DELETE FROM t009_lines WHERE NOT EXISTS(SELECT s.id FROM t016_scheduled_services s WHERE s.path_id=t009_lines.id);
DELETE FROM t042_commercial_lines WHERE NOT EXISTS(SELECT s.id FROM t009_lines s WHERE s.commercial_line_id=t042_commercial_lines.id);

Cleaning unused stops

WARNING : Do not use this query if DRTArea are used on the SYNTHESE instance

DELETE FROM t012_physical_stops WHERE NOT EXISTS(SELECT s.id FROM t010_line_stops s WHERE s.physical_stop_id=t012_physical_stops.id);

Cleaning driver allocations

DELETE FROM t085_driver_allocations WHERE driver_id NOT IN (SELECT id FROM t026_users);

Cleaning unused calendar template elements

DELETE FROM t055_calendar_template_elements
WHERE
    NOT EXISTS(SELECT c.id FROM t054_calendar_templates c WHERE c.id=t055_calendar_template_elements.calendar_id)
    OR calendar_id=0;

Cleaning everything related to a specific data source

To clean the following tables:

• t009_lines
• t010_line_stops
• t016_scheduled_services

You can use the clean service from the Synthese data source admin. Select “Maintenance” tab, and enter a date in the far future and press “Enregistrer”.

The remaining tables:

• t007_connection_places
• t012_physical_stops
• t042_commercial_lines

Can be cleaned with the following project command (replace the data source id and project name):

DATA_SOURCE_ID=replace_me
sy -crel,PROJECT db_shell "delete from t042_commercial_lines where creator_id like ‘${DATA_SOURCE_ID}’; delete from t007_connection_places where code_by_source like ‘${DATA_SOURCE_ID}’; delete from t012_physical_stops where operator_code like ‘${DATA_SOURCE_ID}’; "

Cleaning roads outside a bounding box

DELETE FROM "t014_road_chunks" WHERE id IN (
  SELECT t.id
  FROM "t014_road_chunks" t, idx_t014_road_chunks_geometry i
  WHERE t.id = i.pkid
  AND
  (
    i.xmin < 3.0487
    OR i.xmax > 3.1485
    OR i.ymin > 45.7952
    OR i.ymax < 45.7585
  )
)

DELETE
FROM t015_roads
WHERE NOT EXISTS (
  SELECT *
  FROM t014_road_chunks c
  WHERE c.road_id = t015_roads.id
)

DELETE
FROM t060_road_places
WHERE NOT EXISTS (
  SELECT *
  FROM t015_roads r
  WHERE r.road_place_id = t060_road_places.id
)

DELETE FROM "t043_crossings" WHERE id IN (
  SELECT t.id
  FROM "t043_crossings" t, idx_t043_crossings_geometry i
  WHERE t.id = i.pkid
  AND
  (
    i.xmin < 3.0487
    OR i.xmax > 3.1485
    OR i.ymin > 45.7952
    OR i.ymax < 45.7585
  )
)

Database reinitialization

35 Public transport stops

DELETE FROM t007_connection_places;
DELETE FROM t012_physical_stops;

35 Public transport lines

DELETE FROM t016_scheduled_services;
DELETE FROM t017_continuous_services;
DELETE FROM t010_line_stops;
DELETE FROM t009_lines;
DELETE FROM t042_commercial_lines;

Corruption detection

Web pages with same rank

SELECT p1.id, p1.title, p2.id, p2.title, p1.rank, p2.rank, p1.up_id FROM "t063_web_pages" p1 INNER JOIN t063_web_pages p2 ON p1.up_id=p2.up_id AND p1.id<p2.id AND p1.site_id=p2.site_id WHERE p1.rank=p2.rank