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

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(((7.223159 47.829672,7.223591 47.829418,7.223025 47.829227,7.222772 47.829126,7.222193 47.828854,7.222326 47.827658,7.222369 47.826791,7.22238 47.826347,7.222367 47.825146,7.222294 47.824758,7.222219 47.824203,7.222122 47.82391,7.222034 47.823442,7.221891 47.822897,7.221749 47.822414,7.221673 47.822072,7.2214 47.821397,7.221236 47.821133,7.221005 47.82068,7.22082 47.820159,7.220719 47.820164,7.220114 47.820045,7.219326 47.819947,7.2189 47.81984,7.218907 47.819735,7.218736 47.819535,7.21833 47.819156,7.217809 47.818646,7.217527 47.818398,7.216952 47.81779,7.216243 47.817119,7.216189 47.817142,7.21599 47.816855,7.215853 47.816763,7.215567 47.816472,7.215321 47.816281,7.215203 47.816151,7.214734 47.815761,7.214351 47.815462,7.213866 47.81505,7.213413 47.814764,7.213159 47.814642,7.212478 47.814273,7.21168 47.813928,7.21124 47.813804,7.211065 47.813672,7.210799 47.813563,7.207987 47.81217,7.208815 47.811342,7.203685 47.809966,7.200967 47.811881,7.200623 47.811463,7.200298 47.811149,7.20011 47.811239,7.199826 47.811491,7.199431 47.81133,7.199374 47.811409,7.199124 47.81161,7.198593 47.811342,7.197752 47.812214,7.196699 47.812064,7.195269 47.811951,7.19528 47.812052,7.195055 47.812058,7.195082 47.812576,7.192488 47.812508,7.192094 47.813726,7.192276 47.813884,7.189779 47.813506,7.189747 47.813601,7.188559 47.813576,7.188478 47.813441,7.188206 47.813532,7.186528 47.813416,7.186532 47.813585,7.18322 47.813444,7.182852 47.813404,7.181157 47.813362,7.179145 47.81355,7.179183 47.813962,7.179158 47.814222,7.179024 47.814406,7.17864 47.81475,7.17843 47.814868,7.178231 47.814939,7.178225 47.814835,7.178026 47.814314,7.177434 47.813865,7.177182 47.814719,7.176933 47.814688,7.176979 47.814519,7.176767 47.814494,7.176692 47.814912,7.17648 47.814878,7.176427 47.815014,7.17629 47.814918,7.17425 47.814905,7.17404 47.815294,7.173909 47.815368,7.173597 47.815399,7.173428 47.815503,7.173344 47.815668,7.173194 47.816177,7.17306 47.816158,7.172874 47.81625,7.17265 47.816704,7.172621 47.816717,7.172358 47.817258,7.172023 47.818048,7.171716 47.818723,7.171092 47.818692,7.17081 47.818659,7.170409 47.818549,7.169796 47.818423,7.169603 47.818757,7.169289 47.819105,7.168885 47.819053,7.16821 47.81888,7.167747 47.818799,7.167359 47.818763,7.166573 47.818717,7.16624 47.818656,7.165563 47.81862,7.165418 47.818565,7.1647 47.818687,7.164138 47.818799,7.16361 47.818926,7.163352 47.819028,7.16278 47.819364,7.162594 47.819536,7.162251 47.819801,7.161526 47.820286,7.161849 47.820491,7.161603 47.820717,7.161427 47.820916,7.160211 47.821532,7.160104 47.821593,7.160199 47.821824,7.159795 47.821913,7.159429 47.822037,7.159061 47.822185,7.158948 47.822262,7.158156 47.822668,7.157844 47.822843,7.157184 47.823106,7.156212 47.823561,7.155138 47.824103,7.154991 47.824188,7.153701 47.824657,7.152375 47.825208,7.152332 47.825255,7.152015 47.825322,7.151931 47.825415,7.151309 47.82581,7.150701 47.826098,7.150221 47.826348,7.149322 47.826774,7.148832 47.826947,7.147886 47.82717,7.147428 47.827268,7.146609 47.82736,7.14619 47.827447,7.14564 47.827535,7.145222 47.827629,7.144995 47.827656,7.144479 47.827859,7.144327 47.827945,7.143777 47.8281,7.143202 47.82829,7.142741 47.828404,7.142567 47.828472,7.142312 47.828653,7.142147 47.828897,7.142031 47.829204,7.141925 47.829246,7.1416 47.829284,7.14137 47.829283,7.14115 47.829209,7.141028 47.829122,7.140737 47.828997,7.140595 47.828966,7.140232 47.828995,7.13963 47.829131,7.1394 47.829239,7.139183 47.829392,7.138684 47.829802,7.138408 47.830082,7.138215 47.830189,7.137962 47.830188,7.137827 47.830143,7.137287 47.82983,7.137174 47.829798,7.136823 47.829816,7.136598 47.829865,7.13636 47.829959,7.135754 47.830352,7.135421 47.830603,7.135113 47.830745,7.134261 47.830841,7.13395 47.83092,7.133814 47.830995,7.133582 47.831219,7.133392 47.831442,7.133308 47.831652,7.133296 47.83193,7.133154 47.832366,7.132953 47.832478,7.132641 47.832524,7.132156 47.832431,7.131714 47.832312,7.131414 47.832286,7.131177 47.832374,7.131049 47.832589,7.131059 47.833096,7.130703 47.833844,7.130599 47.834237,7.13058 47.834699,7.130684 47.835258,7.130662 47.835519,7.130544 47.835745,7.130481 47.835996,7.130479 47.836136,7.130679 47.836555,7.130783 47.83696,7.130853 47.837401,7.130769 47.837481,7.130043 47.837232,7.129813 47.837119,7.129399 47.836739,7.128943 47.836601,7.128718 47.836448,7.128572 47.836141,7.128559 47.835931,7.128381 47.835794,7.128253 47.835791,7.1278 47.835907,7.127177 47.836014,7.127001 47.836184,7.12685 47.836623,7.126505 47.836886,7.12613 47.837231,7.125969 47.837567,7.125845 47.838059,7.125748 47.838522,7.125757 47.838638,7.125583 47.839051,7.125248 47.839194,7.124664 47.839401,7.124278 47.839468,7.123855 47.839683,7.123744 47.8398,7.123423 47.84004,7.122923 47.840479,7.122266 47.840988,7.122162 47.840937,7.121753 47.840842,7.121414 47.840805,7.120658 47.84069,7.120371 47.840631,7.119409 47.840389,7.119009 47.840305,7.118622 47.840197,7.116416 47.839619,7.116093 47.83956,7.114579 47.839181,7.113856 47.83901,7.114093 47.839386,7.114117 47.83977,7.114178 47.839979,7.11442 47.84062,7.114365 47.840891,7.114245 47.841172,7.114235 47.841358,7.114337 47.841649,7.114373 47.841862,7.114365 47.842063,7.114509 47.84246,7.114535 47.84274,7.114602 47.842938,7.114787 47.843213,7.114883 47.843278,7.115665 47.843674,7.115902 47.843829,7.116116 47.844057,7.116214 47.844243,7.116257 47.844527,7.116365 47.844832,7.116648 47.845392,7.116817 47.845908,7.116741 47.846231,7.116705 47.847122,7.116654 47.847542,7.116453 47.848103,7.116449 47.848111,7.117701 47.848822,7.118074 47.848985,7.118811 47.849236,7.119181 47.849375,7.119559 47.849588,7.120576 47.84997,7.120893 47.850135,7.121625 47.850715,7.121842 47.850718,7.121821 47.850807,7.121914 47.850919,7.122208 47.851071,7.122416 47.851112,7.122523 47.85123,7.122888 47.851337,7.123028 47.851419,7.123086 47.851603,7.123252 47.851705,7.123592 47.851748,7.123802 47.851726,7.124041 47.851751,7.124457 47.851937,7.124936 47.852088,7.126029 47.85224,7.126183 47.852334,7.126515 47.852337,7.126709 47.852369,7.127841 47.852828,7.128464 47.853122,7.129058 47.853372,7.130022 47.853621,7.130803 47.853117,7.131221 47.852882,7.131812 47.852594,7.132973 47.852117,7.133231 47.851992,7.133287 47.851923,7.133525 47.851841,7.134218 47.851458,7.134438 47.851414,7.136697 47.850829,7.137199 47.85074,7.138835 47.850372,7.139351 47.850234,7.139504 47.850105,7.14008 47.849699,7.140346 47.849396,7.140693 47.848871,7.141509 47.848013,7.141723 47.84758,7.141809 47.847298,7.141987 47.847128,7.142232 47.846691,7.142564 47.846181,7.142769 47.845906,7.142868 47.845729,7.14371 47.844928,7.144064 47.84463,7.144547 47.8443,7.145025 47.843947,7.14544 47.843508,7.145757 47.843266,7.146068 47.84292,7.14621 47.842845,7.146478 47.842791,7.14688 47.84276,7.147473 47.842654,7.148516 47.842556,7.149269 47.842359,7.149882 47.842321,7.150479 47.842318,7.151264 47.842336,7.151531 47.842306,7.151726 47.84223,7.153236 47.841498,7.153414 47.841406,7.1541 47.840872,7.155027 47.840074,7.155294 47.839807,7.15545 47.839557,7.156157 47.837918,7.156419 47.837723,7.156535 47.837548,7.156937 47.83728,7.157102 47.837211,7.159109 47.835103,7.1601 47.834152,7.160741 47.833618,7.161048 47.833332,7.161404 47.832955,7.161826 47.832554,7.162958 47.831934,7.163307 47.831704,7.163325 47.831639,7.163542 47.831564,7.165311 47.830495,7.166631 47.829769,7.166816 47.829794,7.166991 47.829868,7.167297 47.829776,7.167579 47.829736,7.168027 47.829729,7.168749 47.829828,7.169011 47.829823,7.169373 47.829768,7.170305 47.829576,7.170995 47.829313,7.171415 47.829174,7.171723 47.829047,7.172087 47.829317,7.172668 47.829093,7.17294 47.829017,7.173029 47.829051,7.173895 47.828935,7.174152 47.828848,7.174978 47.82862,7.175354 47.828475,7.175769 47.828538,7.176455 47.828505,7.177245 47.828495,7.177465 47.828456,7.177919 47.828469,7.17853 47.828411,7.178867 47.828362,7.179195 47.828275,7.179449 47.828165,7.180132 47.827941,7.180565 47.827722,7.180801 47.827658,7.181184 47.827777,7.181319 47.827508,7.182631 47.826793,7.183133 47.826974,7.183509 47.826827,7.183551 47.826872,7.18414 47.826687,7.184199 47.827249,7.184312 47.827798,7.184505 47.827723,7.186241 47.826787,7.186765 47.826527,7.188316 47.825829,7.189748 47.825169,7.191272 47.82687,7.19173 47.826736,7.192547 47.826455,7.193183 47.826259,7.1942 47.825901,7.196476 47.825121,7.1978 47.824618,7.198022 47.824524,7.199372 47.824304,7.200288 47.824194,7.201078 47.824112,7.201973 47.824046,7.204545 47.823778,7.204733 47.823754,7.204877 47.823655,7.205829 47.822873,7.206209 47.82268,7.207992 47.823403,7.209153 47.822506,7.211295 47.82375,7.211393 47.823798,7.211639 47.82347,7.212001 47.823111,7.212119 47.823146,7.213057 47.823195,7.213209 47.823192,7.214285 47.824,7.214444 47.823959,7.215738 47.824701,7.215649 47.824761,7.21578 47.824849,7.217149 47.825666,7.218201 47.826278,7.219011 47.826732,7.219188 47.826862,7.219741 47.827166,7.220393 47.827357,7.22061 47.828351,7.221168 47.828431,7.221775 47.828797,7.223159 47.829672)))', 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[];