May 21, 2020 at 10:33 pm
Hello,
I have a spatial query that pulls in the points of a polygon from a table, then pulls in trip data (picks and drops) and compares if either pick or drop is inside the polygon using STIntersects. Everything along the way checks out, as far as whether the data is valid or not, but the final query to Intersect just errs out with:
Msg 6522, Level 16, State 1, Line 147
A .NET Framework error occurred during execution of user-defined routine or aggregate "geometry":
System.ArgumentException: 24144: This operation cannot be completed because the instance is not valid. Use MakeValid to convert the instance to a valid instance. Note that MakeValid may cause the points of a geometry instance to shift slightly.
System.ArgumentException:
at Microsoft.SqlServer.Types.SqlGeometry.ThrowIfInvalid()
at Microsoft.SqlServer.Types.SqlGeometry.STIntersects(SqlGeometry other)
The query I used is as follows:
/* Create temp table for trip addresses */
CREATE TABLE #Points (
LDate INT
,BookingID INT
,PGeoLocation Geometry
,PLat Float
,PLon Float
,DGeoLocation Geometry
,DLat Float
,DLon Float
)
INSERT INTO #Points
SELECT S.LDate
,b.BookingID
,NULL PGeoLocation
,CAST(bl1.lat AS FLOAT)/1000000 PLat
,CAST(bl1.lon AS FLOAT)/1000000 PLon
,NULL DGeoLocation
,CAST(bl2.lat AS FLOAT)/1000000 DLat
,CAST(bl2.lon AS FLOAT)/1000000 DLon
FROM ...[tables]
WHERE s.LDate BETWEEN @SLDate AND @ELDate
AND CAST(bl1.LON AS FLOAT)/1000000 <> 99.999999
AND CAST(bl2.LON AS FLOAT)/1000000 <> 99.999999
--select * from #points
/* Convert addresses Lats and Lons to points */
UPDATE #Points
SET PGeoLocation = geometry::STGeomFromText('POINT(' + CAST(PLat AS VARCHAR(20)) + ' ' +
CAST(Plon AS VARCHAR(20)) + ')', 4326)
UPDATE #Points
SET DGeoLocation = geometry::STGeomFromText('POINT(' + CAST(DLat AS VARCHAR(20)) + ' ' +
CAST(Dlon AS VARCHAR(20)) + ')', 4326)
UPDATE #Points
SET PGeoLocation = PGeoLocation.MakeValid() WHERE PGeoLocation.STIsValid() = 0;
UPDATE #Points
SET DGeoLocation = DGeoLocation.MakeValid() WHERE DGeoLocation.STIsValid() = 0;
/* Create the Polygon out of the Polygon Lats and Lons */
DECLARE @Polygon1 INT = 7146
DECLARE @BuildString NVARCHAR(MAX)
SELECT @BuildString = COALESCE(@BuildString + ',', '') + CAST(CAST(lat AS FLOAT)/1000000 AS NVARCHAR(50)) + ' ' + CAST(CAST(lon AS FLOAT)/1000000 AS NVARCHAR(50))
FROM mmsdata1.dbo.PolygonVerts
WHERE polyid = @Polygon1
ORDER BY vertSequence DESC
SET @BuildString = 'POLYGON((' + @BuildString + '))'
DECLARE @PolygonFromPoints geometry = geometry::STPolyFromText(@BuildString, 4326)
/*pull all data with geolocation whether address is in or out of poly*/
SELECT *
,CASE WHEN @PolygonFromPoints.STIntersects(PGeoLocation) = 1 THEN 'Y' ELSE 'N' END AS P_InPolygon1
,CASE WHEN @PolygonFromPoints.STIntersects(DGeoLocation) = 1 THEN 'Y' ELSE 'N' END AS D_InPolygon1
FROM #points
Just looking at the code, is there anything I'm missing here? I've been googling and the only thing I can find even closely related to what I'm seeing is that there may be a problem with the actual polygon itself. Maybe bad data in that table? Maybe it was drawn poorly? But, if that is the case here, how do I get around that? I don't think redrawing the polygon is a viable answer here so I think I'm stuck with the data I have.
Or could it be some other issue?
Any thoughts?
May 22, 2020 at 11:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply