-- 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 |