Skip to content

Instantly share code, notes, and snippets.

@jtroe

jtroe/FIPS.sql

Last active October 14, 2015 20:47
Show Gist options
  • Save jtroe/a8e8528520b8d6f346b6 to your computer and use it in GitHub Desktop.
Save jtroe/a8e8528520b8d6f346b6 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. Jason Roebuck revised this gist Oct 14, 2015. 1 changed file with 0 additions and 0 deletions.
    Binary file added counties.sql
    View file
    Binary file not shown.
  2. jtroe revised this gist Oct 14, 2015. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion FIPS.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -36,7 +36,7 @@ BEGIN
    CNTY_FIPS,
    FIPS,
    SHAPE
    from United_States.dbo.Counties
    from dbo.Counties
    where Shape.STIntersects(@POINT) = 1
    RETURN
    END
  3. jtroe revised this gist Oct 13, 2015. No changes.
  4. jtroe revised this gist Oct 13, 2015. No changes.
  5. jtroe created this gist Oct 13, 2015.
    44 changes: 44 additions & 0 deletions FIPS.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,44 @@
    /*
    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