Finding county to a road using STContains

Question

Running SQL Server 2014 Standard

I have about 1 million linestring (represent roads) and 98 polygons (counties). I want to update what county the road belongs to. My query:

UPDATE RAW_HERE
SET COUNTY = RAW_HERE_COUNTY.POLYGON_NM
FROM [dbo].[RAW_HERE_PROCESSED_SINGLE] AS RAW_HERE
INNER JOIN [RAW_HERE_D91_COUNTY] as RAW_HERE_COUNTY ON (RAW_HERE_COUNTY.[Shape].STContains(RAW_HERE.[Shape]) = 1)

This query has been running for 3 hours and counting, and I have verified it is using the spatial index on RAW_HERE_D91_COUNTY

RAW_HERE_PROCESSED_SINGLE contains the linestrings (their geometry column is [shape]) RAW_HERE_D91_COUNTY contains the polygon counties their geometry column is also [shape].

Is there an faster way to do this? Am I missing something?

Answer 1

It takes only 5 minute to make a primary key to county code lookup, so joining on county rather than joining county on roads seems the way to go

CREATE TABLE [dbo].[#OBJECTCOUNTY](
    OBJECTID int PRIMARY KEY,
    [County] [nvarchar](105) NOT NULL
) ON [PRIMARY] 

INSERT INTO [#OBJECTCOUNTY]
SELECT RAW_HERE.OBJECTID, RAW_HERE_COUNTY.POLYGON_NM as County
FROM [RAW_HERE_D93_COUNTY] as RAW_HERE_COUNTY
LEFT JOIN [RAW_HERE_D93] as RAW_HERE ON (RAW_HERE_COUNTY.[Shape].STContains(RAW_HERE.[Shape]) = 1)
WHERE RAW_HERE.[Shape] IS NOT NULL

Then just update based on the table.