TL;DR: To validate GPS coordinates in SQL Server, stage the raw latitude and longitude as text, check each value’s format and range, log failures with a rejection reason, and only then pass the accepted pairs to geography::Point().
This stops malformed values, such as the word “north”, a blank longitude or a latitude of 95, from breaking your load, and it lets you run distance queries on clean spatial data.
This article follows 12 fictional service locations from raw input to a “within 10 km” search. It also shows the one thing numeric validation can’t catch: a swapped latitude and longitude that passes every check.
In brief:
– Stage the raw coordinates as nvarchar so nothing is lost or rounded before validation.
– Normalize each value by trimming spaces and turning empty strings into NULL.
– Check the format, then convert with TRY_CONVERT() to decimal(9,6) for latitude and decimal(10,6) for longitude.
– Check ranges (latitude -90 to 90, longitude -180 to 180) and assign one rejection code per failed row.
– Build geography points from accepted rows only, in a separate statement.
– Keep rejected rows with their original text and query accepted points with STDistance().
Read on for the full guide, including code.
Why bad coordinates break geography::Point() loads
A list of service locations comes in with a name, latitude, and longitude for each entry. Most of the coordinates look fine…but not all. One entry, for example, contains the word “north” instead of a latitude. Another has a blank longitude. And a third entry has a latitude of 95 degrees.
This is a problem, as the valid rows still need to be loaded to be used for searches – such as finding locations within 10 km of a customer.
Passing the raw values directly to geography::Point() can also cause problems. Malformed text may fail during conversion, an out-of-range coordinate may pass through a weak validation process, and some mistakes can still produce a valid point in the wrong place.
This article follows twelve fictional records from the raw input through validation and, finally, to a distance query. The original coordinate values are preserved, each row is checked against clear validation rules, rejected records are stored with a reason, and only accepted coordinate pairs are converted to SQL Server geography values.
One deliberately swapped pair, meanwhile, is kept in the ‘accepted’ set. This is to demonstrate an important limitation: numeric validation alone cannot prove that a location is correct.
The sample coordinates are assumed to use World Geodetic System (WGS) 84, represented here by SRID 4326. The geography data type is appropriate for this example because the coordinates represent locations on the Earth.
The geometry type represents data in a flat Euclidean coordinate system – with the choice between them depending on the coordinate system being used and the type of calculation required.
The test environment and table design
I tested the examples on SQL Server 2022 Developer Edition, build 16.0.1000.6, using a GPSGeographyDemo database at compatibility level 160.
The example uses three tables, each with a different job:
dbo.GPSImportStagekeeps the coordinate values exactly as they arrived.dbo.ValidLocationsstores the rows that pass validation, including their parsed latitude and longitude values and the final geography point.dbo.GPSImportRejectsstores rows that fail validation together with the reason for rejection.
The two coordinate columns in the staging table use nvarchar deliberately.
Step 1: Stage raw coordinates as text
The raw staging table
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
CREATE TABLE dbo.GPSImportStage ( ImportRowID int NOT NULL, LocationName nvarchar(100) NOT NULL, RawLatitude nvarchar(50) NULL, RawLongitude nvarchar(50) NULL, LoadedAt datetime2(0) NOT NULL CONSTRAINT DF_GPSImportStage_LoadedAt DEFAULT (SYSUTCDATETIME()), CONSTRAINT PK_GPSImportStage PRIMARY KEY CLUSTERED (ImportRowID) ); |
Converting the coordinates as soon as they arrive could remove useful information before validation has a chance to inspect it. A blank value might be mistaken for zero, or extra decimal places might be rounded during conversion. Keeping the incoming values as text means the validation process can work with the original input.
The 50-character limit is only part of this demonstration. In a production landing process, the complete source value should be preserved, or an over-length value should be rejected before any truncation takes place. Once part of the original value has been lost, the validator cannot recover it.
The 12-row sample batch
The sample batch contains the following rows:
The original sample batch
| ID | Location | Raw latitude | Raw longitude |
| 1 | London Central | 51.5074 | -0.1278 |
| 2 | Camden Service Point | 51.5416 | -0.1433 |
| 3 | Greenwich Service Point | 51.4826 | 0.0077 |
| 4 | Heathrow Depot | 51.4700 | -0.4543 |
| 5 | Manchester Depot | 53.4808 | -2.2426 |
| 6 | Missing Latitude | NULL | -0.1180 |
| 7 | Blank Longitude | 51.5150 | (three spaces) |
| 8 | Bad Latitude Text | north | -0.1410 |
| 9 | Bad Longitude Text | 51.5200 | west |
| 10 | Latitude Out of Range | 95.0000 | -0.1278 |
| 11 | Longitude Out of Range | 51.5074 | 190.0000 |
| 12 | Karachi – Swapped Example | 67.0011 | 24.8607 |
In the sample batch, Row 6 contains a database NULL, and Row 7 contains three ordinary spaces in the longitude field.
ImportRowID, meanwhile, identifies the incoming source row. When a row is accepted, that value is copied into SourceImportRowID.
LocationID is a separate identity value generated by dbo.ValidLocations. SourceImportRowID then provides the link back to the original staged row, while LocationID identifies the accepted location inside the destination table.
Step 2: Create tables for accepted and rejected rows
The two output tables separate accepted rows from rejected rows. The accepted rows keep the parsed coordinates and the final geography value, while the rejected rows keep the text so the source data can still be investigated.
The output tables
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
CREATE TABLE dbo.ValidLocations ( LocationID int IDENTITY(1,1) NOT NULL, LocationName nvarchar(100) NOT NULL, Latitude decimal(9,6) NOT NULL, Longitude decimal(10,6) NOT NULL, GeoPoint geography NOT NULL, SourceImportRowID int NOT NULL, CONSTRAINT PK_ValidLocations PRIMARY KEY CLUSTERED (LocationID), CONSTRAINT UQ_ValidLocations_SourceImportRowID UNIQUE (SourceImportRowID) ); CREATE TABLE dbo.GPSImportRejects ( ImportRowID int NOT NULL, LocationName nvarchar(100) NOT NULL, RawLatitude nvarchar(50) NULL, RawLongitude nvarchar(50) NULL, RejectCode varchar(40) NOT NULL, RejectReason nvarchar(250) NOT NULL, RejectedAt datetime2(0) NOT NULL CONSTRAINT DF_GPSImportRejects_RejectedAt DEFAULT (SYSUTCDATETIME()), CONSTRAINT PK_GPSImportRejects PRIMARY KEY CLUSTERED (ImportRowID) ); |
Here, dbo.ValidLocations gives each accepted location its own LocationID, while SourceImportRowID keeps the link back to the staged row.
The unique constraint on SourceImportRowID also prevents the same source row from being loaded into the accepted table more than once.
dbo.GPSImportRejects uses the original ImportRowID as its primary key and keeps the raw latitude and longitude text together with the rejection code and reason. This makes it possible to investigate a failed row without losing the source values that caused the rejection.
Step 3: Parse latitude and longitude safely with TRY_CONVERT()
Before parsing the coordinates, the feed needs a clear format rule. In this example, a coordinate may begin with an optional + or – sign, followed by ASCII (American Standard Code for Information Interchange) digits.
Coordinate format rules for this feed (ASCII)
A decimal point is optional but, when one is present, there must be digits on both sides of it (with no more than six digits after the decimal point.)
Ordinary spaces, meanwhile, are allowed at the beginning or end of the value – but tabs, commas, scientific notation, degree symbols, and embedded spaces are not accepted in any form.
These rules form the contract for this particular feed. Another source might allow scientific notation or a greater number of decimal places, but that would require a different set of validation rules and tests.
Normalize values with TRIM() and NULLIF()
Normalization begins by removing ordinary spaces from both ends of the value. If nothing remains, NULLIF() turns the empty string into NULL.
Normalize latitude for validation
|
1 2 3 4 |
NULLIF( TRIM(RawLatitude) COLLATE Latin1_General_100_BIN2, N'' ) AS LatitudeText |
The longitude uses the same expression. By default, TRIM() removes ordinary char(32) spaces from the beginning and end of the string. Tabs and nonbreaking spaces remain in place, however, so the later format checks can reject them, and a coordinate value of zero is valid data (so must remain different from a missing value.)
For the character checks, an optional leading sign is removed temporarily and the rest of the text is examined. The remaining value must start and end with a digit, contain only digits, have one decimal point at most, and contain no more than six digits after the decimal point.
The Latin1_General_100_BIN2 collation makes these checks follow the ASCII-based rules defined for this feed instead of relying on linguistic comparison rules.
After a value passes the format checks, TRY_CONVERT() attempts to convert latitude to decimal(9,6) and longitude to decimal(10,6). TRY_CONVERT() the returns NULL if a value cannot be converted to the required decimal type.
This allows the validation logic to handle the row instead of stopping the import.
The six-decimal-place rule also prevents conversion from hiding a problem, as SQL Server can round a value when converting it to a type with a smaller scale.
For example, 90.0000004 could become 90.000000 when converted to decimal(9,6). That would hide the fact that the original latitude was greater than 90. By rejecting values with more than six fractional digits before conversion, the parser avoids this problem.
The same rule also rejects an otherwise valid-looking value such as 51.5074001. That is intentional: both cases are treated consistently according to the format contract for this feed.
Fast, reliable and consistent SQL Server development…
Step 4: Validate ranges and assign rejection codes
The next query brings the parsing rules together and assigns one validation result to each staged row.
It returns the original values, the parsed coordinates, and the final validation result without inserting anything into the output tables or constructing a geography value.
Preview the complete validation result
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 |
;WITH Normalized AS ( SELECT ImportRowID, LocationName, RawLatitude, RawLongitude, NULLIF(TRIM(RawLatitude) COLLATE Latin1_General_100_BIN2, N'') AS LatitudeText, NULLIF(TRIM(RawLongitude) COLLATE Latin1_General_100_BIN2, N'') AS LongitudeText FROM dbo.GPSImportStage ), CoordinateBodies AS ( SELECT *, CASE WHEN LEFT(LatitudeText, 1) IN (N'+', N'-') THEN SUBSTRING(LatitudeText, 2, 50) ELSE LatitudeText END AS LatitudeBody, CASE WHEN LEFT(LongitudeText, 1) IN (N'+', N'-') THEN SUBSTRING(LongitudeText, 2, 50) ELSE LongitudeText END AS LongitudeBody FROM Normalized ), Parsed AS ( SELECT *, CASE WHEN LatitudeBody LIKE N'[0-9]%' AND RIGHT(LatitudeBody, 1) LIKE N'[0-9]' AND LatitudeBody NOT LIKE N'%[^0-9.]%' AND LatitudeBody NOT LIKE N'%.%.%' AND (CHARINDEX(N'.', LatitudeBody) = 0 OR LEN(LatitudeBody) - CHARINDEX(N'.', LatitudeBody) <= 6) THEN TRY_CONVERT(decimal(9,6), LatitudeText) END AS ParsedLatitude, CASE WHEN LongitudeBody LIKE N'[0-9]%' AND RIGHT(LongitudeBody, 1) LIKE N'[0-9]' AND LongitudeBody NOT LIKE N'%[^0-9.]%' AND LongitudeBody NOT LIKE N'%.%.%' AND (CHARINDEX(N'.', LongitudeBody) = 0 OR LEN(LongitudeBody) - CHARINDEX(N'.', LongitudeBody) <= 6) THEN TRY_CONVERT(decimal(10,6), LongitudeText) END AS ParsedLongitude FROM CoordinateBodies ) SELECT ImportRowID, LocationName, RawLatitude, RawLongitude, ParsedLatitude, ParsedLongitude, CASE WHEN LatitudeText IS NULL THEN 'MISSING_LATITUDE' WHEN LongitudeText IS NULL THEN 'MISSING_LONGITUDE' WHEN ParsedLatitude IS NULL THEN 'INVALID_LATITUDE_FORMAT' WHEN ParsedLongitude IS NULL THEN 'INVALID_LONGITUDE_FORMAT' WHEN ParsedLatitude NOT BETWEEN -90 AND 90 THEN 'LATITUDE_OUT_OF_RANGE' WHEN ParsedLongitude NOT BETWEEN -180 AND 180 THEN 'LONGITUDE_OUT_OF_RANGE' ELSE 'ACCEPT' END AS ValidationResult FROM Parsed ORDER BY ImportRowID; |
The order of the final CASE expression is deliberate. Missing latitude and longitude are checked first, followed by invalid coordinate formats, and then the latitude and longitude ranges. If a row has more than one problem, the first matching rule determines the rejection code.
For example, a row containing latitude 95 and longitude west is classified as INVALID_LONGITUDE_FORMAT. The latitude can be converted successfully but the longitude cannot. Both format checks are evaluated before either range check.
The format codes can also apply to any numeric text that doesn’t fit the selected decimal type. decimal(9,6) allows nine digits in total, with six after the decimal point (leaving three digits before it.)
A latitude such as 1000, however, cannot fit that type – so TRY_CONVERT() returns NULL, and the row is classified as INVALID_LATITUDE_FORMAT.
A value such as 95, on the other hand, converts successfully and is rejected later by the range check.
Therefore, in this example, a format error doesn’t necessarily mean that the source value contained letters; it can also mean that the numeric value is too large for the chosen decimal type.
Latitude must be between -90 and 90, inclusive. For this feed, longitude must be between -180 and 180, inclusive. These limits are checked before any geography value is created.
The import, therefore, doesn’t depend on how a particular SQL Server build handles longitude values outside the range accepted by the feed. In the sample batch, 190.0000 is rejected as LONGITUDE_OUT_OF_RANGE.
Row 12: valid numbers, wrong place
The 12 staged rows produce the following results:
Validation results from the sample batch
| Import Row ID | Validation Result |
| 1, 2, 3, 4, 5, 12 | ACCEPT |
| 6 | MISSING_LATITUDE |
| 7 | MISSING_LONGITUDE |
| 8 | INVALID_LATITUDE_FORMAT |
| 9 | INVALID_LONGITUDE_FORMAT |
| 10 | LATITUDE_OUT_OF_RANGE |
| 11 | LONGITUDE_OUT_OF_RANGE |
Row 12 shows a limit of this validation. Both 67.0011 and 24.8607 are valid numeric values and fall within the allowed latitude and longitude ranges, so the row is classified as ACCEPT. However, none of these rules checks whether those coordinates actually correspond to the location name Karachi.
Detecting that kind of problem requires additional business context, such as an expected service region, a known site record, or another trusted source. Automatically swapping latitude and longitude because a pair looks suspicious would be unsafe, because an unusual but correct location could then be changed into an incorrect one.
The supporting parser tests cover 30 cases, including zero, both range boundaries, values immediately outside those boundaries, competing rejection reasons, values with seven fractional digits, numeric overflow, and unexpected characters.
Step 5: Build geography points from accepted rows only
The listings in this section show the post-validation part of the load. In the complete load script, the validation logic from Listing 4 is materialized in #GPSDemoValidated, and rejected rows are routed separately to dbo.GPSImportRejects.
Rows classified as ACCEPT are then copied into #GPSDemoAccepted before any geography value is constructed.
Constrain the accepted coordinate pairs
|
1 2 3 4 5 6 7 8 9 |
CREATE TABLE #GPSDemoAccepted ( ImportRowID int NOT NULL PRIMARY KEY, LocationName nvarchar(100) NOT NULL, Latitude decimal(9,6) NOT NULL CHECK (Latitude BETWEEN -90 AND 90), Longitude decimal(10,6) NOT NULL CHECK (Longitude BETWEEN -180 AND 180) ); |
Materialize the accepted rows
|
1 2 3 4 5 |
INSERT INTO #GPSDemoAccepted (ImportRowID, LocationName, Latitude, Longitude) SELECT ImportRowID, LocationName, ParsedLatitude, ParsedLongitude FROM #GPSDemoValidated WHERE ValidationResult = 'ACCEPT'; |
The NOT NULL and CHECK constraints reinforce the rules that were already applied during validation. At this point, geography::Point() no longer operates directly on the raw feed or on a query containing both valid and invalid rows.
Instead, it now operates on the materialized set of accepted coordinate pairs only.
Build the stored geography points
|
1 2 3 4 5 6 7 8 9 |
INSERT INTO dbo.ValidLocations (LocationName, Latitude, Longitude, GeoPoint, SourceImportRowID) SELECT LocationName, Latitude, Longitude, geography::Point(Latitude, Longitude, 4326), ImportRowID FROM #GPSDemoAccepted; |
Keeping validation and construction in separate statements is deliberate. I think this approach also makes the logic easier to follow, as a common table expression (CTE) is not a materialized result, and SQL Server doesn’t guarantee that predicates or expressions will be evaluated in the order they appear in a query.
Therfore, putting a filter and geography::Point() in the same statement does not guarantee that invalid rows will always be removed before the constructor is evaluated. In this example, the accepted values have already been stored in #GPSDemoAccepted before the constructor runs.
Latitude and longitude order: Point() vs WKT
geography::Point() takes latitude, longitude, and SRID in that order. Well-known text (WKT) represents a point using the opposite coordinate order: longitude first, followed by latitude. The following two expressions describe the same point in London:
Two forms of the same reference point
|
1 2 3 4 5 6 7 8 |
DECLARE @ReferencePoint geography = geography::Point(51.5074, -0.1278, 4326); DECLARE @ReferenceFromWKT geography = geography::STGeomFromText( N'POINT(-0.1278 51.5074)', 4326 ); |
When the London point is displayed as WKT, it appears as POINT (-0.1278 51.5074). In this format, longitude comes before latitude.
I also ran a test using STDistance() between the Point() and WKT versions, and the result was zero. This confirms that both expressions describe the same location.
I use SRID 4326 in this example because the feed is assumed to contain WGS 84 coordinates. Assigning SRID 4326 doesn’t transform coordinates from another reference system into WGS 84.
If the incoming coordinates use a different coordinate reference system, they must be converted correctly before being treated as WGS 84 values. All six points accepted by the test load use SRID 4326.
The complete load wraps its output changes in a transaction with XACT_ABORT enabled, and a TRY...CATCH block that rolls back an active transaction before rethrowing the error. The supporting transaction test deliberately causes a failure after an initial write, confirming that no partial output remains.
The successful sample load then produces six accepted rows and six rejected rows from the 12 staged records.
Step 6: Keep the rejected rows for review
A rejected row should keep enough information to explain what went wrong and to trace the result back to the original import.
In this example, the rejection table stores the source row identifier, location name, original latitude and longitude strings, a rejection code, a readable explanation, and a timestamp.
The raw coordinate strings are copied from staging without being normalized or rewritten. This is important because details such as NULL, an empty value, or whitespace can help explain what the source system actually supplied.
To review the rejected rows after the load:
Review the rejected input
|
1 2 3 4 5 6 7 8 9 |
SELECT ImportRowID, LocationName, RawLatitude, RawLongitude, RejectCode, RejectReason FROM dbo.GPSImportRejects ORDER BY ImportRowID; |
The six rejected rows come from source IDs 6 through 11. Row 6 has a missing latitude, while row 7 contains three spaces in the longitude field. Both are treated as missing values by the validation rules.
Also, preserving the original input still lets us distinguish between a field that was not supplied, and one that arrived as whitespace.
Additionally, the supporting checks compare the rejected coordinate strings with the staged values to confirm that the original text was preserved. This is useful for values such as the three spaces in row 7, which are difficult to verify simply by looking at a SQL Server Management Studio (SSMS) results grid.
The two output tables enforce uniqueness separately, but the schema doesn’t actually contain one single rule that guarantees every staged row appears in exactly one output table.
The reconciliation checks therefore verify that each staged row is routed once (either to the accepted table or to the rejection table.) A production process would also need a clear policy for corrections, retries, and reprocessing.
Step 7: Find locations within 10 km with STDistance()
With the accepted points stored, they can now be used to answer the practical question: which locations are within 10 km of London Central? The query creates a reference point, sets the search radius to 10,000 metres, and compares that point with each stored geography value.
Find points within 10 kilometres
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
DECLARE @ReferencePoint geography = geography::Point(51.5074, -0.1278, 4326); DECLARE @RadiusMetres float = 10000.0; SELECT v.SourceImportRowID, v.LocationName, CONVERT(decimal(12,3), v.GeoPoint.STDistance(@ReferencePoint)) AS DistanceMetres, CONVERT(decimal(10,3), v.GeoPoint.STDistance(@ReferencePoint) / 1000.0) AS DistanceKm FROM dbo.ValidLocations AS v WHERE v.GeoPoint.STDistance(@ReferencePoint) <= @RadiusMetres ORDER BY v.GeoPoint.STDistance(@ReferencePoint), v.SourceImportRowID; |
For SRID 4326, STDistance() returns the distance in metres. The supporting checks also confirm the corresponding unit information in sys.spatial_reference_systems. Dividing the result by 1000.0 converts the displayed value to kilometres.
Locations returned by the 10km query
| Source ID | Location | Distance (m) | Distance (km) |
| 1 | London Central | 0.000 | 0.000 |
| 2 | Camden Service Point | 3954.178 | 3.954 |
| 3 | Greenwich Service Point | 9806.393 | 9.806 |
London Central returns a distance of zero because its coordinates are identical to the reference point. Camden Service Point and Greenwich Service Point are also within the 10 km radius.
The other accepted locations fall outside that radius. Heathrow Depot is about 23.056 km away, Manchester Depot is 262.371 km away, and the swapped Karachi example produces a distance of about 2206.859 km. That number describes the stored point with latitude 67.0011 and longitude 24.8607 – not the actual distance to the intended Karachi location!
Put simply, a plausible numeric result does not prove that the source coordinates describe the correct place.
The radius comparison uses the original STDistance() value in metres. Rounding is applied only to the columns shown in the result set, while the WHERE clause and ordering use the unrounded distance.
This matters near the boundary, as a point slightly more than 10 km away remains outside the result even if its displayed distance rounds to 10.000 km.
STDistance() represents an approximate geodesic distance between the two geography points, rather than being a road distance or travel-time estimate. Similarly, the number of decimal places shown in the result doesn’t indicate the accuracy of the GPS.
If the two geography values have different SRIDs, STDistance() returns NULL. Because a comparison with NULL doesn’t satisfy the radius predicate, an SRID mismatch could cause a row to be excluded without appearing as an error.
The query therefore assumes that all points use the same reference system.
Do you actually need a spatial index?
With only six accepted locations, this example is too small to demonstrate any performance benefit from a spatial index. This is why the scripts do not include timing or logical-read comparisons.
For a larger table, SQL Server can use a spatial index with an STDistance() predicate such as:
GeoPoint.STDistance(@ReferencePoint) <= @RadiusMetres |
The predicate needs to remain in a form that SQL Server can recognize for spatial index use. Even when a query is eligible to use a spatial index, the optimizer may still choose a different access method if it estimates that approach will cost less.
A spatial index also requires a clustered primary key. dbo.ValidLocations already has one on LocationID, although this example doesn’t create a spatial index. For a real workload, performance should be tested with a representative number and distribution of locations and realistic search radii.
The actual execution plan should also be examined. A 10 km search in a dense city can behave very differently from a much broader search over the same table.
Conclusion: what validation can’t catch (swapped coordinates)
The sample import finishes with six accepted locations and six rejected rows. Missing values are kept separate from valid zero coordinates, malformed text receives a clear rejection reason, and out-of-range coordinates are rejected before a geography value is constructed.
Once the accepted points are stored, the 10 km query can work directly with validated spatial data instead of parsing raw coordinate text each time.
The swapped Karachi example shows what numeric validation cannot determine on its own. Its latitude and longitude both satisfy the format and range rules, so SQL Server can construct a valid point and calculate a distance from it.
That does not mean the coordinates describe the intended location, however. Detecting this kind of problem requires additional business knowledge, such as an expected service area or a trusted record of known locations.
Furthermore, preserving the original source values makes it possible to investigate a suspicious row and apply a correction – but only when there is enough evidence to justify it.
Subscribe to the Simple Talk newsletter
FAQs
1. How do you validate latitude and longitude in SQL Server?
Store the raw values as nvarchar, trim spaces, and check the format with pattern rules before converting with TRY_CONVERT() to decimal(9,6) for latitude and decimal(10,6) for longitude. Then confirm latitude is between -90 and 90 and longitude is between -180 and 180. Only rows that pass every check should reach geography::Point().
2. Why does geography::Point() fail on some coordinates?
It fails when a value is malformed text or falls outside the range the type accepts, such as a latitude above 90. If valid and invalid rows share one statement, SQL Server doesn’t guarantee the filter runs before the constructor. Validate first, store accepted pairs in a temp table, then build the points in a separate statement.
3. Does geography::Point() take latitude or longitude first?
geography::Point() takes latitude, then longitude, then the SRID. Well-known text (WKT) reverses this, writing POINT(longitude latitude). geography::Point(51.5074, -0.1278, 4326) and STGeomFromText(N'POINT(-0.1278 51.5074)', 4326) describe the same London point, and STDistance() between them returns zero.
4. How do you find locations within 10 km in SQL Server?
Create a reference point, set the radius to 10,000 metres, and filter with STDistance(). For SRID 4326, STDistance() returns metres, so GeoPoint.STDistance(@ReferencePoint) <= 10000 returns everything within 10 km. Both points must share the same SRID, or STDistance() returns NULL and rows drop out silently.
5. Can SQL Server detect swapped latitude and longitude?
No. A swapped pair such as 67.0011, 24.8607 for Karachi passes every format and range check and builds a valid point in the wrong place. Catching it needs business context, such as an expected service region or a trusted site record. Don’t auto-swap values, because an unusual but correct location could be corrupted.
This document contains proprietary information and is protected by copyright law.
Copyright © 2026 Red Gate Software Limited. All rights reserved
Load comments