Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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

This comment has been minimized.

Copy link
Owner Author

@rustprooflabs rustprooflabs commented Apr 27, 2020

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'
;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.