Symfony Profiler

SELECT
  t0.id AS id_1,
  t0.zone AS zone_2,
  t0.completion_percent AS completion_percent_3,
  t0.places_count AS places_count_4,
  t0.avec_horaires AS avec_horaires_5,
  t0.avec_adresse AS avec_adresse_6,
  t0.avec_site AS avec_site_7,
  t0.avec_accessibilite AS avec_accessibilite_8,
  t0.avec_note AS avec_note_9,
  t0.name AS name_10,
  t0.population AS population_11,
  t0.siren AS siren_12,
  t0.code_epci AS code_epci_13,
  t0.codes_postaux AS codes_postaux_14,
  t0.date_created AS date_created_15,
  t0.date_modified AS date_modified_16,
  t0.avec_siret AS avec_siret_17,
  t0.avec_name AS avec_name_18,
  t0.avec_email AS avec_email_19,
  t0.osm_data_date_min AS osm_data_date_min_20,
  t0.osm_data_date_avg AS osm_data_date_avg_21,
  t0.osm_data_date_max AS osm_data_date_max_22,
  t0.budget_annuel AS budget_annuel_23,
  t0.lat AS lat_24,
  t0.lon AS lon_25,
  t0.date_labourage_requested AS date_labourage_requested_26,
  t0.date_labourage_done AS date_labourage_done_27,
  t0.kind AS kind_28
FROM
  stats t0
WHERE
  t0.zone = ?
LIMIT
  1

SELECT t0.id AS id_1, t0.zone AS zone_2, t0.completion_percent AS completion_percent_3, t0.places_count AS places_count_4, t0.avec_horaires AS avec_horaires_5, t0.avec_adresse AS avec_adresse_6, t0.avec_site AS avec_site_7, t0.avec_accessibilite AS avec_accessibilite_8, t0.avec_note AS avec_note_9, t0.name AS name_10, t0.population AS population_11, t0.siren AS siren_12, t0.code_epci AS code_epci_13, t0.codes_postaux AS codes_postaux_14, t0.date_created AS date_created_15, t0.date_modified AS date_modified_16, t0.avec_siret AS avec_siret_17, t0.avec_name AS avec_name_18, t0.avec_email AS avec_email_19, t0.osm_data_date_min AS osm_data_date_min_20, t0.osm_data_date_avg AS osm_data_date_avg_21, t0.osm_data_date_max AS osm_data_date_max_22, t0.budget_annuel AS budget_annuel_23, t0.lat AS lat_24, t0.lon AS lon_25, t0.date_labourage_requested AS date_labourage_requested_26, t0.date_labourage_done AS date_labourage_done_27, t0.kind AS kind_28 FROM stats t0 WHERE t0.zone = '01396' LIMIT 1;

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 -> 'shop') = 'vacant'
    OR EXISTS (
      SELECT
        1
      FROM
        unnest(
          akeys(tags)
        ) k
      WHERE
        k LIKE 'was:%'
    )
  )

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(((5.416419 45.848082,5.416197 45.848448,5.415828 45.848948,5.415412 45.849672,5.414973 45.850354,5.414823 45.850679,5.414442 45.851164,5.414239 45.851359,5.414071 45.851615,5.413761 45.852009,5.413415 45.852292,5.413077 45.852398,5.411338 45.852795,5.409863 45.853163,5.40913 45.85345,5.408524 45.85378,5.407963 45.854271,5.407036 45.855184,5.406347 45.855689,5.40564 45.856145,5.40454 45.856838,5.404407 45.856904,5.403209 45.857508,5.402386 45.858199,5.401317 45.858965,5.400534 45.859493,5.399957 45.859775,5.399247 45.860054,5.398374 45.860364,5.397433 45.860761,5.396664 45.861044,5.396074 45.86124,5.395544 45.861477,5.394911 45.861681,5.394202 45.861951,5.392844 45.862343,5.392308 45.862464,5.392002 45.862516,5.391625 45.862688,5.39132 45.862786,5.390971 45.863046,5.390709 45.863188,5.389757 45.86348,5.388393 45.864052,5.386833 45.864792,5.386303 45.864994,5.385732 45.865188,5.385161 45.865364,5.384462 45.86556,5.383512 45.865898,5.382173 45.866425,5.381115 45.866918,5.380654 45.867218,5.380269 45.867505,5.379564 45.868117,5.379291 45.86833,5.378223 45.869399,5.380048 45.870866,5.380197 45.870764,5.382106 45.872366,5.38251 45.872194,5.384871 45.87366,5.384495 45.87395,5.386689 45.875343,5.388147 45.876259,5.388604 45.876231,5.389041 45.876464,5.389506 45.876748,5.39002 45.877183,5.390799 45.877077,5.390943 45.87711,5.391064 45.87698,5.391417 45.87707,5.391676 45.877101,5.391887 45.877166,5.391987 45.877129,5.3924 45.876875,5.392542 45.876757,5.392772 45.876645,5.392888 45.876643,5.393197 45.876769,5.393343 45.876869,5.393558 45.877196,5.393769 45.877059,5.394163 45.876534,5.394373 45.876316,5.394714 45.876097,5.394882 45.875956,5.395434 45.875964,5.39617 45.876234,5.396664 45.87636,5.396822 45.876376,5.397077 45.876491,5.397417 45.876559,5.397688 45.876676,5.39791 45.876852,5.398461 45.877613,5.399567 45.879243,5.399758 45.879192,5.400188 45.878821,5.401153 45.878794,5.4014 45.878608,5.401766 45.878238,5.401954 45.878142,5.402694 45.878079,5.403089 45.877988,5.403725 45.877547,5.404153 45.877282,5.404666 45.877078,5.404514 45.876808,5.404118 45.876656,5.404088 45.876413,5.404418 45.876094,5.405219 45.875442,5.405488 45.875265,5.406041 45.87515,5.406746 45.875085,5.407403 45.874496,5.407935 45.87436,5.408508 45.873948,5.411377 45.872618,5.412434 45.871914,5.41298 45.871314,5.413418 45.871076,5.414188 45.87101,5.415107 45.870783,5.41636 45.871388,5.417169 45.871558,5.417687 45.87168,5.418038 45.871746,5.418523 45.871879,5.418858 45.872211,5.419322 45.872558,5.419553 45.872627,5.42049 45.872613,5.41999 45.871884,5.420049 45.871535,5.42007 45.871142,5.419891 45.869955,5.420124 45.869992,5.420249 45.869592,5.420209 45.869139,5.419919 45.868648,5.419572 45.868709,5.419094 45.868658,5.419036 45.868338,5.419173 45.867993,5.418386 45.867745,5.417122 45.86716,5.417317 45.867005,5.417677 45.86656,5.417811 45.866425,5.41791 45.866256,5.418048 45.865748,5.418253 45.865427,5.418241 45.865264,5.417873 45.864979,5.417721 45.864754,5.4177 45.864485,5.417741 45.864208,5.418122 45.863551,5.418361 45.863286,5.418643 45.863016,5.418875 45.861518,5.41963 45.860057,5.420516 45.859943,5.420453 45.859644,5.420228 45.859275,5.420125 45.859001,5.420197 45.858696,5.420054 45.858493,5.420032 45.858269,5.419616 45.858045,5.419499 45.85772,5.41958 45.857068,5.420433 45.857055,5.421009 45.857147,5.421399 45.857167,5.421318 45.856867,5.421372 45.856491,5.421353 45.856368,5.42117 45.856151,5.420842 45.856015,5.420423 45.855949,5.420167 45.855974,5.419815 45.855261,5.419883 45.855113,5.419856 45.854843,5.419342 45.853756,5.419237 45.853263,5.419098 45.852878,5.419113 45.852406,5.419254 45.852166,5.419321 45.851808,5.419484 45.851353,5.41987 45.850986,5.420225 45.850744,5.420549 45.850457,5.420902 45.850301,5.421103 45.850189,5.421502 45.850154,5.421863 45.850102,5.42273 45.849864,5.42318 45.849771,5.42313 45.84968,5.423014 45.848598,5.42297 45.848004,5.423071 45.84764,5.423112 45.847262,5.423081 45.847156,5.422947 45.84697,5.422852 45.846651,5.422747 45.846509,5.422455 45.846012,5.422297 45.845654,5.422122 45.84544,5.422075 45.845195,5.422078 45.845012,5.422215 45.844332,5.422385 45.843821,5.422457 45.84367,5.422355 45.843191,5.421822 45.843069,5.42175 45.843279,5.421663 45.843673,5.421553 45.844061,5.421227 45.844489,5.421066 45.844872,5.420816 45.845068,5.420536 45.845371,5.420382 45.845594,5.419822 45.846289,5.419576 45.846546,5.418885 45.847146,5.418522 45.847444,5.417929 45.847971,5.417466 45.84845,5.417433 45.848505,5.416419 45.848082)))', 4326))
            AND (
              (tags->'shop') = 'vacant'
              OR EXISTS (SELECT 1 FROM unnest(akeys(tags)) k WHERE k LIKE 'was:%')
            );

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'01396'') = ?
  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
  COUNT(*)
FROM
  information_schema.tables
WHERE
  table_schema = 'public'
  AND table_name = 'osm_object_meta'

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

SELECT
  COUNT(*)
FROM
  osm_object_meta
WHERE
  osm_type = 'relation'
  AND geom IS NOT NULL
  AND (tags -> 'admin_level') = '8'
  AND (tags -> 'boundary') = 'administrative'
  AND (tags -> 'ref:INSEE') IS NOT NULL

SELECT COUNT(*) FROM osm_object_meta WHERE osm_type = 'relation'
                  AND geom IS NOT NULL
                  AND (tags->'admin_level') = '8'
                  AND (tags->'boundary') = 'administrative'
                  AND (tags->'ref:INSEE') IS NOT NULL;

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

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

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

SELECT
  ST_AsText(
    ST_Multi(geom)
  ) AS wkt
FROM
  (
    SELECT
      DISTINCT ON (h.osm_id) h.geom
    FROM
      osm_object_history h
    WHERE
      h.osm_type = 'relation'
      AND h.visible = true
      AND h.geom IS NOT NULL
      AND (h.tags -> 'ref:INSEE') = ?
      AND (h.tags -> 'admin_level') = '8'
      AND (h.tags -> 'boundary') = 'administrative'
    ORDER BY
      h.osm_id,
      h.version DESC,
      h.valid_from DESC
  ) latest
ORDER BY
  ST_Area(geom :: geography) DESC NULLS LAST
LIMIT
  1

SELECT ST_AsText(ST_Multi(geom)) AS wkt
FROM (
    SELECT DISTINCT ON (h.osm_id)
        h.geom
    FROM osm_object_history h
    WHERE h.osm_type = 'relation'
      AND h.visible = true
      AND h.geom IS NOT NULL
      AND (h.tags->'ref'01396'') = ?
      AND (h.tags->'admin_level') = '8'
      AND (h.tags->'boundary') = 'administrative'
    ORDER BY h.osm_id, h.version DESC, h.valid_from DESC
) latest
ORDER BY ST_Area(geom::geography) DESC NULLS LAST
LIMIT 1;

SELECT
  1

SELECT 1;

CREATE INDEX IF NOT EXISTS zone_polygon_geom_idx ON zone_polygon USING GIST (geom)

CREATE INDEX IF NOT EXISTS zone_polygon_geom_idx ON zone_polygon USING GIST (geom);

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()
                );