Symfony Profiler

SELECT
  t0.id AS id_1,
  t0.name AS name_2,
  t0.measure AS measure_3,
  t0.date AS date_4,
  t0.stats_id AS stats_id_5
FROM
  city_follow_up t0
WHERE
  t0.stats_id = ?

SELECT t0.id AS id_1, t0.name AS name_2, t0.measure AS measure_3, t0.date AS date_4, t0.stats_id AS stats_id_5 FROM city_follow_up t0 WHERE t0.stats_id = 27385;

SELECT
  s0_.id AS id_0,
  s0_.places_count AS places_count_1,
  s0_.emails_count AS emails_count_2,
  s0_.completion_percent AS completion_percent_3,
  s0_.emails_sent AS emails_sent_4,
  s0_.date AS date_5,
  s0_.names_count AS names_count_6,
  s0_.opening_hours_count AS opening_hours_count_7,
  s0_.website_count AS website_count_8,
  s0_.address_count AS address_count_9,
  s0_.siret_count AS siret_count_10,
  s0_.stats_id AS stats_id_11
FROM
  stats_history s0_
WHERE
  s0_.stats_id = ?
ORDER BY
  s0_.id DESC
LIMIT
  100

SELECT s0_.id AS id_0, s0_.places_count AS places_count_1, s0_.emails_count AS emails_count_2, s0_.completion_percent AS completion_percent_3, s0_.emails_sent AS emails_sent_4, s0_.date AS date_5, s0_.names_count AS names_count_6, s0_.opening_hours_count AS opening_hours_count_7, s0_.website_count AS website_count_8, s0_.address_count AS address_count_9, s0_.siret_count AS siret_count_10, s0_.stats_id AS stats_id_11 FROM stats_history s0_ WHERE s0_.stats_id = 27385 ORDER BY s0_.id DESC LIMIT 100;

SELECT
  COUNT(*)
FROM
  osm_object t0
WHERE
  t0.zone = ?

SELECT COUNT(*) FROM osm_object t0 WHERE t0.zone = '67483';

"COMMIT"

"COMMIT";

INSERT INTO action_log (
  type, data, created_at, from_url, who
)
VALUES
  (?, ?, ?, ?, ?)

INSERT INTO action_log (type, data, created_at, from_url, who) VALUES ('stats_de_ville', '{\"insee_code\":\"67483\",\"nom\":\"67483\"}', '2026-07-15 07:08:14', NULL, NULL);

SELECT
  p0_.osm_user AS osm_user_0,
  COUNT(p0_.id) AS sclr_1,
  AVG(
    (
      CASE WHEN p0_.has_opening_hours = 1 THEN
        1
      ELSE
        0
      END
    ) + (
      CASE WHEN p0_.has_address = 1 THEN
        1
      ELSE
        0
      END
    ) + (
      CASE WHEN p0_.has_website = 1 THEN
        1
      ELSE
        0
      END
    ) + (
      CASE WHEN p0_.has_wheelchair = 1 THEN
        1
      ELSE
        0
      END
    ) + (
      CASE WHEN p0_.has_note = 1 THEN
        1
      ELSE
        0
      END
    )
  ) / 5 * 100 AS sclr_2
FROM
  place p0_
WHERE
  p0_.osm_user IS NOT NULL
  AND p0_.osm_user <> ''
  AND p0_.stats_id = ?
GROUP BY
  p0_.osm_user
ORDER BY
  sclr_1 DESC
LIMIT
  100

SELECT p0_.osm_user AS osm_user_0, COUNT(p0_.id) AS sclr_1, AVG((CASE WHEN p0_.has_opening_hours = 1 THEN 1 ELSE 0 END) + (CASE WHEN p0_.has_address = 1 THEN 1 ELSE 0 END) + (CASE WHEN p0_.has_website = 1 THEN 1 ELSE 0 END) + (CASE WHEN p0_.has_wheelchair = 1 THEN 1 ELSE 0 END) + (CASE WHEN p0_.has_note = 1 THEN 1 ELSE 0 END)) / 5 * 100 AS sclr_2 FROM place p0_ WHERE p0_.osm_user IS NOT NULL AND p0_.osm_user <> '' AND p0_.stats_id = 27385 GROUP BY p0_.osm_user ORDER BY sclr_1 DESC LIMIT 100;

SELECT
  t0.id AS id_1,
  t0.disappeared_list AS disappeared_list_2,
  t0.current_list AS current_list_3,
  t0.theme AS theme_4,
  t0.stats_id AS stats_id_5
FROM
  zone_places t0
WHERE
  t0.stats_id = ?

SELECT t0.id AS id_1, t0.disappeared_list AS disappeared_list_2, t0.current_list AS current_list_3, t0.theme AS theme_4, t0.stats_id AS stats_id_5 FROM zone_places t0 WHERE t0.stats_id = 27385;

SELECT
  s0_.id AS id_0,
  s0_.zone AS zone_1,
  s0_.completion_percent AS completion_percent_2,
  s0_.places_count AS places_count_3,
  s0_.avec_horaires AS avec_horaires_4,
  s0_.avec_adresse AS avec_adresse_5,
  s0_.avec_site AS avec_site_6,
  s0_.avec_accessibilite AS avec_accessibilite_7,
  s0_.avec_note AS avec_note_8,
  s0_.name AS name_9,
  s0_.population AS population_10,
  s0_.siren AS siren_11,
  s0_.code_epci AS code_epci_12,
  s0_.codes_postaux AS codes_postaux_13,
  s0_.date_created AS date_created_14,
  s0_.date_modified AS date_modified_15,
  s0_.avec_siret AS avec_siret_16,
  s0_.avec_name AS avec_name_17,
  s0_.avec_email AS avec_email_18,
  s0_.osm_data_date_min AS osm_data_date_min_19,
  s0_.osm_data_date_avg AS osm_data_date_avg_20,
  s0_.osm_data_date_max AS osm_data_date_max_21,
  s0_.budget_annuel AS budget_annuel_22,
  s0_.lat AS lat_23,
  s0_.lon AS lon_24,
  s0_.date_labourage_requested AS date_labourage_requested_25,
  s0_.date_labourage_done AS date_labourage_done_26,
  s0_.kind AS kind_27
FROM
  stats s0_
WHERE
  s0_.zone = ?
ORDER BY
  s0_.id ASC
LIMIT
  1

SELECT s0_.id AS id_0, s0_.zone AS zone_1, s0_.completion_percent AS completion_percent_2, s0_.places_count AS places_count_3, s0_.avec_horaires AS avec_horaires_4, s0_.avec_adresse AS avec_adresse_5, s0_.avec_site AS avec_site_6, s0_.avec_accessibilite AS avec_accessibilite_7, s0_.avec_note AS avec_note_8, s0_.name AS name_9, s0_.population AS population_10, s0_.siren AS siren_11, s0_.code_epci AS code_epci_12, s0_.codes_postaux AS codes_postaux_13, s0_.date_created AS date_created_14, s0_.date_modified AS date_modified_15, s0_.avec_siret AS avec_siret_16, s0_.avec_name AS avec_name_17, s0_.avec_email AS avec_email_18, s0_.osm_data_date_min AS osm_data_date_min_19, s0_.osm_data_date_avg AS osm_data_date_avg_20, s0_.osm_data_date_max AS osm_data_date_max_21, s0_.budget_annuel AS budget_annuel_22, s0_.lat AS lat_23, s0_.lon AS lon_24, s0_.date_labourage_requested AS date_labourage_requested_25, s0_.date_labourage_done AS date_labourage_done_26, s0_.kind AS kind_27 FROM stats s0_ WHERE s0_.zone = '67483' ORDER BY s0_.id ASC LIMIT 1;

"START TRANSACTION"

"START TRANSACTION";

SELECT
  ST_AsText(
    ST_Multi(geom)
  )
FROM
  zone_polygon
WHERE
  insee_code = ?

SELECT ST_AsText(ST_Multi(geom)) FROM zone_polygon WHERE insee_code = '67483';

SELECT
  osm_type,
  osm_id :: text AS osm_id,
  ST_Y(
    ST_Centroid(m.geom)
  ) AS lat,
  ST_X(
    ST_Centroid(m.geom)
  ) AS lon,
  tags :: jsonb AS tags_json,
  version,
  osm_timestamp,
  changeset,
  osm_user,
  uid :: bigint AS uid,
  ST_AsGeoJSON(
    ST_Force2D(m.geom),
    6,
    0
  ) AS geom_geojson,
  COALESCE(
    ST_Length(
      ST_Intersection(
        ST_MakeValid(m.geom),
        bufc.buf
      ):: geography
    ),
    0
  ) AS length_m,
  CASE WHEN (tags -> 'highway') = 'proposed'
  AND exist(tags, 'proposed')
  AND (tags -> 'proposed') = 'cycleway' THEN
    'proposed_cycleway'
  WHEN (tags -> 'highway') = 'cycleway' THEN
    'separated'
  WHEN (tags -> 'highway') = 'busway' THEN
    'separated'
  WHEN (tags -> 'cycleway:both') IN (
    'track', 'opposite_track', 'separate'
  )
  AND exist(tags, 'highway')
  AND (tags -> 'highway') IS NOT NULL
  AND (tags -> 'highway') NOT IN ('cycleway', 'busway') THEN
    'separated'
  WHEN EXISTS (
    SELECT
      1
    FROM
      unnest(
        akeys(tags)
      ) k
    WHERE
      k = 'cycleway'
      OR k LIKE 'cycleway:%'
  )
  AND exist(tags, 'highway')
  AND (tags -> 'highway') IS NOT NULL
  AND (tags -> 'highway') NOT IN ('cycleway', 'busway') THEN
    'lane'
  WHEN (
    exist(tags, 'bicycle')
    AND (tags -> 'bicycle') IS NOT NULL
    AND (tags -> 'bicycle') <> 'no'
  )
  AND exist(tags, 'highway')
  AND (tags -> 'highway') IS NOT NULL
  AND (tags -> 'highway') NOT IN ('cycleway', 'busway')
  AND NOT (
    EXISTS (
      SELECT
        1
      FROM
        unnest(
          akeys(tags)
        ) k
      WHERE
        k = 'cycleway'
        OR k LIKE 'cycleway:%'
    )
  ) THEN
    'designated'
  ELSE
    NULL
  END AS cycleway_category
FROM
  osm_object_meta m CROSS
  JOIN (
    SELECT
      ST_MakeValid(
        ST_SetSRID(
          ST_GeomFromText(?),
          4326
        )
      ) AS poly
  ) z CROSS
  JOIN LATERAL (
    SELECT
      ST_Buffer(z.poly :: geography, ?):: geometry AS buf
  ) bufc
WHERE
  m.geom IS NOT NULL
  AND m.osm_type IN ('way', 'relation')
  AND ST_Intersects(m.geom, bufc.buf)
  AND (
    (tags -> 'highway') = 'proposed'
    AND exist(tags, 'proposed')
    AND (tags -> 'proposed') = 'cycleway'
    OR (tags -> 'highway') IN ('cycleway', 'busway')
    OR (
      EXISTS (
        SELECT
          1
        FROM
          unnest(
            akeys(tags)
          ) k
        WHERE
          k = 'cycleway'
          OR k LIKE 'cycleway:%'
      )
      AND exist(tags, 'highway')
      AND (tags -> 'highway') IS NOT NULL
      AND (tags -> 'highway') NOT IN ('cycleway', 'busway')
    )
    OR (
      (
        exist(tags, 'bicycle')
        AND (tags -> 'bicycle') IS NOT NULL
        AND (tags -> 'bicycle') <> 'no'
      )
      AND exist(tags, 'highway')
      AND (tags -> 'highway') IS NOT NULL
      AND (tags -> 'highway') NOT IN ('cycleway', 'busway')
    )
  )

SELECT osm_type, osm_id::text AS osm_id,
       ST_Y(ST_Centroid(m.geom)) AS lat, ST_X(ST_Centroid(m.geom)) AS lon,
       tags::jsonb AS tags_json, version, osm_timestamp, changeset, osm_user, uid::bigint AS uid,
       ST_AsGeoJSON(ST_Force2D(m.geom), 6, 0) AS geom_geojson,
       COALESCE(ST_Length(ST_Intersection(ST_MakeValid(m.geom), bufc.buf)::geography), 0) AS length_m,
       CASE
         WHEN (tags->'highway') = 'proposed' AND exist(tags, 'proposed') AND (tags->'proposed') = 'cycleway' THEN 'proposed_cycleway'
         WHEN (tags->'highway') = 'cycleway' THEN 'separated'
         WHEN (tags->'highway') = 'busway' THEN 'separated'
         WHEN (tags->'cycleway'MULTIPOLYGON(((7.25055 48.888404,7.250118 48.888864,7.250054 48.889582,7.250024 48.890303,7.250068 48.890675,7.25028 48.89155,7.250373 48.892152,7.250164 48.892836,7.249647 48.892876,7.249774 48.89303,7.249913 48.894346,7.250403 48.894556,7.250468 48.894891,7.250307 48.89515,7.24966 48.894859,7.249014 48.895224,7.248493 48.895558,7.2483 48.895773,7.24827 48.895917,7.24818 48.896958,7.248125 48.89727,7.248023 48.897538,7.247737 48.898045,7.24722 48.898545,7.247467 48.898882,7.248285 48.900156,7.248726 48.900199,7.249483 48.900079,7.250741 48.899954,7.251253 48.899934,7.251128 48.899424,7.253178 48.898156,7.255113 48.897808,7.256026 48.897839,7.256122 48.89783,7.258948 48.897969,7.262479 48.897948,7.262623 48.898373,7.262705 48.898438,7.261438 48.899159,7.263266 48.900152,7.263181 48.900209,7.262913 48.900512,7.264215 48.900876,7.265327 48.900982,7.265589 48.900972,7.266515 48.900901,7.26628 48.90131,7.265975 48.902514,7.265995 48.90291,7.266066 48.903132,7.266206 48.90343,7.266198 48.903791,7.266251 48.904061,7.267294 48.904085,7.267953 48.905082,7.267939 48.905293,7.267793 48.905637,7.267776 48.905902,7.267923 48.906189,7.26825 48.906646,7.269002 48.906097,7.270573 48.906553,7.272107 48.906849,7.272926 48.905884,7.273524 48.905893,7.273966 48.905869,7.274086 48.906208,7.277408 48.905662,7.278586 48.904954,7.278407 48.904781,7.277528 48.904587,7.276644 48.903993,7.276571 48.903873,7.276662 48.903645,7.278162 48.902356,7.278747 48.901772,7.279013 48.901626,7.279205 48.901567,7.281552 48.90236,7.281114 48.902783,7.280985 48.902956,7.282354 48.903429,7.283389 48.902308,7.28291 48.901952,7.280916 48.901089,7.28093 48.900847,7.28184 48.901034,7.282911 48.901417,7.283245 48.901516,7.283511 48.9015,7.283698 48.901047,7.283999 48.900709,7.286091 48.89877,7.285515 48.898418,7.285505 48.898311,7.285636 48.898164,7.285771 48.897937,7.286133 48.898062,7.286596 48.898287,7.286901 48.898361,7.287087 48.898279,7.287532 48.898006,7.287772 48.897838,7.287897 48.897704,7.288118 48.897279,7.288168 48.896915,7.288224 48.896781,7.288514 48.896285,7.288565 48.896025,7.288838 48.895748,7.288826 48.895549,7.288868 48.895481,7.289254 48.895341,7.289397 48.895237,7.289452 48.895026,7.289162 48.894833,7.288625 48.89478,7.288827 48.894496,7.28925 48.894523,7.289227 48.894452,7.288831 48.89385,7.289165 48.893766,7.28906 48.89357,7.288601 48.893595,7.288289 48.892879,7.288185 48.892576,7.288031 48.892352,7.2879 48.89197,7.287815 48.891822,7.287494 48.891557,7.287326 48.891339,7.286857 48.89123,7.286001 48.891098,7.285616 48.890916,7.285321 48.890872,7.284736 48.890919,7.284562 48.890797,7.284355 48.890772,7.283941 48.890896,7.283718 48.890889,7.283525 48.890834,7.283 48.89062,7.282477 48.890585,7.281924 48.890783,7.281597 48.890847,7.281156 48.890877,7.280041 48.890886,7.279141 48.890856,7.27877 48.890798,7.278511 48.890781,7.277795 48.890788,7.277517 48.890822,7.277251 48.890889,7.276944 48.890902,7.276174 48.890878,7.275724 48.890731,7.275491 48.890696,7.274862 48.890642,7.274636 48.890579,7.274345 48.890394,7.273775 48.890244,7.273563 48.890168,7.272805 48.889636,7.272627 48.889565,7.271922 48.889386,7.271348 48.88901,7.270782 48.888353,7.270536 48.88811,7.270493 48.888015,7.270582 48.887696,7.270553 48.887479,7.270329 48.88726,7.270025 48.886575,7.269945 48.886285,7.269925 48.885886,7.268094 48.885571,7.268053 48.885552,7.266767 48.885329,7.266555 48.885274,7.266366 48.885163,7.265284 48.885275,7.264441 48.885277,7.263428 48.885589,7.262394 48.885923,7.260564 48.886646,7.260242 48.886792,7.259844 48.887017,7.259713 48.887113,7.2578 48.887662,7.257028 48.887705,7.256184 48.887706,7.255144 48.887733,7.254785 48.887821,7.254463 48.887924,7.254133 48.887948,7.253547 48.888075,7.253251 48.888199,7.253015 48.888237,7.252286 48.888281,7.251194 48.888377,7.25055 48.888404)))'') IN ('track', 'opposite_track', 'separate')
              AND exist(tags, 'highway')
              AND (tags->'highway') IS NOT NULL AND (tags->'highway') NOT IN ('cycleway', 'busway') THEN 'separated'
         WHEN EXISTS (SELECT 1 FROM unnest(akeys(tags)) k WHERE k = 'cycleway' OR k LIKE 'cycleway:%') AND exist(tags, 'highway')
              AND (tags->'highway') IS NOT NULL AND (tags->'highway') NOT IN ('cycleway', 'busway') THEN 'lane'
         WHEN (exist(tags, 'bicycle') AND (tags->'bicycle') IS NOT NULL AND (tags->'bicycle') <> 'no') AND exist(tags, 'highway')
              AND (tags->'highway') IS NOT NULL AND (tags->'highway') NOT IN ('cycleway', 'busway')
              AND NOT (EXISTS (SELECT 1 FROM unnest(akeys(tags)) k WHERE k = 'cycleway' OR k LIKE 'cycleway:%')) THEN 'designated'
         ELSE NULL
       END AS cycleway_category
FROM osm_object_meta m
CROSS JOIN (SELECT ST_MakeValid(ST_SetSRID(ST_GeomFromText(2000), 4326)) AS poly) z
CROSS JOIN LATERAL (SELECT ST_Buffer(z.poly::geography, ?)::geometry AS buf) bufc
WHERE m.geom IS NOT NULL
  AND m.osm_type IN ('way', 'relation')
  AND ST_Intersects(m.geom, bufc.buf)
  AND (
    (tags->'highway') = 'proposed' AND exist(tags, 'proposed') AND (tags->'proposed') = 'cycleway'
    OR (tags->'highway') IN ('cycleway', 'busway')
    OR (EXISTS (SELECT 1 FROM unnest(akeys(tags)) k WHERE k = 'cycleway' OR k LIKE 'cycleway:%') AND exist(tags, 'highway')
        AND (tags->'highway') IS NOT NULL AND (tags->'highway') NOT IN ('cycleway', 'busway'))
    OR ((exist(tags, 'bicycle') AND (tags->'bicycle') IS NOT NULL AND (tags->'bicycle') <> 'no') AND exist(tags, 'highway')
        AND (tags->'highway') IS NOT NULL AND (tags->'highway') NOT IN ('cycleway', 'busway'))
  )
;

SELECT
  osm_type,
  osm_id :: text AS osm_id,
  ST_Y(
    ST_Centroid(m.geom)
  ) AS lat,
  ST_X(
    ST_Centroid(m.geom)
  ) AS lon,
  tags :: jsonb AS tags_json,
  version,
  osm_timestamp,
  changeset,
  osm_user,
  uid :: bigint AS uid,
  ST_AsGeoJSON(
    ST_Force2D(m.geom),
    6,
    0
  ) AS geom_geojson,
  COALESCE(
    ST_Length(
      ST_Intersection(
        ST_MakeValid(m.geom),
        z.poly
      ):: geography
    ),
    0
  ) AS length_m
FROM
  osm_object_meta m CROSS
  JOIN (
    SELECT
      ST_MakeValid(
        ST_SetSRID(
          ST_GeomFromText(?),
          4326
        )
      ) AS poly
  ) z
WHERE
  m.geom IS NOT NULL
  AND m.osm_type IN ('way', 'relation')
  AND ST_Intersects(m.geom, z.poly)
  AND exist(tags, 'highway')
  AND (tags -> 'highway') NOT IN (
    'motorway', 'motorway_link', 'trunk',
    'trunk_link', 'footway', 'steps',
    'pedestrian', 'bridleway', 'raceway',
    'cycleway', 'busway', 'construction',
    'proposed'
  )
  AND (tags -> 'oneway') IN ('yes', '-1', '1')
  AND NOT (
    (tags -> 'highway') IN ('cycleway', 'busway')
    OR (
      EXISTS (
        SELECT
          1
        FROM
          unnest(
            akeys(tags)
          ) k
        WHERE
          k = 'cycleway'
          OR k LIKE 'cycleway:%'
      )
      AND exist(tags, 'highway')
      AND (tags -> 'highway') IS NOT NULL
      AND (tags -> 'highway') NOT IN ('cycleway', 'busway')
    )
    OR (
      (
        exist(tags, 'bicycle')
        AND (tags -> 'bicycle') IS NOT NULL
        AND (tags -> 'bicycle') <> 'no'
      )
      AND exist(tags, 'highway')
      AND (tags -> 'highway') IS NOT NULL
      AND (tags -> 'highway') NOT IN ('cycleway', 'busway')
      AND NOT (
        EXISTS (
          SELECT
            1
          FROM
            unnest(
              akeys(tags)
            ) k
          WHERE
            k = 'cycleway'
            OR k LIKE 'cycleway:%'
        )
      )
    )
  )
  AND NOT (
    (tags -> 'oneway:bicycle') = 'no'
  )

SELECT osm_type, osm_id::text AS osm_id,
       ST_Y(ST_Centroid(m.geom)) AS lat, ST_X(ST_Centroid(m.geom)) AS lon,
       tags::jsonb AS tags_json, version, osm_timestamp, changeset, osm_user, uid::bigint AS uid,
       ST_AsGeoJSON(ST_Force2D(m.geom), 6, 0) AS geom_geojson,
       COALESCE(ST_Length(ST_Intersection(ST_MakeValid(m.geom), z.poly)::geography), 0) AS length_m
FROM osm_object_meta m
CROSS JOIN (SELECT ST_MakeValid(ST_SetSRID(ST_GeomFromText('MULTIPOLYGON(((7.25055 48.888404,7.250118 48.888864,7.250054 48.889582,7.250024 48.890303,7.250068 48.890675,7.25028 48.89155,7.250373 48.892152,7.250164 48.892836,7.249647 48.892876,7.249774 48.89303,7.249913 48.894346,7.250403 48.894556,7.250468 48.894891,7.250307 48.89515,7.24966 48.894859,7.249014 48.895224,7.248493 48.895558,7.2483 48.895773,7.24827 48.895917,7.24818 48.896958,7.248125 48.89727,7.248023 48.897538,7.247737 48.898045,7.24722 48.898545,7.247467 48.898882,7.248285 48.900156,7.248726 48.900199,7.249483 48.900079,7.250741 48.899954,7.251253 48.899934,7.251128 48.899424,7.253178 48.898156,7.255113 48.897808,7.256026 48.897839,7.256122 48.89783,7.258948 48.897969,7.262479 48.897948,7.262623 48.898373,7.262705 48.898438,7.261438 48.899159,7.263266 48.900152,7.263181 48.900209,7.262913 48.900512,7.264215 48.900876,7.265327 48.900982,7.265589 48.900972,7.266515 48.900901,7.26628 48.90131,7.265975 48.902514,7.265995 48.90291,7.266066 48.903132,7.266206 48.90343,7.266198 48.903791,7.266251 48.904061,7.267294 48.904085,7.267953 48.905082,7.267939 48.905293,7.267793 48.905637,7.267776 48.905902,7.267923 48.906189,7.26825 48.906646,7.269002 48.906097,7.270573 48.906553,7.272107 48.906849,7.272926 48.905884,7.273524 48.905893,7.273966 48.905869,7.274086 48.906208,7.277408 48.905662,7.278586 48.904954,7.278407 48.904781,7.277528 48.904587,7.276644 48.903993,7.276571 48.903873,7.276662 48.903645,7.278162 48.902356,7.278747 48.901772,7.279013 48.901626,7.279205 48.901567,7.281552 48.90236,7.281114 48.902783,7.280985 48.902956,7.282354 48.903429,7.283389 48.902308,7.28291 48.901952,7.280916 48.901089,7.28093 48.900847,7.28184 48.901034,7.282911 48.901417,7.283245 48.901516,7.283511 48.9015,7.283698 48.901047,7.283999 48.900709,7.286091 48.89877,7.285515 48.898418,7.285505 48.898311,7.285636 48.898164,7.285771 48.897937,7.286133 48.898062,7.286596 48.898287,7.286901 48.898361,7.287087 48.898279,7.287532 48.898006,7.287772 48.897838,7.287897 48.897704,7.288118 48.897279,7.288168 48.896915,7.288224 48.896781,7.288514 48.896285,7.288565 48.896025,7.288838 48.895748,7.288826 48.895549,7.288868 48.895481,7.289254 48.895341,7.289397 48.895237,7.289452 48.895026,7.289162 48.894833,7.288625 48.89478,7.288827 48.894496,7.28925 48.894523,7.289227 48.894452,7.288831 48.89385,7.289165 48.893766,7.28906 48.89357,7.288601 48.893595,7.288289 48.892879,7.288185 48.892576,7.288031 48.892352,7.2879 48.89197,7.287815 48.891822,7.287494 48.891557,7.287326 48.891339,7.286857 48.89123,7.286001 48.891098,7.285616 48.890916,7.285321 48.890872,7.284736 48.890919,7.284562 48.890797,7.284355 48.890772,7.283941 48.890896,7.283718 48.890889,7.283525 48.890834,7.283 48.89062,7.282477 48.890585,7.281924 48.890783,7.281597 48.890847,7.281156 48.890877,7.280041 48.890886,7.279141 48.890856,7.27877 48.890798,7.278511 48.890781,7.277795 48.890788,7.277517 48.890822,7.277251 48.890889,7.276944 48.890902,7.276174 48.890878,7.275724 48.890731,7.275491 48.890696,7.274862 48.890642,7.274636 48.890579,7.274345 48.890394,7.273775 48.890244,7.273563 48.890168,7.272805 48.889636,7.272627 48.889565,7.271922 48.889386,7.271348 48.88901,7.270782 48.888353,7.270536 48.88811,7.270493 48.888015,7.270582 48.887696,7.270553 48.887479,7.270329 48.88726,7.270025 48.886575,7.269945 48.886285,7.269925 48.885886,7.268094 48.885571,7.268053 48.885552,7.266767 48.885329,7.266555 48.885274,7.266366 48.885163,7.265284 48.885275,7.264441 48.885277,7.263428 48.885589,7.262394 48.885923,7.260564 48.886646,7.260242 48.886792,7.259844 48.887017,7.259713 48.887113,7.2578 48.887662,7.257028 48.887705,7.256184 48.887706,7.255144 48.887733,7.254785 48.887821,7.254463 48.887924,7.254133 48.887948,7.253547 48.888075,7.253251 48.888199,7.253015 48.888237,7.252286 48.888281,7.251194 48.888377,7.25055 48.888404)))'), 4326)) AS poly) z
WHERE m.geom IS NOT NULL
  AND m.osm_type IN ('way', 'relation')
  AND ST_Intersects(m.geom, z.poly)
  AND exist(tags, 'highway')
  AND (tags->'highway') NOT IN (
    'motorway', 'motorway_link', 'trunk', 'trunk_link',
    'footway', 'steps', 'pedestrian', 'bridleway', 'raceway',
    'cycleway', 'busway', 'construction', 'proposed'
  )
  AND (tags->'oneway') IN ('yes', '-1', '1')
  AND NOT ( (tags->'highway') IN ('cycleway', 'busway')
    OR (EXISTS (SELECT 1 FROM unnest(akeys(tags)) k WHERE k = 'cycleway' OR k LIKE 'cycleway:%') AND exist(tags, 'highway') AND (tags->'highway') IS NOT NULL AND (tags->'highway') NOT IN ('cycleway', 'busway'))
    OR ((exist(tags, 'bicycle') AND (tags->'bicycle') IS NOT NULL AND (tags->'bicycle') <> 'no') AND exist(tags, 'highway') AND (tags->'highway') IS NOT NULL AND (tags->'highway') NOT IN ('cycleway', 'busway') AND NOT (EXISTS (SELECT 1 FROM unnest(akeys(tags)) k WHERE k = 'cycleway' OR k LIKE 'cycleway:%'))))
  AND NOT ((tags->'oneway:bicycle') = 'no')
;

SELECT
  ST_AsText(
    ST_Multi(geom)
  ) AS wkt
FROM
  osm_object_meta m
WHERE
  osm_type = 'relation'
  AND geom IS NOT NULL
  AND (tags -> 'ref:INSEE') = ?
  AND (tags -> 'admin_level') = '8'
  AND (tags -> 'boundary') = 'administrative'
ORDER BY
  ST_Area(geom :: geography) DESC NULLS LAST
LIMIT
  1

SELECT ST_AsText(ST_Multi(geom)) AS wkt
FROM osm_object_meta m
WHERE osm_type = 'relation'
  AND geom IS NOT NULL
  AND (tags->'ref'67483'') = ?
  AND (tags->'admin_level') = '8'
  AND (tags->'boundary') = 'administrative'
ORDER BY ST_Area(geom::geography) DESC NULLS LAST
LIMIT 1;

SELECT
  COUNT(*)
FROM
  information_schema.tables
WHERE
  table_schema = ?
  AND table_name = ?

SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'public' AND table_name = 'osm_object_meta';

SELECT
  osm_type,
  osm_id :: text AS osm_id,
  ST_Y(
    ST_Centroid(geom)
  ) AS lat,
  ST_X(
    ST_Centroid(geom)
  ) AS lon,
  tags :: jsonb AS tags_json,
  version,
  osm_timestamp,
  changeset,
  osm_user,
  uid :: bigint AS uid
FROM
  osm_object_meta m
WHERE
  geom IS NOT NULL
  AND ST_Intersects(
    m.geom,
    ST_GeomFromText(?, 4326)
  )
  AND (tags -> 'man_made') = 'monitoring_station'
  AND (tags -> 'monitoring:bicycle') = 'yes'

SELECT osm_type, osm_id::text AS osm_id,
       ST_Y(ST_Centroid(geom)) AS lat, ST_X(ST_Centroid(geom)) AS lon,
       tags::jsonb AS tags_json, version, osm_timestamp, changeset, osm_user, uid::bigint AS uid
FROM osm_object_meta m
WHERE geom IS NOT NULL
  AND ST_Intersects(m.geom, ST_GeomFromText('MULTIPOLYGON(((7.25055 48.888404,7.250118 48.888864,7.250054 48.889582,7.250024 48.890303,7.250068 48.890675,7.25028 48.89155,7.250373 48.892152,7.250164 48.892836,7.249647 48.892876,7.249774 48.89303,7.249913 48.894346,7.250403 48.894556,7.250468 48.894891,7.250307 48.89515,7.24966 48.894859,7.249014 48.895224,7.248493 48.895558,7.2483 48.895773,7.24827 48.895917,7.24818 48.896958,7.248125 48.89727,7.248023 48.897538,7.247737 48.898045,7.24722 48.898545,7.247467 48.898882,7.248285 48.900156,7.248726 48.900199,7.249483 48.900079,7.250741 48.899954,7.251253 48.899934,7.251128 48.899424,7.253178 48.898156,7.255113 48.897808,7.256026 48.897839,7.256122 48.89783,7.258948 48.897969,7.262479 48.897948,7.262623 48.898373,7.262705 48.898438,7.261438 48.899159,7.263266 48.900152,7.263181 48.900209,7.262913 48.900512,7.264215 48.900876,7.265327 48.900982,7.265589 48.900972,7.266515 48.900901,7.26628 48.90131,7.265975 48.902514,7.265995 48.90291,7.266066 48.903132,7.266206 48.90343,7.266198 48.903791,7.266251 48.904061,7.267294 48.904085,7.267953 48.905082,7.267939 48.905293,7.267793 48.905637,7.267776 48.905902,7.267923 48.906189,7.26825 48.906646,7.269002 48.906097,7.270573 48.906553,7.272107 48.906849,7.272926 48.905884,7.273524 48.905893,7.273966 48.905869,7.274086 48.906208,7.277408 48.905662,7.278586 48.904954,7.278407 48.904781,7.277528 48.904587,7.276644 48.903993,7.276571 48.903873,7.276662 48.903645,7.278162 48.902356,7.278747 48.901772,7.279013 48.901626,7.279205 48.901567,7.281552 48.90236,7.281114 48.902783,7.280985 48.902956,7.282354 48.903429,7.283389 48.902308,7.28291 48.901952,7.280916 48.901089,7.28093 48.900847,7.28184 48.901034,7.282911 48.901417,7.283245 48.901516,7.283511 48.9015,7.283698 48.901047,7.283999 48.900709,7.286091 48.89877,7.285515 48.898418,7.285505 48.898311,7.285636 48.898164,7.285771 48.897937,7.286133 48.898062,7.286596 48.898287,7.286901 48.898361,7.287087 48.898279,7.287532 48.898006,7.287772 48.897838,7.287897 48.897704,7.288118 48.897279,7.288168 48.896915,7.288224 48.896781,7.288514 48.896285,7.288565 48.896025,7.288838 48.895748,7.288826 48.895549,7.288868 48.895481,7.289254 48.895341,7.289397 48.895237,7.289452 48.895026,7.289162 48.894833,7.288625 48.89478,7.288827 48.894496,7.28925 48.894523,7.289227 48.894452,7.288831 48.89385,7.289165 48.893766,7.28906 48.89357,7.288601 48.893595,7.288289 48.892879,7.288185 48.892576,7.288031 48.892352,7.2879 48.89197,7.287815 48.891822,7.287494 48.891557,7.287326 48.891339,7.286857 48.89123,7.286001 48.891098,7.285616 48.890916,7.285321 48.890872,7.284736 48.890919,7.284562 48.890797,7.284355 48.890772,7.283941 48.890896,7.283718 48.890889,7.283525 48.890834,7.283 48.89062,7.282477 48.890585,7.281924 48.890783,7.281597 48.890847,7.281156 48.890877,7.280041 48.890886,7.279141 48.890856,7.27877 48.890798,7.278511 48.890781,7.277795 48.890788,7.277517 48.890822,7.277251 48.890889,7.276944 48.890902,7.276174 48.890878,7.275724 48.890731,7.275491 48.890696,7.274862 48.890642,7.274636 48.890579,7.274345 48.890394,7.273775 48.890244,7.273563 48.890168,7.272805 48.889636,7.272627 48.889565,7.271922 48.889386,7.271348 48.88901,7.270782 48.888353,7.270536 48.88811,7.270493 48.888015,7.270582 48.887696,7.270553 48.887479,7.270329 48.88726,7.270025 48.886575,7.269945 48.886285,7.269925 48.885886,7.268094 48.885571,7.268053 48.885552,7.266767 48.885329,7.266555 48.885274,7.266366 48.885163,7.265284 48.885275,7.264441 48.885277,7.263428 48.885589,7.262394 48.885923,7.260564 48.886646,7.260242 48.886792,7.259844 48.887017,7.259713 48.887113,7.2578 48.887662,7.257028 48.887705,7.256184 48.887706,7.255144 48.887733,7.254785 48.887821,7.254463 48.887924,7.254133 48.887948,7.253547 48.888075,7.253251 48.888199,7.253015 48.888237,7.252286 48.888281,7.251194 48.888377,7.25055 48.888404)))', 4326))
  AND (tags->'man_made') = 'monitoring_station'
  AND (tags->'monitoring:bicycle') = 'yes';

SELECT
  osm_type,
  osm_id :: text AS osm_id,
  ST_Y(
    ST_Centroid(geom)
  ) AS lat,
  ST_X(
    ST_Centroid(geom)
  ) AS lon,
  tags :: jsonb AS tags_json,
  version,
  osm_timestamp,
  changeset,
  osm_user,
  uid :: bigint AS uid,
  ST_AsGeoJSON(
    ST_Force2D(geom),
    6,
    0
  ) AS geom_geojson
FROM
  osm_object_meta m
WHERE
  geom IS NOT NULL
  AND ST_Intersects(
    m.geom,
    ST_GeomFromText(?, 4326)
  )
  AND osm_type IN ('way', 'relation')
  AND (tags -> 'highway') IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

SELECT osm_type, osm_id::text AS osm_id,
                ST_Y(ST_Centroid(geom)) AS lat, ST_X(ST_Centroid(geom)) AS lon,
                tags::jsonb AS tags_json, version, osm_timestamp, changeset, osm_user, uid::bigint AS uid,
                ST_AsGeoJSON(ST_Force2D(geom), 6, 0) AS geom_geojson
            FROM osm_object_meta m
            WHERE geom IS NOT NULL AND ST_Intersects(m.geom, ST_GeomFromText('MULTIPOLYGON(((7.25055 48.888404,7.250118 48.888864,7.250054 48.889582,7.250024 48.890303,7.250068 48.890675,7.25028 48.89155,7.250373 48.892152,7.250164 48.892836,7.249647 48.892876,7.249774 48.89303,7.249913 48.894346,7.250403 48.894556,7.250468 48.894891,7.250307 48.89515,7.24966 48.894859,7.249014 48.895224,7.248493 48.895558,7.2483 48.895773,7.24827 48.895917,7.24818 48.896958,7.248125 48.89727,7.248023 48.897538,7.247737 48.898045,7.24722 48.898545,7.247467 48.898882,7.248285 48.900156,7.248726 48.900199,7.249483 48.900079,7.250741 48.899954,7.251253 48.899934,7.251128 48.899424,7.253178 48.898156,7.255113 48.897808,7.256026 48.897839,7.256122 48.89783,7.258948 48.897969,7.262479 48.897948,7.262623 48.898373,7.262705 48.898438,7.261438 48.899159,7.263266 48.900152,7.263181 48.900209,7.262913 48.900512,7.264215 48.900876,7.265327 48.900982,7.265589 48.900972,7.266515 48.900901,7.26628 48.90131,7.265975 48.902514,7.265995 48.90291,7.266066 48.903132,7.266206 48.90343,7.266198 48.903791,7.266251 48.904061,7.267294 48.904085,7.267953 48.905082,7.267939 48.905293,7.267793 48.905637,7.267776 48.905902,7.267923 48.906189,7.26825 48.906646,7.269002 48.906097,7.270573 48.906553,7.272107 48.906849,7.272926 48.905884,7.273524 48.905893,7.273966 48.905869,7.274086 48.906208,7.277408 48.905662,7.278586 48.904954,7.278407 48.904781,7.277528 48.904587,7.276644 48.903993,7.276571 48.903873,7.276662 48.903645,7.278162 48.902356,7.278747 48.901772,7.279013 48.901626,7.279205 48.901567,7.281552 48.90236,7.281114 48.902783,7.280985 48.902956,7.282354 48.903429,7.283389 48.902308,7.28291 48.901952,7.280916 48.901089,7.28093 48.900847,7.28184 48.901034,7.282911 48.901417,7.283245 48.901516,7.283511 48.9015,7.283698 48.901047,7.283999 48.900709,7.286091 48.89877,7.285515 48.898418,7.285505 48.898311,7.285636 48.898164,7.285771 48.897937,7.286133 48.898062,7.286596 48.898287,7.286901 48.898361,7.287087 48.898279,7.287532 48.898006,7.287772 48.897838,7.287897 48.897704,7.288118 48.897279,7.288168 48.896915,7.288224 48.896781,7.288514 48.896285,7.288565 48.896025,7.288838 48.895748,7.288826 48.895549,7.288868 48.895481,7.289254 48.895341,7.289397 48.895237,7.289452 48.895026,7.289162 48.894833,7.288625 48.89478,7.288827 48.894496,7.28925 48.894523,7.289227 48.894452,7.288831 48.89385,7.289165 48.893766,7.28906 48.89357,7.288601 48.893595,7.288289 48.892879,7.288185 48.892576,7.288031 48.892352,7.2879 48.89197,7.287815 48.891822,7.287494 48.891557,7.287326 48.891339,7.286857 48.89123,7.286001 48.891098,7.285616 48.890916,7.285321 48.890872,7.284736 48.890919,7.284562 48.890797,7.284355 48.890772,7.283941 48.890896,7.283718 48.890889,7.283525 48.890834,7.283 48.89062,7.282477 48.890585,7.281924 48.890783,7.281597 48.890847,7.281156 48.890877,7.280041 48.890886,7.279141 48.890856,7.27877 48.890798,7.278511 48.890781,7.277795 48.890788,7.277517 48.890822,7.277251 48.890889,7.276944 48.890902,7.276174 48.890878,7.275724 48.890731,7.275491 48.890696,7.274862 48.890642,7.274636 48.890579,7.274345 48.890394,7.273775 48.890244,7.273563 48.890168,7.272805 48.889636,7.272627 48.889565,7.271922 48.889386,7.271348 48.88901,7.270782 48.888353,7.270536 48.88811,7.270493 48.888015,7.270582 48.887696,7.270553 48.887479,7.270329 48.88726,7.270025 48.886575,7.269945 48.886285,7.269925 48.885886,7.268094 48.885571,7.268053 48.885552,7.266767 48.885329,7.266555 48.885274,7.266366 48.885163,7.265284 48.885275,7.264441 48.885277,7.263428 48.885589,7.262394 48.885923,7.260564 48.886646,7.260242 48.886792,7.259844 48.887017,7.259713 48.887113,7.2578 48.887662,7.257028 48.887705,7.256184 48.887706,7.255144 48.887733,7.254785 48.887821,7.254463 48.887924,7.254133 48.887948,7.253547 48.888075,7.253251 48.888199,7.253015 48.888237,7.252286 48.888281,7.251194 48.888377,7.25055 48.888404)))', 4326))
              AND osm_type IN ('way', 'relation')
              AND (tags->'highway') IN ('motorway','trunk','primary','secondary','tertiary','unclassified','residential','living_street','motorway_link','trunk_link','primary_link','secondary_link','tertiary_link','service','road');

CREATE TABLE IF NOT EXISTS zone_polygon (
  insee_code TEXT PRIMARY KEY,
  geom geometry(MultiPolygon, 4326) NOT NULL,
  source TEXT NOT NULL,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
)

CREATE TABLE IF NOT EXISTS zone_polygon (
                    insee_code TEXT PRIMARY KEY,
                    geom geometry(MultiPolygon, 4326) NOT NULL,
                    source TEXT NOT NULL,
                    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
                );

SELECT
  COUNT(*)
FROM
  information_schema.tables
WHERE
  table_schema = 'public'
  AND table_name = 'osm_object_history'

SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'public' AND table_name = 'osm_object_history';

INSERT INTO osm_object_meta (
  osm_type, osm_id, geom, tags, version,
  osm_timestamp, changeset, osm_user,
  uid
)
SELECT
  'relation',
  latest.osm_id,
  latest.geom,
  latest.tags,
  latest.version,
  to_char(
    latest.valid_from AT TIME ZONE 'UTC',
    'YYYY-MM-DD"T"HH24:MI:SS"Z"'
  ),
  latest.changeset,
  latest.osm_user,
  latest.uid
FROM
  (
    SELECT
      DISTINCT ON (h.osm_id) h.osm_id,
      h.geom,
      h.tags,
      h.version,
      h.valid_from,
      h.changeset,
      h.osm_user,
      h.uid
    FROM
      osm_object_history h
    WHERE
      h.osm_type = 'relation'
      AND h.visible = true
      AND h.geom IS NOT NULL
      AND (h.tags -> 'boundary') = 'administrative'
      AND (h.tags -> 'admin_level') = '8'
      AND (h.tags -> 'ref:INSEE') IS NOT NULL
    ORDER BY
      h.osm_id,
      h.version DESC,
      h.valid_from DESC
  ) AS latest
WHERE
  NOT EXISTS (
    SELECT
      1
    FROM
      osm_object_meta m
    WHERE
      m.osm_type = 'relation'
      AND m.osm_id = latest.osm_id
  )

INSERT INTO osm_object_meta (
    osm_type, osm_id, geom, tags, version, osm_timestamp, changeset, osm_user, uid
)
SELECT
    'relation',
    latest.osm_id,
    latest.geom,
    latest.tags,
    latest.version,
    to_char(latest.valid_from AT TIME ZONE 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS"Z"'),
    latest.changeset,
    latest.osm_user,
    latest.uid
FROM (
    SELECT DISTINCT ON (h.osm_id)
        h.osm_id,
        h.geom,
        h.tags,
        h.version,
        h.valid_from,
        h.changeset,
        h.osm_user,
        h.uid
    FROM osm_object_history h
    WHERE h.osm_type = 'relation'
      AND h.visible = true
      AND h.geom IS NOT NULL
      AND (h.tags->'boundary') = 'administrative'
      AND (h.tags->'admin_level') = '8'
      AND (h.tags->'ref:INSEE') IS NOT NULL
    ORDER BY h.osm_id, h.version DESC, h.valid_from DESC
) AS latest
WHERE NOT EXISTS (
    SELECT 1 FROM osm_object_meta m
    WHERE m.osm_type = 'relation' AND m.osm_id = latest.osm_id
);