STIntersect says every point is intersecting every zone - need to confirm

Question

I am implementing a Vehicle Safety program for an industry group. The group have provided 3k+ geozones that cover sites that are members of the group. The file provided was a shapefile. My colleague extracted it and converted it to 4326 and uploaded it into a new database. I checked the zones using IsValidDetailed(). 7 were invalid, but righted themselves when MakeValid() was run on them. The geography table SaferTogether has had a spatial index created, just using auto.

I was initially testing using the following. This would run every point against every zone, and I got results after just over an hour.

INSERT INTO @Intersects (LogId, ogr_fid, NodeId)
SELECT p.LogId, s.ogr_fid, p.NodeId
FROM LogPos p,
     [gis]..safertogether as s
WHERE p.MyPoint.STIntersects(s.geog4326) = 1
and AssembledTime between @PeriodStart and @PeriodEnd

I'm now testing a single trip of 27 log points, and I've noticed I have 81162 results This roughly matches the 27 points x 3k+ zones. I don't believe that every zone overlays every point. The code below is the testing for one trip worth of logs. It is not efficient.

UPDATE #Logs
SET IsIntersect = 0

SET @Message = 'Detecting Log intersects with Zone ' + CONVERT(VARCHAR(10), @GeographyId) + ':' + @GeographyName
EXEC usp_Log @ProcessName, @Message, @SessionName, @LogInfo, @Section

UPDATE #Logs
SET IsIntersect = 1
WHERE @Geography.STIntersects(MyPoint) = 1

SELECT @rc = COUNT(1)
FROM #Logs WHERE IsIntersect = 1

SET @Message = 'Trip ' + CONVERT(VARCHAR(10), @TripLogId) + ' intersecting points with ' + CONVERT(VARCHAR(10), @GeographyId) + ':' + @GeographyName + ': ' + CONVERT(varchar(10), @rc)
EXEC usp_Log @ProcessName, @Message, @SessionName, @LogDebug, @Section

SET @Message = 'Save intersects with ' + CONVERT(VARCHAR(10), @GeographyId) + ':' + @GeographyName
EXEC usp_Log @ProcessName, @Message, @SessionName, @LogInfo, @Section

INSERT INTO @Intersects (LogId, NodeId, ogr_fid)
SELECT 
    LogId,
    NodeId,
    @GeographyId
FROM #Logs
WHERE IsIntersect = 1

What I'm looking for here a way to visualise the zones vs the points so I can tell if they are broken or not. I don't know how to compare fields in the built in spatial results tab.

Answer 1

It is likely the problem is in conversion of shapefile (planar map) data to SQL Server Geography (spherical) model. These are easy to do wrong and get inverted (complimentary) polygons, i.e. polygons describing all Earth area except the intended shape.

Also see couple similar cases, they were with Google BigQuery, which uses similar semantics of spherical oriented polygons:

• Query times out after 6 hours, how to optimize it?
• How to get the correct centroid of a bigquery polygon with st_centroid

You can check if the polygons were ingested correctly to the database by computing their area - it will be huge (order of 5e14 m^2) for inverted polygons, whereas you should get reasonable result for correctly ingested ones.

SQL Server has ReorientObject() method that reverses the polygon orientation. Be careful with it though, as orientation interacts with validity - so it is better to use ReorientObject() before MakeValid(). Best of all of course is to fix the ingestion method.

Alternatively, you can use SQL Server Geometry (rather than Geography) type that uses planar map semantics. Working with planar data has some issues, but assuming you don't cross anti-meridian, and have basic understanding of which projections are good for which kind of computation, you should typically be OK.

Or switch to a database that uses spherical mode, but can interpret and convert planar data correctly. E.g. BigQuery understands that GeoJson geometries are planar, and ignores their orientation when converting them to the internal spherical Geography model, so you never have this problem if you load Geometry as GeoJson fields.