Skip to content

Instantly share code, notes, and snippets.

@rozap
Last active February 9, 2018 06:49
Show Gist options
  • Save rozap/4e04e315e705884c3716000fbb6c79b3 to your computer and use it in GitHub Desktop.
Save rozap/4e04e315e705884c3716000fbb6c79b3 to your computer and use it in GitHub Desktop.
-- 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)
)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment