Skip to content

Instantly share code, notes, and snippets.

@TheBryanMac
Created September 12, 2014 20:46
Show Gist options
  • Save TheBryanMac/6c4de4888f0a0c438958 to your computer and use it in GitHub Desktop.
Save TheBryanMac/6c4de4888f0a0c438958 to your computer and use it in GitHub Desktop.
SQL Procedure with STIntersection
-- Name: SQL Server Procedure with STIntersection
-- Author: Bryan McIntosh
-- Description: Populated data table from SQL Server Spatial query
CREATE PROCEDURE [dbo].[prc_NoaaDataLoad]
@dt datetime, --DateTime from NOAA header info
@val float, --Value of weather data (mm of rain, temperature in F/C, etc)
@wType varchar(20), -- Type of data stored (precipitation, temperature, etc)
@wkt varchar(8000) --polygon shape (geography type)
AS
BEGIN
SET NOCOUNT ON; --added to prevent extra result sets from interfering with SELECT statements.
DECLARE @polygeo GEOGRAPHY;
SET @polygeo = GEOGRAPHY::STGeomFromText(@wkt,4269); --4269 is NAD83 GCS
INSERT INTO tbl_NoaaData (PointID, LogDate, DataValue)
SELECT POINTID, @dt, @val, @wType
FROM tbl_ReferencePointsGCS
WHERE tbl_ReferencePointsGCS.shape.STIntersects(@polygeo) = 1
END
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment