Skip to content

Instantly share code, notes, and snippets.

@rustprooflabs
Last active May 18, 2021 17:32
Show Gist options
  • Star 2 You must be signed in to star a gist
  • Fork 1 You must be signed in to fork a gist
  • Save rustprooflabs/a86e3ff5c829b0fa32ebbd5702883ebe to your computer and use it in GitHub Desktop.
Save rustprooflabs/a86e3ff5c829b0fa32ebbd5702883ebe to your computer and use it in GitHub Desktop.
Creates view in PostGIS enabled database to make it easier to find what units each SRID is in.
CREATE OR REPLACE VIEW public.srid_units AS
SELECT srid, CASE WHEN proj4text LIKE '%+units=%' THEN True
ELSE False
END AS units_set,
CASE WHEN proj4text LIKE '%+units=m%' THEN 'Meters'
WHEN proj4text LIKE '%+units=ft%' THEN 'Feet'
WHEN proj4text LIKE '%+units=us-ft%' THEN 'Feet'
WHEN proj4text LIKE '%+units=link%'
OR proj4text LIKE '%+units=%'
THEN 'Set, not caught properly'
ELSE 'Decimal Degrees'
END AS units,
proj4text, srtext
FROM public.spatial_ref_sys
;
COMMENT ON VIEW public.srid_units IS 'PostGIS specific view to make it easier to find what units each SRID is in. From https://gist.github.com/rustprooflabs/a86e3ff5c829b0fa32ebbd5702883ebe';
@rustprooflabs
Copy link
Author

Basic usage example. Find active SRIDs in Colorado with units in Meters.

SELECT *
	FROM public.srid_units
	WHERE srtext ILIKE '%Colorado%'
		AND srtext NOT ILIKE '%deprecated%'
		AND units = 'Meters'
;

@rustprooflabs
Copy link
Author

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment