-
-
Save jtroe/85540745fdf5a1d721e9 to your computer and use it in GitHub Desktop.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| /* | |
| Returns County Data for given LATITUDE and LONGITUDE | |
| Example usage: | |
| select a.Latitude, a.Longitude, a.Area, f.NAME, f.FIPS from World.dbo.Locations a --any table with Latitude and Longitude | |
| cross apply dbo.Get_County_From_Lat_Long(a.Latitude, a.Longitude) f | |
| */ | |
| CREATE FUNCTION [dbo].[Get_County_From_Lat_Long] ( | |
| @LATITUDE NUMERIC(10,6), | |
| @LONGITUDE NUMERIC(10,6) | |
| ) | |
| RETURNS @COUNTY_TABLE TABLE ( | |
| NAME NVARCHAR(32), | |
| STATE_NAME NVARCHAR(25), | |
| STATE_FIPS NVARCHAR(2), | |
| CNTY_FIPS NVARCHAR(3), | |
| FIPS NVARCHAR(5), | |
| SHAPE GEOMETRY | |
| ) | |
| as | |
| BEGIN | |
| /* Test Variables -- Morgan County Colorado */ | |
| --DECLARE @LATITUDE NUMERIC(10,6) = 40.4 | |
| --DECLARE @LONGITUDE NUMERIC(10,6) = -103.9 | |
| --DECLARE @FIPS NVARCHAR(5) | |
| DECLARE @POINT GEOMETRY | |
| SET @POINT = geometry::STPointFromText('POINT(' + CAST(@LONGITUDE AS VARCHAR(20)) + ' ' + CAST(@LATITUDE AS VARCHAR(20)) + ')', 4326) | |
| insert into @COUNTY_TABLE | |
| select | |
| NAME, | |
| STATE_NAME, | |
| STATE_FIPS, | |
| CNTY_FIPS, | |
| FIPS, | |
| SHAPE | |
| from United_States.dbo.Counties | |
| where Shape.STIntersects(@POINT) = 1 | |
| RETURN | |
| END | |
| GO |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment