Buffering is what came to my mind, too, as that's what I guess is the base of my pattern matching. Lets try something like this to start (i.e. the final UPDATE .. SET [Name] ist still missing and no optimization at all) --SQL OPTIONS COORDSYS("Rivers" AS COMPONENT); SELECT [RID], First([NHD_Name]) AS [NHD_Name], First([rate]), Count(*) AS [touched NHD segments] FROM ( SELECT [R].[ID] AS [RID], Length(ClipIntersect([R].[ID], BUFFER([NHD].[ID],[NHD].[Length (I)]/10)))/[R].[Length (I)] AS [rate], [NHD].[NHD_Name] FROM [Rivers] AS [R], [NHD] WHERE ((Length(ClipIntersect([R].[ID], BUFFER([NHD].[ID],[NHD].[Length (I)]/10)))/[R].[Length (I)])) IS NOT NULL ORDER BY [R].[ID], Length(ClipIntersect([R].[ID], BUFFER([NHD].[ID],[NHD].[Length (I)]/10)))/[R].[Length (I)] DESC ) GROUP BY [RID] - What we have is a drawing of your [Rivers] and another Drawing of [NHD] with a column called [NHD_Name].
- What we'r doing here ist to create a buffer of one tenth of the length of each [NHD] line and create objects from your [Rivers] intersecting these buffers. An object may contain different branches.
- We then evaluate the match by the proportion of (intersecting River length / total River length) and order by this rate
- We discard all records without any intersection.
- Grouping by [River].[ID] we reduce the dataset to the ones with the best match and just to get a feeling about possible mismatches we keep the selected matching rate and the number of NHD lines that have been touched
Do you really want to ruin economy only to save the planet? |