Intersecting many Points with many Polygons

Question

I have two tables in SQLServer 2014, one with ~100M points and one with ~2000 polygons. Each point intersects with only one of the polygons. The task is to assign the ID of the intersecting polygon to the point.

What is the best practice to do it?

I have tried it in C#, loading two datatables, going row by row through the points and row by row through the polygons to find matches.

Boolean inside = (Boolean)polygon.STIntersects(point);

This is painfully slow since i have to access each point separately and each polygon multiple times to check for intersection. Any ideas are very welcome!

Create table statement for Points

CREATE TABLE [dbo].[ManyPoints](
     [idNearByTimeLine] [int] IDENTITY(1,1) NOT NULL,
     [msgID] [bigint] NOT NULL,
     [userID] [bigint] NULL,
     [createdAT] [datetime2](0) NULL,
     [WGSLatitudeX] [numeric](9, 6) NULL,
     [WGSLongitudeY] [numeric](9, 6) NULL,
     [location] [geography] NULL
)

and Polygons

CREATE TABLE [dbo].[ManyPolygons](
     [OBJECTID] [int] IDENTITY(1,1) NOT NULL,
     [Shape] [geography] NULL,
     [ID_0] [int] NULL,
     [ISO] [nvarchar](3) NULL,
     [NAME_0] [nvarchar](75) NULL,
     [ID_1] [int] NULL,
     [NAME_1] [nvarchar](75) NULL,
     [ID_2] [int] NULL,
     [NAME_2] [nvarchar](75) NULL,
     [ID_3] [int] NULL,
     [NAME_3] [nvarchar](75) NULL,
     [NL_NAME_3] [nvarchar](75) NULL,
     [VARNAME_3] [nvarchar](100) NULL,
     [TYPE_3] [nvarchar](50) NULL,
     [ENGTYPE_3] [nvarchar](50) NULL,
     [ORIG_FID] [int] NULL,
)

Both tables have a spatial index on "location" and "Shape"

Answer 1

I came up with another solution. This is a stored procedure which selects all points within a given polygon ID. Then I use a simple C# program to loop through all polygons. However this is still not optimal and painfully slow. Any tweaks that can be made easily?

USE [<<DATABASE>>]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[test] @ID INT 
AS 

SET IDENTITY_INSERT [weiboDEV].[dbo].[<<NEW TABLE>>] ON;
-- Select Points in Polygon (Geography)

DECLARE @Shape GEOGRAPHY = (select [Shape] from <<POLYGONS>> where OBJECTID=@ID);
DECLARE @SQLString2 NVARCHAR(500)= N'INSERT INTO <<NEW TABLE>>(<<YOUR COLUMNS>>) SELECT <<YOUR COLUMNS>> FROM <<POINTS>> WHERE ([location]).STWithin(@Shape) = 1;';
DECLARE @ParmDefinition NVARCHAR(500) = N'@ID INT, @Shape geography';
EXECUTE sp_executesql @SQLString2, @ParmDefinition, @ID, @Shape;

GO

Answer 2

Select idnearbytimeline, objectid
From dbo.manypoints as point
Join dbo.manypolygons as polygon
   On point.location.STIntersects(polygon.shape) =1

Answer 3

I suggest you to store your points of single polygon with comma separated string. So it can be covered in only one record for each polygon.