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 = '57630' 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 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.017656 48.730807,7.017464 48.730949,7.016504 48.731617,7.014851 48.732912,7.014527 48.733916,7.012897 48.735184,7.011856 48.73578,7.009795 48.736403,7.010759 48.737095,7.011389 48.737718,7.011919 48.738759,7.012296 48.739005,7.013451 48.739719,7.012872 48.740143,7.013315 48.740495,7.014032 48.74116,7.013257 48.741272,7.013085 48.741322,7.013458 48.741676,7.014404 48.742018,7.015031 48.742386,7.015559 48.742577,7.015896 48.743311,7.016785 48.743899,7.01661 48.744208,7.017186 48.744587,7.017461 48.744569,7.01803 48.745095,7.016606 48.746284,7.017303 48.746706,7.016899 48.747053,7.017749 48.747743,7.018702 48.748386,7.019002 48.748329,7.019093 48.748443,7.019456 48.748622,7.020734 48.748375,7.022287 48.748201,7.024153 48.748071,7.024875 48.748215,7.02623 48.748289,7.027165 48.74826,7.028045 48.748309,7.028809 48.748332,7.029776 48.748321,7.030928 48.74833,7.031769 48.74831,7.032858 48.748234,7.033957 48.748178,7.034374 48.748167,7.035889 48.748046,7.036188 48.748046,7.035695 48.749082,7.035187 48.750045,7.03494 48.750618,7.034834 48.751079,7.034736 48.751652,7.034621 48.752149,7.034501 48.752348,7.034483 48.753322,7.034494 48.753913,7.034536 48.754734,7.03466 48.755383,7.03526 48.757554,7.035435 48.757604,7.035409 48.758099,7.035646 48.75845,7.038539 48.759407,7.039313 48.759692,7.039928 48.759862,7.04096 48.760265,7.042823 48.760164,7.044805 48.759973,7.045683 48.759797,7.047676 48.759352,7.051001 48.75867,7.054609 48.758581,7.054945 48.758677,7.055343 48.758648,7.056354 48.758693,7.057312 48.758778,7.057988 48.75882,7.060404 48.758818,7.063383 48.758644,7.064439 48.758469,7.067456 48.757672,7.069758 48.75788,7.070067 48.757823,7.070369 48.757821,7.07079 48.757684,7.071155 48.75764,7.071243 48.757659,7.07136 48.757544,7.071687 48.757378,7.072 48.75748,7.072225 48.757321,7.072339 48.757299,7.072816 48.757319,7.072917 48.757289,7.073148 48.757099,7.073585 48.75716,7.073874 48.757049,7.074064 48.756905,7.073753 48.756807,7.073833 48.756603,7.0738 48.756435,7.073683 48.756245,7.073655 48.755995,7.073377 48.755814,7.073281 48.75565,7.073247 48.755407,7.073415 48.755349,7.073451 48.755155,7.073297 48.755092,7.073048 48.755091,7.072863 48.755146,7.072765 48.754996,7.072879 48.754826,7.072962 48.754613,7.072902 48.754302,7.072983 48.754157,7.072961 48.754042,7.072615 48.753878,7.072735 48.753775,7.07302 48.75372,7.072754 48.753522,7.07277 48.753462,7.073281 48.753285,7.073558 48.753354,7.073702 48.753339,7.073855 48.75318,7.074214 48.753211,7.074543 48.753172,7.074808 48.753244,7.075099 48.752961,7.07514 48.752861,7.075116 48.752603,7.074885 48.752449,7.074876 48.752372,7.074989 48.752237,7.075504 48.751974,7.07571 48.751661,7.076034 48.751243,7.076303 48.750802,7.076482 48.75061,7.075924 48.750085,7.076114 48.749938,7.075916 48.749837,7.076069 48.749693,7.076153 48.74935,7.07659 48.749424,7.076806 48.749304,7.077013 48.749266,7.07705 48.749415,7.077224 48.749474,7.077308 48.749353,7.077225 48.749244,7.077335 48.749187,7.077619 48.749164,7.077748 48.749122,7.078019 48.749106,7.078617 48.748975,7.07895 48.749062,7.079301 48.748748,7.079114 48.748572,7.079943 48.748141,7.080067 48.748142,7.080293 48.747901,7.080482 48.74779,7.080743 48.747701,7.08097 48.747666,7.081229 48.747667,7.081525 48.747588,7.081541 48.747497,7.081466 48.74735,7.08127 48.747108,7.081243 48.746943,7.081327 48.746741,7.081554 48.746509,7.08168 48.746339,7.081695 48.74603,7.081895 48.745818,7.081837 48.745578,7.081737 48.74546,7.081927 48.745429,7.082162 48.745449,7.082434 48.745556,7.082438 48.745662,7.082566 48.745921,7.082697 48.745976,7.082966 48.745982,7.083036 48.745873,7.082915 48.745747,7.082946 48.74557,7.083155 48.745564,7.083362 48.745619,7.083474 48.745709,7.083854 48.7458,7.083977 48.745786,7.084079 48.745624,7.083958 48.745504,7.083582 48.745385,7.083431 48.745281,7.083382 48.745181,7.083452 48.745029,7.083671 48.744906,7.083917 48.744875,7.08419 48.744917,7.084343 48.74489,7.084597 48.744777,7.084766 48.744548,7.084774 48.744222,7.084897 48.743961,7.085227 48.743517,7.085363 48.743252,7.085453 48.742924,7.085469 48.742578,7.085443 48.742195,7.08534 48.741786,7.085112 48.741438,7.084814 48.741112,7.084467 48.740897,7.084069 48.740783,7.083982 48.740716,7.083414 48.740714,7.083036 48.740573,7.082852 48.74058,7.082581 48.740645,7.082311 48.740661,7.082096 48.74057,7.082067 48.740502,7.082142 48.740188,7.082133 48.740085,7.081795 48.739596,7.081673 48.739209,7.081673 48.73912,7.081777 48.738983,7.081584 48.73889,7.081289 48.739034,7.081129 48.739022,7.081034 48.738925,7.080968 48.738685,7.081165 48.738322,7.081386 48.738303,7.081344 48.738186,7.081124 48.738065,7.080961 48.738018,7.080969 48.73781,7.081158 48.737753,7.081294 48.737579,7.081241 48.737496,7.080852 48.737534,7.08076 48.737454,7.080741 48.73732,7.080617 48.737245,7.080413 48.737333,7.080241 48.737342,7.080063 48.737245,7.079225 48.737129,7.077899 48.73809,7.077747 48.738051,7.076488 48.738266,7.075827 48.737763,7.075633 48.73625,7.075347 48.734602,7.07377 48.732474,7.073625 48.732445,7.073313 48.73286,7.072835 48.732788,7.072482 48.732756,7.07231 48.732711,7.072082 48.732573,7.071762 48.732425,7.070932 48.732155,7.070662 48.731929,7.070176 48.731609,7.069779 48.731483,7.069467 48.731306,7.069173 48.73106,7.069115 48.73091,7.068656 48.730725,7.068423 48.730579,7.068178 48.730478,7.06799 48.730348,7.066797 48.729805,7.066076 48.730089,7.065825 48.730382,7.065475 48.730605,7.064985 48.730816,7.063846 48.731256,7.063483 48.731386,7.063388 48.73132,7.062989 48.730785,7.062433 48.730895,7.062019 48.730334,7.06162 48.729688,7.061277 48.729779,7.060982 48.729739,7.060807 48.729581,7.060683 48.729416,7.060365 48.728867,7.060045 48.728379,7.059789 48.728076,7.059567 48.727868,7.059439 48.727792,7.059372 48.727635,7.058831 48.727221,7.0584 48.726955,7.058051 48.726786,7.057751 48.7266,7.057296 48.726269,7.057102 48.726158,7.05684 48.726059,7.056405 48.725989,7.055797 48.725732,7.054506 48.725259,7.054219 48.725124,7.054068 48.725094,7.0537 48.724752,7.053326 48.724722,7.05284 48.724635,7.052527 48.724502,7.051709 48.723811,7.051263 48.723238,7.050945 48.722762,7.050513 48.721599,7.050588 48.721435,7.050493 48.721326,7.049272 48.720771,7.049657 48.720517,7.049992 48.720207,7.050134 48.720002,7.050231 48.719774,7.050304 48.719496,7.05048 48.719183,7.050346 48.719136,7.050488 48.718953,7.05033 48.718846,7.050345 48.718687,7.050292 48.718565,7.05006 48.718393,7.049725 48.718043,7.04961 48.717986,7.049034 48.717837,7.049166 48.716998,7.049208 48.716822,7.048476 48.716556,7.048269 48.716348,7.048227 48.716142,7.047034 48.715095,7.046074 48.714488,7.045054 48.712563,7.043383 48.711123,7.043608 48.711069,7.044116 48.710999,7.044266 48.710948,7.044476 48.710785,7.044592 48.710628,7.04468 48.710428,7.044959 48.709138,7.045017 48.709038,7.04473 48.708152,7.043811 48.708067,7.043277 48.707966,7.042181 48.707554,7.04097 48.706954,7.040432 48.706665,7.039615 48.706439,7.037694 48.705755,7.037397 48.705587,7.036246 48.707021,7.03594 48.707393,7.0347 48.70913,7.033825 48.710853,7.032426 48.713377,7.0321 48.714015,7.032236 48.715748,7.032398 48.717297,7.032414 48.718766,7.031725 48.719715,7.030613 48.720947,7.029658 48.722063,7.029098 48.722803,7.028081 48.724069,7.027986 48.724233,7.027177 48.725103,7.026583 48.724974,7.026459 48.725365,7.026516 48.725577,7.026693 48.725692,7.025065 48.727231,7.024397 48.727789,7.023694 48.728488,7.022605 48.729252,7.02117 48.729896,7.019868 48.730193,7.018577 48.73087,7.017656 48.730807)))', 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
  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'57630'') = ?
  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 = '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
  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 = '57630';

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