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