special-forms.plsql

-- maybe our dataset has a column that looks like '(47.000, -128.000)' but some of those values
-- are 'unknown', so this will parse the lat/lng from the string if it's there, and if not, then
-- geocode using different columns, but both branches of this case return a point.
case(
 location LIKE '%(%' and location LIKE '%)%' and location LIKE '%,%',
 make_point(
    to_number(
      regex_named_capture(incident_location, '\((?<latitude>[-\d\.]+)', 'latitude')
    ),
    to_number(
      regex_named_capture(incident_location, '(?<longitude>[-\d\.]+)\)', 'longitude')
    )
  ),
  true,
  geocode(address, city, state, zip)
)