geopoldepth

WITH RECURSIVE gpid_path AS (

         SELECT gp_1.geopoliticalid,

            (gp_1.geopoliticalid)::text AS gpid

           FROM ndb.geopoliticalunits gp_1

          WHERE (gp_1.rank = 1)

        UNION ALL

         SELECT gpu_1.geopoliticalid,

            concat(gpa.gpid, ',', gpu_1.geopoliticalid) AS concat

           FROM (ndb.geopoliticalunits gpu_1

             JOIN gpid_path gpa ON ((gpa.geopoliticalid = gpu_1.highergeopoliticalid)))

        )

 SELECT gp.geopoliticalid,

    gpu.geopoliticalname,

    gpu.geopoliticalunit,

    gpu.rank,

    (string_to_array(gp.gpid, ','::text))::integer[] AS path

   FROM (gpid_path gp

     JOIN ndb.geopoliticalunits gpu ON ((gpu.geopoliticalid = gp.geopoliticalid)))

  ORDER BY gp.geopoliticalid;