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 = '02363' LIMIT 1;

SELECT
  1

SELECT 1;

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 = ?
  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 = ?
  AND table_name = ?

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

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)
  ) 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'02363'') = ?
  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_history';

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'02363'') = ?
      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;

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

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

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

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

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 akeys(tags) && ARRAY[ 'amenity',
  'shop',
  'office',
  'craft',
  'leisure',
  'healthcare',
  'emergency',
  'man_made',
  'power',
  'highway',
  'railway',
  'public_transport',
  'landuse',
  'historic',
  'barrier',
  'tourism',
  'sport',
  'place',
  'waterway',
  'natural',
  'geological',
  'route',
  'military',
  'traffic_sign',
  'traffic_calming',
  'seamark',
  'route_master',
  'water',
  'airway',
  'aerialway',
  'building' ] :: text[]

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(((3.256732 49.483315,3.255042 49.485296,3.25222 49.490905,3.251908 49.491911,3.249463 49.499966,3.246866 49.502354,3.244191 49.504546,3.242373 49.505878,3.243851 49.506809,3.24552 49.508978,3.247567 49.511406,3.248774 49.515292,3.247583 49.518602,3.247776 49.519713,3.247892 49.520595,3.2482 49.521957,3.248357 49.522827,3.248111 49.524473,3.248004 49.524832,3.247156 49.527855,3.24646 49.533459,3.246325 49.534646,3.246062 49.5354,3.246059 49.53556,3.246459 49.536285,3.246621 49.536513,3.246659 49.536653,3.246791 49.536761,3.246782 49.537106,3.246718 49.537339,3.246976 49.537568,3.247819 49.538103,3.247686 49.538733,3.247804 49.538865,3.248554 49.539143,3.248584 49.539468,3.248393 49.539723,3.248882 49.539975,3.248988 49.540115,3.249439 49.539971,3.249482 49.539182,3.25012 49.538435,3.250765 49.538056,3.252387 49.53765,3.25294 49.537485,3.252631 49.536999,3.253111 49.536925,3.25464 49.536649,3.254201 49.536168,3.254218 49.536024,3.254466 49.535903,3.254855 49.535829,3.255057 49.535897,3.255806 49.536435,3.256265 49.536611,3.256502 49.536588,3.256559 49.536516,3.256585 49.536195,3.256853 49.535983,3.256866 49.535926,3.256636 49.535762,3.256529 49.535619,3.256544 49.535451,3.256649 49.535253,3.256767 49.535128,3.256953 49.535049,3.257259 49.535015,3.257551 49.534751,3.25783 49.534611,3.25829 49.534513,3.258481 49.534449,3.25865 49.534298,3.258685 49.534098,3.258874 49.533897,3.259445 49.533668,3.259759 49.533615,3.260015 49.533344,3.260333 49.532739,3.261775 49.532784,3.261242 49.532133,3.261251 49.531937,3.261346 49.531865,3.261756 49.531791,3.261948 49.531616,3.262342 49.530659,3.262397 49.530351,3.262999 49.530819,3.263303 49.530943,3.263497 49.530918,3.263564 49.53061,3.263756 49.530562,3.264085 49.53063,3.264408 49.530478,3.264915 49.530435,3.265071 49.53024,3.264874 49.530036,3.264878 49.529673,3.265188 49.529513,3.265559 49.529156,3.266075 49.528852,3.266538 49.528369,3.267522 49.52788,3.26849 49.527857,3.269808 49.527705,3.270089 49.527852,3.270427 49.527842,3.27052 49.527597,3.270629 49.52695,3.271561 49.526324,3.271829 49.525918,3.272583 49.525597,3.272917 49.525011,3.273463 49.524692,3.274078 49.524373,3.275337 49.524053,3.27597 49.523646,3.275989 49.523347,3.276458 49.522951,3.27636 49.522763,3.2764 49.522647,3.276299 49.522251,3.27624 49.522171,3.276196 49.521768,3.276292 49.521617,3.276622 49.521237,3.276664 49.520931,3.276828 49.520739,3.277231 49.520164,3.277225 49.519901,3.277884 49.519886,3.278189 49.51991,3.278708 49.518781,3.277998 49.519235,3.277535 49.519184,3.277431 49.519318,3.277008 49.518794,3.277223 49.518671,3.27769 49.518682,3.278231 49.518541,3.278622 49.518324,3.278942 49.517807,3.279174 49.517715,3.280061 49.517953,3.280266 49.517954,3.280556 49.517843,3.280741 49.5177,3.281062 49.517177,3.281403 49.516999,3.281826 49.51681,3.282162 49.516705,3.282289 49.516608,3.282673 49.516098,3.283667 49.515245,3.283961 49.514961,3.284656 49.514805,3.284762 49.514752,3.28475 49.514612,3.283941 49.513999,3.284059 49.513528,3.284125 49.512967,3.283825 49.51238,3.283877 49.512214,3.284036 49.512084,3.284608 49.511678,3.285225 49.511187,3.285445 49.510909,3.284883 49.510864,3.284958 49.510615,3.285646 49.510662,3.285774 49.510517,3.285676 49.509847,3.284292 49.509588,3.279899 49.508879,3.280033 49.508663,3.280312 49.508416,3.28137 49.507841,3.281826 49.507628,3.282245 49.507395,3.282442 49.507231,3.282661 49.506989,3.281864 49.506525,3.2817 49.506392,3.281706 49.506222,3.28205 49.505439,3.282068 49.505276,3.280257 49.505049,3.278639 49.505688,3.278421 49.506122,3.276143 49.506059,3.272358 49.502756,3.269423 49.501386,3.265994 49.499932,3.261795 49.496309,3.261962 49.495847,3.262339 49.495168,3.262661 49.49468,3.263657 49.493321,3.264288 49.492377,3.264867 49.491619,3.264947 49.491459,3.265247 49.490454,3.265363 49.490135,3.265507 49.489554,3.265612 49.488976,3.26563 49.488415,3.261373 49.488054,3.256732 49.483315)))', 4326))
            AND akeys(tags) && ARRAY['amenity','shop','office','craft','leisure','healthcare','emergency','man_made','power','highway','railway','public_transport','landuse','historic','barrier','tourism','sport','place','waterway','natural','geological','route','military','traffic_sign','traffic_calming','seamark','route_master','water','airway','aerialway','building']::text[];