sql queries repository

gistfile1.txt

WITH parcel_ar AS (
  SELECT cadastre.parcel_id, json_agg(json_build_object('ar_id', r.ar_id, 'year', r.year)) as ar_ids FROM cadastre JOIN role_p r on st_intersects(cadastre.wkb_geometry, r.geom) WHERE cadastre.parcel_id = '5618810' GROUP BY parcel_id
)
SELECT row_to_json(t) 
      FROM (
        SELECT
          parcel.parcel_id,
          parcel_ar.ar_ids,
          aq_lim_arrondissements.nomarr as borough,
          aq_lim_municipales.nommun as city,
          aq_lim_municipales.codemun as city_id,
          aq_lim_municipales.nommrc as mrc,
          aq_lim_municipales.codemrc as mrc_id,
          aq_lim_municipales.nomreg as region,
          aq_lim_municipales.codereg as region_id,
          eq_district_electoraux.nm_dis as provincial_electoral_district,
          ST_AsGeoJSON(ST_Transform(parcel.geometry, 4326))::json as geometry
          from parcel
          left join aq_lim_arrondissements on st_intersects(parcel.geometry, aq_lim_arrondissements.wkb_geometry)
          left join aq_lim_municipales on st_intersects(parcel.geometry, aq_lim_municipales.wkb_geometry)
          left join eq_district_electoraux on st_intersects(parcel.geometry, eq_district_electoraux.wkb_geometry)
          left join parcel_ar on parcel_ar.parcel_id = parcel.parcel_id
          where parcel.parcel_id = '5618810'
        ) as t;