WITH LocationSubgraph AS (
  SELECT 'md:' || g1.subject as subject, g1.predicate, g1.object, g1.relationshipPredicate, g1.relationshipId
  FROM ${entityCentricSubgraph} g1
  JOIN ${entityCentricSubgraph} g2 on g1.subject = g2.subject
  WHERE g2.predicate = 'PS1' AND g2.object = 'SB152'
    AND g1.subject IN ($SUBJECT_IDS)
), LocationSubjects AS (
  SELECT DISTINCT subject FROM LocationSubgraph
), LocationName AS (
  SELECT subject, object as name
  FROM LocationSubgraph
  WHERE predicate = 'PS33'
), LocationAddressString AS (
  SELECT subject, object as addressString
  FROM LocationSubgraph
  WHERE predicate = 'nm_addressString'
), LocationLatitude AS (
  SELECT l1.subject, l2.object as latitude
  FROM LocationSubgraph l1 JOIN LocationSubgraph l2 ON l1.relationshipId = l2.relationshipId
  WHERE l1.predicate = 'PS573' AND l1.relationshipPredicate = 'PS1' AND l1.object = 'CS29'
    AND l2.relationshipPredicate = 'PS571'
), LocationLongitude AS (
  SELECT l1.subject, l2.object as longitude
  FROM LocationSubgraph l1 JOIN LocationSubgraph l2 ON l1.relationshipId = l2.relationshipId
  WHERE l1.predicate = 'PS573' AND l1.relationshipPredicate = 'PS1' AND l1.object = 'CS29'
    AND l2.relationshipPredicate = 'PS572' LIMIT 1
), LocationAddress AS (
  SELECT l1.subject, json_group_object(
    CASE WHEN l2.relationshipPredicate = 'PS454' THEN 'isoCountryCode' WHEN l2.relationshipPredicate = 'PS450' THEN 'country' WHEN l2.relationshipPredicate = 'PS315' THEN 'postalCode' WHEN l2.relationshipPredicate = 'PS568' THEN 'administrativeArea' WHEN l2.relationshipPredicate = 'PS569' THEN 'locality' WHEN l2.relationshipPredicate = 'PS570' THEN 'thoroughfare' WHEN l2.relationshipPredicate = 'PS316' THEN 'streetName' WHEN l2.relationshipPredicate = 'PS453' THEN 'city' END,
    l2.object
  ) as address
  FROM LocationSubgraph l1 JOIN LocationSubgraph l2  ON l1.relationshipId = l2.relationshipId
  WHERE l1.predicate = 'PS317' and l1.relationshipPredicate = 'PS1' and l1.object = 'CS97'
  AND l2.relationshipPredicate IN ('PS315', 'PS450', 'PS568', 'PS569', 'PS570', 'PS454', 'PS316', 'PS453')
  GROUP BY l1.subject
), LocationsRepeated AS (
    SELECT s.subject as identifier,
      LocationName.name,
      LocationAddressString.addressString,
      LocationLatitude.latitude,
      LocationLongitude.longitude,
      LocationAddress.address
    FROM LocationSubjects as s
    LEFT JOIN LocationName ON s.subject = LocationName.subject
    LEFT JOIN LocationAddressString ON s.subject = LocationAddressString.subject
    LEFT JOIN LocationLatitude ON s.subject = LocationLatitude.subject
    LEFT JOIN LocationLongitude ON s.subject = LocationLongitude.subject
    LEFT JOIN LocationAddress ON s.subject = LocationAddress.subject
    ORDER BY identifier
), LocationsRanked AS (
    SELECT *,
        ROW_NUMBER() OVER (PARTITION BY identifier) as rn
    FROM LocationsRepeated
)

SELECT
    identifier,
    name,
    addressString,
    latitude,
    longitude,
    address
FROM
    LocationsRanked
WHERE
    rn = 1;