CREATE OR REPLACE FORCE VIEW "PIRSA"."GSML_MAPPEDFEATURE_SIMPLE" ("SITE_ID", "GML_NAME", "DESCRIPTION", "OBSERVATION_METHOD", "OBSERVATION_METHOD_CODESPACE", "SPECIFICATION_URN", "LOCATION", "POS_ACC", "POS_ACC_UOM") AS 
SELECT
  s.site_no,
  'urn:cgi:feature:PIRSA:MappedFeature:' || s.site_no AS GML_NAME,
  s.comments,
  CASE WHEN svym.svy_method_desc IS NULL THEN 'urn:ogc:def:nil:OGC::missing' ELSE svym.svy_method_desc END AS OBSERVATION_METHOD,
  CASE WHEN svym.svy_method_desc IS NULL THEN 'http://urn.opengis.net/' ELSE 'urn:cgi:classifierScheme:PIRSA:ObservationMethod' END AS OBSERVATION_METHOD_CODESPACE,
  CASE
    WHEN md.mineral_deposit_no IS NOT NULL THEN 'urn:cgi:feature:PIRSA:MineralOccurrence:' || md.mineral_deposit_no || 'deposit'
    ELSE 'urn:cgi:feature:PIRSA:MineralOccurrence:' || mz.zone_no || 'zone'
  END AS SPECIFICATION_URN,
  s.geodetic_point AS LOCATION,
  CASE WHEN s.svy_accrcy_horiz >= 0 THEN to_char(s.svy_accrcy_horiz) ELSE 'urn:ogc:def:nil:OGC::missing' END AS POS_ACC,
  CASE WHEN s.svy_accrcy_horiz >= 0 THEN 'urn:ogc:def:uom:UCUM::m' ELSE 'http://urn.opengis.net/' END AS POS_ACC_UOM
FROM SITE s
  LEFT OUTER JOIN DD_SVY_METHOD svym ON svym.svy_method_code = s.svy_method_horiz_code
  LEFT OUTER JOIN MD_MINERAL_DEPOSIT md ON md.site_no = s.site_no
  LEFT OUTER JOIN MD_MINERALISED_ZONE mz ON mz.site_no = s.site_no
WHERE
  COALESCE(md.mineral_deposit_no, mz.zone_no) IS NOT NULL;