The overall goal that I am attempting to achieve is to join two tables together based on a point (created from xy coordinates in feet) from one table falling within a polygon from another table. The expected result is given records and the name of the polygon it falls within. When the query is executed, the below error is returned, in summary:
A .NET Framework error occurred during execution of user-defined routine or aggregate "geometry": System.FormatException: 24114: The label 395 in the input well-known text (WKT) is not valid.<
Due to not being familiar with geometry data in SQL, I am not fully certain whether I am even on the right path, so any advice would be appreciated.
The polygon table that I am attempting to join is a temporary table and the polygons are formatted as geometry. Running the below code in isolation executes as expected by creating a spatial output.
My apologies, it appears code formatting is not working on my phone and my work computer’s browser is not supported.
DECLARE @Hex1 TABLE (PolyGeom geometry, Hex varchar(6))
INSERT INTO @Hex1
Values
(geometry::STPolyFromText('Polygon((7598795.05553838 734372.656,7598217.70526919 735372.656,7597063.00473081 735372.656,7596485.65446162 734372.656,7597063.00473081 733372.656,7598217.70526919 733372.656,7598795.05553838 734372.656))',0),1),
(geometry::STPolyFromText('Polygon((7602259.15715352 734372.656,7601681.80688433 735372.656,7600527.10634595 735372.656,7599949.75607676 734372.656,7600527.10634595 733372.656,7601681.80688433 733372.656,7602259.15715352 734372.656))',0),2),
(geometry::STPolyFromText('Polygon((7605723.25876865 734372.656,7605145.90849947 735372.656,7603991.20796109 735372.656,7603413.8576919 734372.656,7603991.20796109 733372.656,7605145.90849947 733372.656,7605723.25876865 734372.656))',0),3)’’’
The table that the polygon table is joined to does not have points so have created a table that has a field with the calculated points. Running the below code in isolation works as expected by returning records with a point.
INSERT INTO #Points (Primary_Key, geom)
select a.rID, geometry::STGeomFromText('POINT('+convert(varchar(20),a.x_coordinate)+' '+convert(varchar(20),a.y_coordinate)+')',0) as geom
from data_a a'''
The tables are joined as shown below
WITH CTE1 AS --Due the number of polygons exceeding insert limits, multiple tables are created and unioned in a CTE
( Select*
From @Hex1
UNION ALL
Select*
From @Hex2
UNION ALL
Select*
From @Hex3
UNION ALL
Select*
From @Hex4)
select a.rID, C.Hex
from data_a a --Existing table with x y coordinates
left join #points p --Joins the point created in points table to the same case in go_data
on a.rID = p.Primary_key
left join CTE1 C --Joins the hexagon to the point if hex containts the point
on p.geom.STIntersects (c.hex) =1'''
Below is the full code, with the number of polygons in each table trimmed down
IF OBJECT_ID('tempdb..#Points') IS NOT NULL DROP TABLE #Points
create table #Points (Primary_key numeric identity not null, geom geometry)
SET IDENTITY_INSERT #Points ON
INSERT INTO #Points (Primary_Key, geom)
select g.rin, geometry::STGeomFromText('POINT('+convert(varchar(20),a.x_coordinate)+' '+convert(varchar(20),a.y_coordinate)+')',0) as geom
from data_a a
;
DECLARE @Hex1 TABLE
(PolyGeom geometry, Hex varchar(6))
INSERT INTO @Hex1
Values
(geometry::STPolyFromText('Polygon((7598795.05553838 734372.656,7598217.70526919 735372.656,7597063.00473081 735372.656,7596485.65446162 734372.656,7597063.00473081 733372.656,7598217.70526919 733372.656,7598795.05553838 734372.656))',0),1),
(geometry::STPolyFromText('Polygon((7602259.15715352 734372.656,7601681.80688433 735372.656,7600527.10634595 735372.656,7599949.75607676 734372.656,7600527.10634595 733372.656,7601681.80688433 733372.656,7602259.15715352 734372.656))',0),2),
(geometry::STPolyFromText('Polygon((7605723.25876865 734372.656,7605145.90849947 735372.656,7603991.20796109 735372.656,7603413.8576919 734372.656,7603991.20796109 733372.656,7605145.90849947 733372.656,7605723.25876865 734372.656))',0),3)
DECLARE @Hex2 TABLE
(PolyGeom geometry, Hex varchar(6))
INSERT INTO @Hex2
Values
(geometry::STPolyFromText('Polygon((7680201.44349411 721372.656,7679624.09322492 722372.656,7678469.39268654 722372.656,7677892.04241735 721372.656,7678469.39268654 720372.656,7679624.09322492 720372.656,7680201.44349411 721372.656))',0),1000),
(geometry::STPolyFromText('Polygon((7683665.54510925 721372.656,7683088.19484006 722372.656,7681933.49430168 722372.656,7681356.14403249 721372.656,7681933.49430168 720372.656,7683088.19484006 720372.656,7683665.54510925 721372.656))',0),1001),
(geometry::STPolyFromText('Polygon((7687129.64672438 721372.656,7686552.29645519 722372.656,7685397.59591681 722372.656,7684820.24564763 721372.656,7685397.59591681 720372.656,7686552.29645519 720372.656,7687129.64672438 721372.656))',0),1002)
DECLARE @Hex3 TABLE
(PolyGeom geometry, Hex varchar(6))
INSERT INTO @Hex3
Values
(geometry::STPolyFromText('Polygon((7765071.93306498 708372.656,7764494.58279579 709372.656,7763339.88225741 709372.656,7762762.53198822 708372.656,7763339.88225741 707372.656,7764494.58279579 707372.656,7765071.93306498 708372.656))',0),1999),
(geometry::STPolyFromText('Polygon((7768536.03468011 708372.656,7767958.68441092 709372.656,7766803.98387254 709372.656,7766226.63360335 708372.656,7766803.98387254 707372.656,7767958.68441092 707372.656,7768536.03468011 708372.656))',0),2000),
(geometry::STPolyFromText('Polygon((7772000.13629525 708372.656,7771422.78602606 709372.656,7770268.08548768 709372.656,7769690.73521849 708372.656,7770268.08548768 707372.656,7771422.78602606 707372.656,7772000.13629525 708372.656))',0),2001)
WITH CTE1 AS
( Select*
From @Hex1
UNION ALL
Select*
From @Hex2
UNION ALL
Select*
From @Hex3)
select a.rID, C.Hex
from data_a a
left join #points p --Joins the point created in points table to the same case in go_data
on g.rin = p.Primary_key
left join CTE1 C --Joins the hexagon to the point if hex containts the point
on p.geom.STIntersects (c.hex) =1
