Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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
You can’t perform that action at this time.