By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE dbo.GeoTest (GeoId INT PRIMARY KEY IDENTITY(1,1), Coordinates GEOGRAPHY);
INSERT INTO dbo.GeoTest (Coordinates)
VALUES (GEOGRAPHY::STGeomFromText('POLYGON ((-73.7920290001574 40.7110690002396, -73.7910140000061 40.7114330001657, -73.7902149997648 40.7117169998112, -73.7894130000489 40.7119980001503, -73.7890120001909 40.7120730001493, -73.7889589995891 40.7120719998775, -73.7888749999235 40.7120669998801, -73.788786000235 40.7120589997473, -73.7886399997469 40.7120360002965, -73.7885240002942 40.7120100000196, -73.7884349997075 40.7119850000056, -73.7878470002517 40.7118109999209, -73.7874129998835 40.7118490001739, -73.7869960001319 40.7119019997558, -73.7865750001824 40.7119719998585, -73.7861600000808 40.7120600000193, -73.7857519996517 40.7121640003071, -73.7853530003419 40.7122839999658, -73.7850289995785 40.711571000044, -73.7847209996069 40.7109220001244, -73.7844560001913 40.7103729998545, -73.7844100001606 40.7102769998409, -73.785304999763 40.7100630002489, -73.7850369999744 40.709502000068, -73.7842899996117 40.7079309998188, -73.7848129996684 40.7079320001528, -73.785561999681 40.7079360001268, -73.7857700000934 40.7079340001398, -73.786739000214 40.7078919997191, -73.7877190002052 40.7076610003368, -73.7880399996972 40.7075709998122, -73.7886909998005 40.7074680001824, -73.7895700004077 40.7072220002897, -73.7897500003328 40.7071709997166, -73.7906530003311 40.7087259998055, -73.7911439997024 40.7095660001563, -73.791599999812 40.7103440000778, -73.7920290001574 40.7110690002396))', 4326));
1 rows affected
CREATE SPATIAL INDEX IX_TestSpatial ON dbo.GeoTest (Coordinates);
SET SHOWPLAN_XML ON;
DECLARE @Point GEOGRAPHY = 'Point(40.755656 -73.587627)';
SELECT Coordinates
FROM dbo.GeoTest
WHERE Coordinates.STDistance(@Point) <= 1;
| Microsoft SQL Server 2005 XML Showplan |
|---|
| <ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.539" Build="15.0.4223.1"><BatchSequence><Batch><Statements><StmtSimple StatementText="DECLARE @Point GEOGRAPHY = 'Point(40.755656 -73.587627)';

" StatementId="1" StatementCompId="1" StatementType="ASSIGN" RetrievedFromCache="false"></StmtSimple><StmtSimple StatementText="SELECT Coordinates
FROM dbo.GeoTest
WHERE Coordinates.STDistance(@Point) <= 1" StatementId="2" StatementCompId="2" StatementType="SELECT" RetrievedFromCache="false" StatementSubTreeCost="0.00428378" StatementEstRows="1" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0x9EBAD5B82FD1BE36" QueryPlanHash="0x229D01995BC1B2EC" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="150"><StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false"></StatementSetOptions><QueryPlan NonParallelPlanReason="NoParallelPlansInDesktopOrExpressEdition" CachedPlanSize="16" CompileTime="0" CompileCPU="0" CompileMemory="296"><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" GrantedMemory="0" MaxUsedMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="1258238" EstimatedPagesCached="78639" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="1656280"></OptimizerHardwareDependentProperties><OptimizerStatsUsage><StatisticsInfo LastUpdate="2024-10-11T17:55:30.41" ModificationCount="0" SamplingPercent="100" Statistics="[PK__GeoTest__EC445C5FA26AA6A9]" Table="[GeoTest]" Schema="[dbo]" Database="[fiddle_db002442981043c4a944331e0b5caa3c]"></StatisticsInfo><StatisticsInfo LastUpdate="2024-10-11T17:55:30.40" ModificationCount="0" SamplingPercent="100" Statistics="[IX_TestSpatial]" Table="[extended_index_581577110_384000]" Schema="[sys]" Database="[fiddle_db002442981043c4a944331e0b5caa3c]"></StatisticsInfo><StatisticsInfo LastUpdate="2024-10-11T17:55:30.41" ModificationCount="0" SamplingPercent="100" Statistics="[_WA_Sys_00000003_24927208]" Table="[extended_index_581577110_384000]" Schema="[sys]" Database="[fiddle_db002442981043c4a944331e0b5caa3c]"></StatisticsInfo></OptimizerStatsUsage><TraceFlags IsCompileTime="1"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><RelOp NodeId="0" PhysicalOp="Filter" LogicalOp="Filter" EstimateRows="1" EstimateIO="0" EstimateCPU="0.00100068" AvgRowSize="4035" EstimatedTotalSubtreeCost="0.00428378" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_db002442981043c4a944331e0b5caa3c]" Schema="[dbo]" Table="[GeoTest]" Column="Coordinates"></ColumnReference></OutputList><Filter StartupExpression="0"><RelOp NodeId="1" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="1" EstimatedRowsRead="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="4035" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="1" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_db002442981043c4a944331e0b5caa3c]" Schema="[dbo]" Table="[GeoTest]" Column="Coordinates"></ColumnReference></OutputList><IndexScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_db002442981043c4a944331e0b5caa3c]" Schema="[dbo]" Table="[GeoTest]" Column="Coordinates"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_db002442981043c4a944331e0b5caa3c]" Schema="[dbo]" Table="[GeoTest]" Index="[PK__GeoTest__EC445C5FA26AA6A9]" IndexKind="Clustered" Storage="RowStore"></Object></IndexScan></RelOp><Predicate><ScalarOperator ScalarString="[fiddle_db002442981043c4a944331e0b5caa3c].[dbo].[GeoTest].[Coordinates].STDistance([@Point])<=(1.0000000000000000e+000)"><Compare CompareOp="LE"><ScalarOperator><UDTMethod><CLRFunction Assembly="Microsoft.SqlServer.Types" Class="Microsoft.SqlServer.Types.SqlGeography" Method="STDistance"></CLRFunction><ScalarOperator><Identifier><ColumnReference Database="[fiddle_db002442981043c4a944331e0b5caa3c]" Schema="[dbo]" Table="[GeoTest]" Column="Coordinates"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Column="@Point"></ColumnReference></Identifier></ScalarOperator></UDTMethod></ScalarOperator><ScalarOperator><Const ConstValue="(1.0000000000000000e+000)"></Const></ScalarOperator></Compare></ScalarOperator></Predicate></Filter></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML> |
DECLARE @Point GEOGRAPHY = 'Point(40.755656 -73.587627)';
ASSIGN
ASSIGN
| Estimated Operator Cost | 0 (0%) |
|---|
Statement
DECLARE @Point GEOGRAPHY = 'Point(40.755656 -73.587627)';
SELECT Coordinates
FROM dbo.GeoTest
WHERE Coordinates.STDistance(@Point) <= 1
SELECT
SELECT
| Cached plan size | 16 KB |
|---|---|
| Estimated Operator Cost | 0 (0%) |
| Estimated Subtree Cost | 0.0042838 |
| Estimated Number of Rows | 1 |
Statement
SELECT Coordinates
FROM dbo.GeoTest
WHERE Coordinates.STDistance(@Point) <= 1
Filter
Cost: 23%
Filter
| Physical Operation | Filter |
|---|---|
| Logical Operation | Filter |
| Estimated Execution Mode | Row |
| Estimated Operator Cost | 0.0010007 (23%) |
| Estimated I/O Cost | 0 |
| Estimated CPU Cost | 0.0010007 |
| Estimated Subtree Cost | 0.0042838 |
| Estimated Number of Executions | 1 |
| Estimated Number of Rows | 1 |
| Estimated Row Size | 4035 B |
| Node ID | 0 |
Output List
[fiddle_db002442981043c4a944331e0b5caa3c].[dbo].[GeoTest].Coordinates
Predicate
[fiddle_db002442981043c4a944331e0b5caa3c].[dbo].[GeoTest].[Coordinates].STDistance([@Point])<=(1.0000000000000000e+000)
Clustered Index Scan (Clustered)
[GeoTest].[PK__GeoTest__EC445C5FA26…
Cost: 77%
Clustered Index Scan (Clustered)
Scanning a clustered index, entirely or only a range.
| Physical Operation | Clustered Index Scan |
|---|---|
| Logical Operation | Clustered Index Scan |
| Estimated Execution Mode | Row |
| Storage | RowStore |
| Estimated Operator Cost | 0.0032831 (77%) |
| Estimated I/O Cost | 0.003125 |
| Estimated CPU Cost | 0.0001581 |
| Estimated Subtree Cost | 0.0032831 |
| Estimated Number of Executions | 1 |
| Estimated Number of Rows to be Read | 1 |
| Estimated Number of Rows | 1 |
| Estimated Row Size | 4035 B |
| Ordered | False |
| Node ID | 1 |
Output List
[fiddle_db002442981043c4a944331e0b5caa3c].[dbo].[GeoTest].Coordinates
Object
[fiddle_db002442981043c4a944331e0b5caa3c].[dbo].[GeoTest].[PK__GeoTest__EC445C5FA26AA6A9]