Postgres 9.6

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE location (
  id integer PRIMARY KEY,
  name text,
  geolocation text NULL,
  parent_id integer NULL REFERENCES location(id)
);
INSERT INTO location (
    id, name, geolocation, parent_id
  )
  VALUES
    (1, 'aaaa', '0.0,0.0', NULL),
    (2, 'bbbb', '1.1,1.1', 1),
    (3, 'cccc', NULL, 1),
    (4, 'dddd', '2.2,2.2', NULL ),
    (5, 'eeee', NULL, NULL),
    (6, 'ffff', NULL, 3);
6 rows affected

WITH RECURSIVE
  r AS (
      SELECT id, name, geolocation
      FROM location
      WHERE parent_id IS NULL
    UNION ALL
      SELECT
        l.id,
        l.name,
        COALESCE(l.geolocation,
                 r.geolocation)
      FROM r
      JOIN location AS l
        ON r.id = l.parent_id
  )
SELECT * FROM r;


id
name
geolocation


1
aaaa
0.0,0.0

4
dddd
2.2,2.2

5
eeee
null

2
bbbb
1.1,1.1

3
cccc
0.0,0.0

6
ffff
0.0,0.0

id	name	geolocation
1	aaaa	0.0,0.0
4	dddd	2.2,2.2
5	eeee	null
2	bbbb	1.1,1.1
3	cccc	0.0,0.0
6	ffff	0.0,0.0