examples of distance functions on geometry tables?

Question

There are many well-written examples of how to leverage SQL spatial data in geometry columns, however, every one (so far) I have found assumes that the conext involves querying against a user-defined shape (e.g. use T-SQL to define a single line, point, or polygon and query its location relative to something else....in most of the examples....another discrete shape created with T-SQL and/or as a temporary object). Are there any examples that demonstrate how to manipulate geometry records when both occur inside well-defined and full-of-many-rows tables? E.g. how to populate a column in the points table with the nearest name of a line from the line table, where both tables may have thousands of records covering a 600,000 acre-region. I may not just be looking hard enough....at it all weekend. Thanks!

Answer 1

I think the problem is that, by definition, functional code samples have to come with all the data that they rely on to demonstrate whatever method it is at hand. In the case of demonstrating spatial methods, it's not really practical to have a code sample that creates a table of dummy data containing thousands of LineStrings/Polygons/whatever, so instead they typically just use variable syntax DECLARE @g geometry = '...';

This syntax will largely be the same as if you're dealing with records in tables, although I agree that there are some (mostly performance) considerations that only apply when you're dealing with larger datasets.

---

twitter: @alastaira blog: http://alastaira.wordpress.com/

Answer 2

I've been messing with the good example at LessThanDot, here's a case:

SELECT h.* FROM zipcodes g 
JOIN zipcodes h on g.zipcode <> h.zipcode
AND g.zipcode = '10028'AND h.zipcode <> '10028'
WHERE g.GeogCol1.STDistance(h.GeogCol1)/1609.344 <= 20

where the example is good in that it provide instructions on how to make a test table for this situation.

However, I'm looking for more global processing, such as...updating the column of one point table with the name of road from a line table (both geometry). I'll shoot for even dummy tables with null records in the example, I have plenty of test data. Does this make sense? I'm having a heck of time substituting all of the "@g"'s with my production table names. Sorry, I'm at the bottom of the learning curve on SQL Spatial. Thanks!

Answer 3

Replacing the @g's with table/column names should be pretty much all there is to it... if there's a particular query that you're having problems writing then post it here together with the DDL that creates the table structures involved.

---

twitter: @alastaira blog: http://alastaira.wordpress.com/

Answer 4

Hey thanks alot! I ordered your book last week....

Here's an example I'm really struggeling with:

SELECT dtbl.ID,min(dtbl.DistanceFrom) as DistanceFrom 
from 
(
 SELECT ID
  ,LAT
  ,LONG
  ,geometry::Point(lat,long,4326) as oPoint
  ,LINEMARKS.Line_ID
  ,[geometry].STDistance(geometry::Point(lat,long,4326)) as DistanceFrom
 FROM SAMPLEDATA, LINEMARKS 
  Where [geometry].STDistance(geometry::Point lat,long,4326)) < 0.001 
) as dtbl
GROUP BY ID 
ORDER BY ID, Distance asc

from georeference.org

So far I'm at

SELECT dtbl.ID,min(dtbl.DistanceFrom) as DistanceFrom 
from 
(
 SELECT ID
  ,LAT
  ,LONG
  ,geometry::Point(lat,long,4326) as oPoint
  ,dbo.GRSM_TRAILS.Name
  ,[geometry].STDistance(geometry::Point(lat,long,4326)) as DistanceFrom
 FROM dbo.TBL_LOCATIONS,dbo.GRSM_TRAILS  
  Where [geometry].STDistance(geometry::Point lat,long,4326)) < 0.001 
) as dtbl
GROUP BY ID 
ORDER BY ID, Distance asc

Here are the two minimally populated tables I'm working with:

CREATE TABLE [dbo].[TBL_LOCATIONS](
	[OBJECTID] [int] NOT NULL,
	[FCategory] [nvarchar](16) NULL,
	[MapMethod] [nvarchar](4) NULL,
	[HError] [nvarchar](50) NULL,
	[MapSource] [nvarchar](255) NULL,
	[SourceDate] [datetime2](7) NULL,
	[EditDate] [datetime2](7) NULL,
	[Notes] [nvarchar](255) NULL,
	[Site_ID] [uniqueidentifier] NULL,
	[Meta_MID] [nvarchar](50) NULL,
	[X_Coord] [numeric](38, 8) NULL,
	[Y_Coord] [numeric](38, 8) NULL,
	[Coord_Units] [nvarchar](50) NULL,
	[Coord_System] [nvarchar](50) NULL,
	[UTM_Zone] [nvarchar](50) NULL,
	[Accuracy_Notes] [nvarchar](max) NULL,
	[Unit_Code] [nvarchar](12) NULL,
	[Loc_Name] [nvarchar](100) NULL,
	[Loc_Type] [nvarchar](25) NULL,
	[Updated_Date] [nvarchar](50) NULL,
	[Loc_Notes] [nvarchar](max) NULL,
	[Datum] [nvarchar](5) NULL,
	[Watershed] [nvarchar](50) NULL,
	[StreamName] [nvarchar](50) NULL,
	[NHDReachCode] [nvarchar](14) NULL,
	[TOPO_NAME] [nvarchar](50) NULL,
	[Trail] [nvarchar](100) NULL,
	[Road] [nvarchar](50) NULL,
	[Elevation] [numeric](38, 8) NULL,
	[LAT] [numeric](38, 8) NULL,
	[LON] [numeric](38, 8) NULL,
	[Year_] [nvarchar](4) NULL,
	[County] [nvarchar](30) NULL,
	[State] [nvarchar](30) NULL,
	[IsExtant] [nvarchar](3) NULL,
	[IsSenstive] [nvarchar](3) NULL,
	[Eco_Notes] [nvarchar](50) NULL,
	[EcoGroup] [nvarchar](50) NULL,
	[ELCode] [smallint] NULL,
	[Validation] [nvarchar](50) NULL,
	[LocationDescription] [nvarchar](max) NULL,
	[LocationDirections] [nvarchar](max) NULL,
	[VerbatimLocation] [nvarchar](255) NULL,
	[PlaceName] [nvarchar](75) NULL,
	[SHAPE] [geometry] NULL,
	[Location_ID] [uniqueidentifier] NOT NULL,
 CONSTRAINT [PK_TBL_LOCATIONS] PRIMARY KEY CLUSTERED 
(
	[OBJECTID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT [dbo].[TBL_LOCATIONS] ([OBJECTID], [FCategory], [MapMethod], [HError], [MapSource], [SourceDate], [EditDate], [Notes], [Site_ID], [Meta_MID], [X_Coord], [Y_Coord], [Coord_Units], [Coord_System], [UTM_Zone], [Accuracy_Notes], [Unit_Code], [Loc_Name], [Loc_Type], [Updated_Date], [Loc_Notes], [Datum], [Watershed], [StreamName], [NHDReachCode], [TOPO_NAME], [Trail], [Road], [Elevation], [LAT], [LON], [Year_], [County], [State], [IsExtant], [IsSenstive], [Eco_Notes], [EcoGroup], [ELCode], [Validation], [LocationDescription], [LocationDirections], [VerbatimLocation], [PlaceName], [SHAPE], [Location_ID]) VALUES (30261, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, CAST(260859.72450000 AS Numeric(38, 8)), CAST(3975860.58820000 AS Numeric(38, 8)), NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'BOYDS CREEK', NULL, NULL, NULL, NULL, NULL, NULL, N'Sevier', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0x25690000010CE0A7C6CBDDD70F4138234A4B5A554E41, N'55085392-ca55-e111-affa-002564d275d1')
ALTER TABLE [dbo].[TBL_LOCATIONS] ADD  CONSTRAINT [DF__TBL_LOCAT__Globa__12C8C788]  DEFAULT (newsequentialid()) FOR [Location_ID]
GO
/****** Object:  Check [g2_ck]    Script Date: 02/12/2012 17:44:55 ******/
ALTER TABLE [dbo].[TBL_LOCATIONS]  WITH CHECK ADD  CONSTRAINT [g2_ck] CHECK  (([SHAPE].[STSrid]=(26917)))
GO
ALTER TABLE [dbo].[TBL_LOCATIONS] CHECK CONSTRAINT [g2_ck]
GO

and

CREATE TABLE [dbo].[GRSM_TRAILS](
	[OBJECTID] [int] NOT NULL,
	[Name] [nvarchar](254) NULL,
	[ID] [nvarchar](254) NULL,
	[Distance_R] [int] NULL,
	[Dist_metho] [nvarchar](10) NULL,
	[Map] [nvarchar](10) NULL,
	[SgmCumm] [numeric](38, 8) NULL,
	[InvtryID] [nvarchar](254) NULL,
	[update_] [datetime2](7) NULL,
	[District] [nvarchar](1) NULL,
	[Class] [nvarchar](20) NULL,
	[AdninRoad] [int] NULL,
	[Horse] [nvarchar](10) NULL,
	[BMT] [nvarchar](10) NULL,
	[MtoS] [nvarchar](10) NULL,
	[LOCATION] [numeric](38, 8) NULL,
	[EQNUM] [numeric](38, 8) NULL,
	[Type] [nvarchar](20) NULL,
	[RuleID] [int] NULL,
	[Override] [varbinary](max) NULL,
	[Shape] [geometry] NULL,
 CONSTRAINT [R18_pk] PRIMARY KEY CLUSTERED 
(
	[OBJECTID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 75) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[GRSM_TRAILS] ([OBJECTID], [Name], [ID], [Distance_R], [Dist_metho], [Map], [SgmCumm], [InvtryID], [update_], [District], [Class], [AdninRoad], [Horse], [BMT], [MtoS], [LOCATION], [EQNUM], [Type], [RuleID], [Override], [Shape]) VALUES (63, N'Appalachian Trail', N'AT19_2103', 5007, N'W', N'HO', CAST(237567.00000000 AS Numeric(38, 8)), N'SO-02', CAST(0x070000000000B0270B AS DateTime2), N'S', N'foot path', 0, N' ', N' ', N' ', CAST(0.00000000 AS Numeric(38, 8)), CAST(0.00000000 AS Numeric(38, 8)), N'HO', 1, NULL, 0x2569000001044A000000D07F48FFE45311412C431C5B1E1A4E41702497FF18541141A05E29DB201A4E41505BB1FF2C54114158EC2FDB241A4E41702497FF2C5411413433335B291A4E41B0B662FF1054114158EC2F5B2B1A4E4120DBF9FEDC531141C017265B2D1A4E4190FF90FEBC531141C017265B341A4E41102428FE84531141088A1FDB391A4E41607FD9FD6C531141088A1FDB411A4E41A011A5FD5C531141E4D022DB471A4E4130FF21FD3C531141C01726DB551A4E41E0B584FCFC521141088A1F5B5E1A4E41E0B584FC00531141E4D022DB601A4E41E0B584FC1453114158EC2F5B691A4E41E0B584FC20531141107A36DB701A4E41E0B584FC30531141A44E405B761A4E41A023B9FC78531141AC3E57DB811A4E41805AD3FC985311411C5A645B891A4E4130FF21FDE8531141244A7B5B941A4E41F06C56FD205411414CF38EDB9F1A4E41C0DA8AFD5C54114154E3A5DBA91A4E418048BFFD8C541141A045B65BB21A4E41A011A5FD985411415CD3BCDBB91A4E41F06C56FD7C541141381AC0DBC21A4E4130FF21FD705411411461C35BCB1A4E4150C807FD7C541141CCEEC95BD21A4E417091EDFC78541141847CD05BD91A4E41A023B9FC785411413C0AD7DBE11A4E41805AD3FC8C541141F897DDDBE51A4E4130FF21FDBC54114168B3EADBE71A4E4110363CFDE0541141FC87F4DBEB1A4E41607FD9FD3C551141283108DCEC1A4E41D0915CFE8C5511417493185CEF1A4E4140A4DFFEE4551141A03C2C5CF31A4E41D07F48FF34561141C8E53FDCF71A4E4120C9E5FF98561141AC1C5A5CFD1A4E4150499D0009571141905374DC001B4E4170006F019557114150D1915C041B4E41A0802602F5571141787AA55C041B4E41D000DE027958114138F8C25C0A1B4E4160DC4603B5581141A813D05C091B4E41D0EEC90305591141F875E05C0B1B4E412038670469591141D8ACFA5C111B4E41B013D004BD59114104560E5D171B4E4110265305195A11410C46255D1C1B4E4150A60A06815A114134EF385D1B1B4E418026C206F55A11411826535D1D1B4E418014AE07655B1141648863DD171B4E4120B9FC07955B1141D8A3705D181B4E41B0946508D95B114148BF7D5D191B4E416039B408095C1141004D84DD171B4E4190B96B09715C1141083D9B5D181B4E41305EBA09A55C11417858A8DD191B4E41C039230AED5C1141A401BC5D211B4E411083C00A615D11418838D65D251B4E41E0F0F40A855D11411C0DE0DD291B4E41A05E290BA15D1141D49AE65D2A1B4E414003780BC95D11418C28ED5D281B4E41F0A7C60BF55D114144B6F35D241B4E4140F1630C4D5E1141941804DE251B4E41903A010DBD5E1141784F1E5E2A1B4E4120166A0DF95E1141E86A2BDE2A1B4E419028ED0D455F114134CD3B5E2D1B4E412004560EA55F1141F44A59DE361B4E41C0A8A40EE55F114144AD695E3E1B4E418016D90E21601141489D805E491B4E41704DF30E41601141E0718A5E4F1B4E41C096900FA5601141E461A15E511B4E414072F90FE960114134C4B1DE541B4E41F0164810316111415C6DC55E5D1B4E41B0847C107961114140A4DF5E6D1B4E41D04D621081611141D478E9DE741B4E41B0847C10B56111410022FDDE811B4E41A0BB9610E16111414C840DDF8B1B4E4101000000010000000001000000FFFFFFFF0000000002)

ALTER TABLE [dbo].[GRSM_TRAILS]  WITH CHECK ADD  CONSTRAINT [g7_ck] CHECK  (([SHAPE].[STSrid]=(26917)))
GO
ALTER TABLE [dbo].[GRSM_TRAILS] CHECK CONSTRAINT [g7_ck]
GO