Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment