IntersectsCoordinatesAndArea.sql

CREATE view [Model].[vw_GeoIntersects] 
as

WITH Coordinates AS (

	SELECT	
		ID
		, Latitude
		, Longitude
		, geometry::Point(Longitude, Latitude, 0) AS LatLon
	FROM Ref.MarpolCoordinates

), Intersects AS (

	SELECT 
		ID
		, c.Latitude
		, c.Longitude
		, CAST(c.LatLon.STIntersects(a.AreaCoordinates) AS INT) as inArea
	FROM Coordinates c
	CROSS JOIN Ref.MarpolAreas a

)

SELECT	CAST(ID AS VARCHAR(50))	AS ID
		, ''					AS AreaCoordinates
		, 50					AS Size -- required for Power BI visual
		, i.Latitude
		, i.Longitude
		, i.inArea				AS InMarpolArea
FROM Intersects i

UNION ALL

SELECT	ma.MarpolAreaName
		, CAST(ma.AreaCoordinates AS varchar(max))	AS AreaCoordinates
		, 50					AS Size
		, NULL
		, NULL
		, NULL
FROM Ref.MarpolAreas ma