{"id":113405,"date":"2026-11-20T12:00:00","date_gmt":"2026-11-20T12:00:00","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=113405"},"modified":"2026-10-22T13:35:54","modified_gmt":"2026-10-22T13:35:54","slug":"validate-gps-coordinates-sql-server-geography","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/validate-gps-coordinates-sql-server-geography\/","title":{"rendered":"How to validate GPS coordinates in SQL Server"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\" id=\"h-\"><strong>TL;DR:<\/strong> <strong>To validate GPS coordinates in SQL Server, stage the raw latitude and longitude as text, check each value&#8217;s format and range, log failures with a rejection reason, and only then pass the accepted pairs to <code>geography::Point()<\/code>. <\/strong><\/p>\n\n\n\n<p class=\"wp-block-paragraph\" id=\"h-\">This stops malformed values, such as the word &#8220;north&#8221;, a blank longitude or a latitude of 95, from breaking your load, and it lets you run distance queries on clean spatial data.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\" id=\"h-\">This article follows 12 fictional service locations from raw input to a &#8220;within 10 km&#8221; search. It also shows the one thing numeric validation can&#8217;t catch: a swapped latitude and longitude that passes every check.<\/p>\n\n\n\n<div id=\"callout-block_e5c4740e20a5afe52191d42f4008b712\" class=\"callout alignnone\">\n    <div class=\"child-last:mb-0 child-first:mt-0 bg-gray-50 dark:bg-gray-950 p-4xl my-3xl\">\n\n<p class=\"wp-block-paragraph\"><strong>In brief:<\/strong><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">&#8211; Stage the raw coordinates as <code>nvarchar<\/code> so nothing is lost or rounded before validation.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">&#8211; Normalize each value by trimming spaces and turning empty strings into <code>NULL<\/code>.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">&#8211; Check the format, then convert with <code>TRY_CONVERT()<\/code> to <code>decimal(9,6)<\/code> for latitude and <code>decimal(10,6)<\/code> for    longitude.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">&#8211; Check ranges (latitude -90 to 90, longitude -180 to 180) and assign one rejection code per failed row.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">&#8211; Build <code>geography<\/code> points from accepted rows only, in a separate statement.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">&#8211; Keep rejected rows with their original text and query accepted points with <code>STDistance()<\/code>.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Read on for the full guide, including code.<\/strong><\/p>\n\n<\/div>\n<\/div> \n\n\n<h2 id=\"h-why-bad-coordinates-break-geography-point-loads\" class=\"wp-block-heading\">Why bad coordinates break <code>geography::Point()<\/code> loads<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">A list of service locations comes in with a name, <strong><a href=\"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/introduction-sql-server-spatial-data\/\" target=\"_blank\" rel=\"noreferrer noopener\">latitude, and longitude<\/a><\/strong> for each entry. <em>Most<\/em> of the coordinates look fine&#8230;but not all. One entry, for example, contains the word &#8220;north&#8221; instead of a latitude. Another has a blank longitude. And a third entry has a latitude of 95 degrees. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">This is a problem, as the valid rows still need to be loaded to be used for searches &#8211; such as finding locations within 10 km of a customer.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Passing the raw values directly to <code>geography::Point()<\/code> 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.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">This article follows twelve fictional records from the raw input through validation and, finally, to a <strong>distance query<\/strong>. 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 <strong><a href=\"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/\" target=\"_blank\" rel=\"noreferrer noopener\">SQL Server<\/a><\/strong> geography values. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">One deliberately swapped pair, meanwhile, is kept in the &#8216;accepted&#8217; set. This is to demonstrate an important limitation: <strong>numeric validation alone cannot prove that a location is correct<\/strong>.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The sample coordinates are assumed to use <strong><a href=\"https:\/\/gisgeography.com\/wgs84-world-geodetic-system\/\" target=\"_blank\" rel=\"noreferrer noopener\">World Geodetic System (WGS) 84<\/a><\/strong>, represented here by <code>SRID 4326<\/code>. The <strong><a href=\"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/introduction-sql-server-spatial-data\/#:~:text=two%2Ddimensional%20spaces.-,Geography,-%3A%20Stores%20data\" target=\"_blank\" rel=\"noreferrer noopener\">geography data type<\/a><\/strong> is appropriate for this example because the coordinates represent locations on the Earth. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The geometry type represents data in a flat <strong><a href=\"https:\/\/en.wikipedia.org\/wiki\/Euclidean_geometry\" target=\"_blank\" rel=\"noreferrer noopener\">Euclidean<\/a><\/strong> coordinate system &#8211; with the choice between them depending on the coordinate system being used and the<strong> <a href=\"https:\/\/learn.microsoft.com\/en-us\/sql\/relational-databases\/spatial\/spatial-data-sql-server?view=sql-server-ver17\" target=\"_blank\" rel=\"noreferrer noopener\"><em>type<\/em> of calculation required<\/a><\/strong>.<\/p>\n\n\n\n<h2 id=\"h-the-test-environment-and-table-design\" class=\"wp-block-heading\">The test environment and table design<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>I tested the examples on <a href=\"https:\/\/www.microsoft.com\/en-us\/sql-server\/sql-server-2022\" target=\"_blank\" rel=\"noreferrer noopener\">SQL Server 2022<\/a> Developer Edition, build 16.0.1000.6, using a <code>GPSGeographyDemo<\/code> database at compatibility level 160.<\/strong><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The example uses three tables, each with a different job:<\/p>\n\n\n<div class=\"block-core-list\">\n<ul class=\"wp-block-list\">\n<li><code><strong>dbo.GPSImportStage<\/strong><\/code> keeps the coordinate values exactly as they arrived.<br><br><\/li>\n\n\n\n<li><code><strong>dbo.ValidLocations<\/strong><\/code> stores the rows that pass validation, including their parsed latitude and longitude values and the final geography point. <br><br><\/li>\n\n\n\n<li><code><strong>dbo.GPSImportRejects<\/strong><\/code> stores rows that fail validation together with the reason for rejection.<\/li>\n<\/ul>\n<\/div>\n\n\n<p class=\"wp-block-paragraph\">The two coordinate columns in the staging table use <code>nvarchar<\/code> deliberately.<\/p>\n\n\n\n<h2 id=\"h-step-1-stage-raw-coordinates-as-text\" class=\"wp-block-heading\">Step 1: Stage raw coordinates as text<\/h2>\n\n\n\n<h4 id=\"h-the-raw-staging-table\" class=\"wp-block-heading\">The raw staging table<\/h4>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:tsql decode:true \">CREATE TABLE dbo.GPSImportStage\n(\n    ImportRowID int NOT NULL,\n    LocationName nvarchar(100) NOT NULL,\n    RawLatitude nvarchar(50) NULL,\n    RawLongitude nvarchar(50) NULL,\n    LoadedAt datetime2(0) NOT NULL\n        CONSTRAINT DF_GPSImportStage_LoadedAt\n        DEFAULT (SYSUTCDATETIME()),\n\n    CONSTRAINT PK_GPSImportStage\n        PRIMARY KEY CLUSTERED (ImportRowID)\n);<\/pre><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">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. <strong>Keeping the incoming values as text <\/strong>means the validation process can work with the original input.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">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. <strong>Once part of the original value has been lost, the validator cannot recover it.<\/strong><\/p>\n\n\n\n<h3 id=\"h-the-12-row-sample-batch\" class=\"wp-block-heading\">The 12-row sample batch<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">The sample batch contains the following rows:<\/p>\n\n\n\n<h4 id=\"h-the-original-sample-batch\" class=\"wp-block-heading\">The original sample batch<\/h4>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><thead><tr><td><strong>ID<\/strong><\/td><td><strong>Location<\/strong><\/td><td><strong>Raw latitude<\/strong><\/td><td><strong>Raw longitude<\/strong><\/td><\/tr><\/thead><tbody><tr><td>1<\/td><td>London Central<\/td><td>51.5074<\/td><td>-0.1278<\/td><\/tr><tr><td>2<\/td><td>Camden Service Point<\/td><td>51.5416<\/td><td>-0.1433<\/td><\/tr><tr><td>3<\/td><td>Greenwich Service Point<\/td><td>51.4826<\/td><td>0.0077<\/td><\/tr><tr><td>4<\/td><td>Heathrow Depot<\/td><td>51.4700<\/td><td>-0.4543<\/td><\/tr><tr><td>5<\/td><td>Manchester Depot<\/td><td>53.4808<\/td><td>-2.2426<\/td><\/tr><tr><td>6<\/td><td>Missing Latitude<\/td><td>NULL<\/td><td>-0.1180<\/td><\/tr><tr><td>7<\/td><td>Blank Longitude<\/td><td>51.5150<\/td><td>(three spaces)<\/td><\/tr><tr><td>8<\/td><td>Bad Latitude Text<\/td><td>north<\/td><td>-0.1410<\/td><\/tr><tr><td>9<\/td><td>Bad Longitude Text<\/td><td>51.5200<\/td><td>west<\/td><\/tr><tr><td>10<\/td><td>Latitude Out of Range<\/td><td>95.0000<\/td><td>-0.1278<\/td><\/tr><tr><td>11<\/td><td>Longitude Out of Range<\/td><td>51.5074<\/td><td>190.0000<\/td><\/tr><tr><td>12<\/td><td>Karachi &#8211; Swapped Example<\/td><td>67.0011<\/td><td>24.8607<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">In the sample batch, Row 6 contains a database <code>NULL<\/code>, and Row 7 contains three ordinary spaces in the longitude field.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><code>ImportRowID<\/code>, meanwhile, identifies the incoming source row. When a row is accepted, that value is copied into <code>SourceImportRowID<\/code>. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><code>LocationID<\/code> is a separate identity value generated by <code>dbo.ValidLocations<\/code>. <code>SourceImportRowID<\/code> then provides the link <em>back<\/em> to the original staged row, while <code>LocationID<\/code> identifies the accepted location inside the destination table.<\/p>\n\n\n\n<h2 id=\"h-step-2-create-tables-for-accepted-and-rejected-rows\" class=\"wp-block-heading\">Step 2: Create tables for accepted and rejected rows<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">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.<\/p>\n\n\n\n<h4 id=\"h-the-output-tables\" class=\"wp-block-heading\">The output tables<\/h4>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:tsql decode:true \">CREATE TABLE dbo.ValidLocations\n(\n    LocationID int IDENTITY(1,1) NOT NULL,\n    LocationName nvarchar(100) NOT NULL,\n    Latitude decimal(9,6) NOT NULL,\n    Longitude decimal(10,6) NOT NULL,\n    GeoPoint geography NOT NULL,\n    SourceImportRowID int NOT NULL,\n\n    CONSTRAINT PK_ValidLocations\n        PRIMARY KEY CLUSTERED (LocationID),\n\n    CONSTRAINT UQ_ValidLocations_SourceImportRowID\n        UNIQUE (SourceImportRowID)\n);\n\nCREATE TABLE dbo.GPSImportRejects\n(\n    ImportRowID int NOT NULL,\n    LocationName nvarchar(100) NOT NULL,\n    RawLatitude nvarchar(50) NULL,\n    RawLongitude nvarchar(50) NULL,\n    RejectCode varchar(40) NOT NULL,\n    RejectReason nvarchar(250) NOT NULL,\n    RejectedAt datetime2(0) NOT NULL\n        CONSTRAINT DF_GPSImportRejects_RejectedAt\n        DEFAULT (SYSUTCDATETIME()),\n\n    CONSTRAINT PK_GPSImportRejects\n        PRIMARY KEY CLUSTERED (ImportRowID)\n);<\/pre><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">Here, <code>dbo.ValidLocations<\/code> gives each accepted location its own <code>LocationID<\/code>, while <code>SourceImportRowID<\/code> keeps the link back to the staged row. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The unique <strong><a href=\"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/constraint-yourself\/\" target=\"_blank\" rel=\"noreferrer noopener\">constraint<\/a><\/strong> on <code>SourceImportRowID<\/code> also prevents the same source row from being loaded into the accepted table more than once.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><code>dbo.GPSImportRejects<\/code> uses the original <code>ImportRowID<\/code> as its <strong><a href=\"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/learn\/primary-key-primer-for-sql-server\/\" target=\"_blank\" rel=\"noreferrer noopener\">primary key<\/a><\/strong> and keeps the raw latitude and longitude text together with the rejection code and reason. This makes it possible to investigate a failed row <em>without<\/em> losing the source values that caused the rejection.<\/p>\n\n\n\n<h2 id=\"h-step-3-parse-latitude-and-longitude-safely-with-try-convert\" class=\"wp-block-heading\">Step 3: Parse latitude and longitude safely with <code>TRY_CONVERT()<\/code><\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Before parsing the coordinates, the feed needs a clear format rule. In this example, a coordinate may begin with an optional + or &#8211; sign, followed by <strong><a href=\"https:\/\/www.ascii-code.com\/\" target=\"_blank\" rel=\"noreferrer noopener\">ASCII (American Standard Code for Information Interchange)<\/a><\/strong> digits. <\/p>\n\n\n\n<h3 id=\"h-coordinate-format-rules-for-this-feed-ascii\" class=\"wp-block-heading\">Coordinate format rules for this feed (ASCII)<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">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.)<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Ordinary spaces, meanwhile, are allowed at the beginning or end of the value &#8211; but tabs, commas, scientific notation, degree symbols, and embedded spaces are <em>not<\/em> accepted in any form.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">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.<\/p>\n\n\n\n<h3 id=\"h-normalize-values-with-trim-and-nullif\" class=\"wp-block-heading\">Normalize values with <code>TRIM()<\/code> and <code>NULLIF()<\/code><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong><a href=\"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/database-normalization-basics\/\" target=\"_blank\" rel=\"noreferrer noopener\">Normalization<\/a><\/strong> begins by removing ordinary spaces from both ends of the value. If nothing remains, <code>NULLIF()<\/code> turns the empty string into <code>NULL<\/code>.<\/p>\n\n\n\n<h4 id=\"h-normalize-latitude-for-validation\" class=\"wp-block-heading\">Normalize latitude for validation<\/h4>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:tsql decode:true \">NULLIF(\n    TRIM(RawLatitude) COLLATE Latin1_General_100_BIN2,\n    N''\n) AS LatitudeText<\/pre><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">The longitude uses the same expression. By default, <code>TRIM()<\/code> removes ordinary <code>char(32)<\/code> spaces from the beginning and end of the <strong><a href=\"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/find-and-replace-text-in-strings-in-t-sql\/\" target=\"_blank\" rel=\"noreferrer noopener\">string<\/a><\/strong>. 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.)<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">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 <em>only<\/em> digits, have one decimal point at most, and contain no more than six digits after the decimal point. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The <code><a href=\"https:\/\/www.sommarskog.se\/collations.html#:~:text=instance%20Latin1_General_100_BIN%20and-,Latin1_General_100_BIN2,-.%20Due%20to%20legacy\" target=\"_blank\" rel=\"noreferrer noopener\"><strong>Latin1_General_100_BIN2<\/strong><\/a><\/code> collation makes these checks follow the ASCII-based rules defined for this feed instead of relying on linguistic comparison rules.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">After a value passes the format checks, <code>TRY_CONVERT()<\/code> attempts to convert latitude to decimal(9,6) and longitude to decimal(10,6). <code>TRY_CONVERT()<\/code> the returns <code>NULL<\/code> if a value <em>cannot<\/em> be converted to the required decimal type. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">This allows the validation logic to handle the row instead of stopping the import.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The six-decimal-place rule also prevents conversion from hiding a problem, as <strong><a href=\"https:\/\/www.red-gate.com\/simple-talk\/databases\/theory-and-design\/rounding-numbers\/\" target=\"_blank\" rel=\"noreferrer noopener\">SQL Server can round a value when converting it to a type with a smaller scale<\/a><\/strong>. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">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.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">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.<\/p>\n\n\n\n<section id=\"my-first-block-block_d96b4507e1571cbefdb8218627b576ae\" class=\"my-first-block alignwide\">\n    <div class=\"bg-brand-600 text-base-white py-5xl px-4xl rounded-sm bg-gradient-to-r from-brand-600 to-brand-500 red\">\n        <div class=\"gap-4xl items-start md:items-center flex flex-col md:flex-row justify-between\">\n            <div class=\"flex-1 col-span-10 lg:col-span-7\">\n                <h3 class=\"mt-0 font-display mb-2 text-display-sm\">Fast, reliable and consistent SQL Server development&#8230;<\/h3>\n                <div class=\"child:last-of-type:mb-0\">\n                                            &#8230;with SQL Toolbelt Essentials. 10 ingeniously simple tools for accelerating development, reducing risk, and standardizing workflows.                                    <\/div>\n            <\/div>\n                                            <a href=\"https:\/\/www.red-gate.com\/products\/sql-toolbelt-essentials\/\" class=\"btn btn--secondary btn--lg\" aria-label=\"Learn more &amp; try for free: Fast, reliable and consistent SQL Server development...\">Learn more &amp; try for free<\/a>\n                    <\/div>\n    <\/div>\n<\/section>\n\n\n<h2 id=\"h-step-4-validate-ranges-and-assign-rejection-codes\" class=\"wp-block-heading\">Step 4: Validate ranges and assign rejection codes<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>The next query brings the parsing rules together and assigns one <a href=\"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/validation-verification-modification\/\" target=\"_blank\" rel=\"noreferrer noopener\">validation<\/a> result to each staged row.<\/strong><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">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.<\/p>\n\n\n\n<h4 id=\"h-preview-the-complete-validation-result\" class=\"wp-block-heading\">Preview the complete validation result<\/h4>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:tsql decode:true \">;WITH Normalized AS\n(\n    SELECT\n        ImportRowID,\n        LocationName,\n        RawLatitude,\n        RawLongitude,\n        NULLIF(TRIM(RawLatitude) COLLATE Latin1_General_100_BIN2, N'')\n            AS LatitudeText,\n        NULLIF(TRIM(RawLongitude) COLLATE Latin1_General_100_BIN2, N'')\n            AS LongitudeText\n    FROM dbo.GPSImportStage\n),\nCoordinateBodies AS\n(\n    SELECT\n        *,\n        CASE WHEN LEFT(LatitudeText, 1) IN (N'+', N'-')\n             THEN SUBSTRING(LatitudeText, 2, 50)\n             ELSE LatitudeText\n        END AS LatitudeBody,\n        CASE WHEN LEFT(LongitudeText, 1) IN (N'+', N'-')\n             THEN SUBSTRING(LongitudeText, 2, 50)\n             ELSE LongitudeText\n        END AS LongitudeBody\n    FROM Normalized\n),\nParsed AS\n(\n    SELECT\n        *,\n        CASE\n            WHEN LatitudeBody LIKE N'[0-9]%'\n             AND RIGHT(LatitudeBody, 1) LIKE N'[0-9]'\n             AND LatitudeBody NOT LIKE N'%[^0-9.]%'\n             AND LatitudeBody NOT LIKE N'%.%.%'\n             AND (CHARINDEX(N'.', LatitudeBody) = 0\n                  OR LEN(LatitudeBody) - CHARINDEX(N'.', LatitudeBody) &lt;= 6)\n            THEN TRY_CONVERT(decimal(9,6), LatitudeText)\n        END AS ParsedLatitude,\n        CASE\n            WHEN LongitudeBody LIKE N'[0-9]%'\n             AND RIGHT(LongitudeBody, 1) LIKE N'[0-9]'\n             AND LongitudeBody NOT LIKE N'%[^0-9.]%'\n             AND LongitudeBody NOT LIKE N'%.%.%'\n             AND (CHARINDEX(N'.', LongitudeBody) = 0\n                  OR LEN(LongitudeBody) - CHARINDEX(N'.', LongitudeBody) &lt;= 6)\n            THEN TRY_CONVERT(decimal(10,6), LongitudeText)\n        END AS ParsedLongitude\n    FROM CoordinateBodies\n)\nSELECT\n    ImportRowID,\n    LocationName,\n    RawLatitude,\n    RawLongitude,\n    ParsedLatitude,\n    ParsedLongitude,\n    CASE\n        WHEN LatitudeText IS NULL\n            THEN 'MISSING_LATITUDE'\n        WHEN LongitudeText IS NULL\n            THEN 'MISSING_LONGITUDE'\n        WHEN ParsedLatitude IS NULL\n            THEN 'INVALID_LATITUDE_FORMAT'\n        WHEN ParsedLongitude IS NULL\n            THEN 'INVALID_LONGITUDE_FORMAT'\n        WHEN ParsedLatitude NOT BETWEEN -90 AND 90\n            THEN 'LATITUDE_OUT_OF_RANGE'\n        WHEN ParsedLongitude NOT BETWEEN -180 AND 180\n            THEN 'LONGITUDE_OUT_OF_RANGE'\n        ELSE 'ACCEPT'\n    END AS ValidationResult\nFROM Parsed\nORDER BY ImportRowID;<\/pre><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">The order of the final <code>CASE<\/code> 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 <strong>first matching rule determines the rejection code<\/strong>.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">For example, a row containing latitude 95 and longitude west is classified as <code>INVALID_LONGITUDE_FORMAT<\/code>. The latitude can be converted successfully but the longitude cannot. Both format checks are evaluated <em>before<\/em> either range check.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The format codes can also apply to any numeric text that doesn&#8217;t fit the selected decimal type. <code>decimal(9,6)<\/code> allows nine digits in total, with six after the decimal point (leaving three digits before it.)<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">A latitude such as 1000, however, <em>cannot<\/em> fit that type &#8211; so <code>TRY_CONVERT()<\/code> returns <code>NULL<\/code>, and the row is classified as <code>INVALID_LATITUDE_FORMAT<\/code>.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">A value such as 95, on the other hand, converts successfully and is rejected later by the range check. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Therefore, in this example, a format error doesn&#8217;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.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Latitude must be between -90 and 90, inclusive<\/strong>. For this feed, longitude must be between -180 and 180, inclusive. These limits are checked before any geography value is created.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The import, therefore, doesn&#8217;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 <code><a href=\"https:\/\/learn.microsoft.com\/en-us\/sql\/relational-databases\/spatial\/point?view=sql-server-ver17\" target=\"_blank\" rel=\"noreferrer noopener\"><strong>LONGITUDE_OUT_OF_RANGE<\/strong><\/a><\/code>.<\/p>\n\n\n\n<h3 id=\"h-row-12-valid-numbers-wrong-place\" class=\"wp-block-heading\">Row 12: valid numbers, wrong place<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">The 12 staged rows produce the following results:<\/p>\n\n\n\n<h4 id=\"h-validation-results-from-the-sample-batch\" class=\"wp-block-heading\">Validation results from the sample batch<\/h4>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><thead><tr><td><strong>Import Row ID<\/strong><\/td><td><strong>Validation Result<\/strong><\/td><\/tr><\/thead><tbody><tr><td>1, 2, 3, 4, 5, 12<\/td><td><code>ACCEPT<\/code><\/td><\/tr><tr><td>6<\/td><td><code>MISSING_LATITUDE<\/code><\/td><\/tr><tr><td>7<\/td><td><code>MISSING_LONGITUDE<\/code><\/td><\/tr><tr><td>8<\/td><td><code>INVALID_LATITUDE_FORMAT<\/code><\/td><\/tr><tr><td>9<\/td><td><code>INVALID_LONGITUDE_FORMAT<\/code><\/td><\/tr><tr><td>10<\/td><td><code>LATITUDE_OUT_OF_RANGE<\/code><\/td><\/tr><tr><td>11<\/td><td><code>LONGITUDE_OUT_OF_RANGE<\/code><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Row 12 shows a limit of this validation<\/strong>. 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 <code>ACCEPT<\/code>. However, none of these rules checks whether those coordinates actually correspond to the location name Karachi.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">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.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">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.<\/p>\n\n\n\n<h2 id=\"h-step-5-build-geography-points-from-accepted-rows-only\" class=\"wp-block-heading\">Step 5: Build geography points from accepted rows only<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">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 <code>#GPSDemoValidated<\/code>, and rejected rows are routed separately to <code>dbo.GPSImportRejects<\/code>.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Rows classified as <code>ACCEPT<\/code> are then copied into <code>#GPSDemoAccepted<\/code> before any geography value is constructed.<\/p>\n\n\n\n<h4 id=\"h-constrain-the-accepted-coordinate-pairs\" class=\"wp-block-heading\">Constrain the accepted coordinate pairs<\/h4>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:tsql decode:true \">CREATE TABLE #GPSDemoAccepted\n(\n    ImportRowID int NOT NULL PRIMARY KEY,\n    LocationName nvarchar(100) NOT NULL,\n    Latitude decimal(9,6) NOT NULL\n        CHECK (Latitude BETWEEN -90 AND 90),\n    Longitude decimal(10,6) NOT NULL\n        CHECK (Longitude BETWEEN -180 AND 180)\n);<\/pre><\/div>\n\n\n\n<h4 id=\"h-materialize-the-accepted-rows\" class=\"wp-block-heading\">Materialize the accepted rows<\/h4>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:tsql decode:true \">INSERT INTO #GPSDemoAccepted\n    (ImportRowID, LocationName, Latitude, Longitude)\nSELECT ImportRowID, LocationName, ParsedLatitude, ParsedLongitude\nFROM #GPSDemoValidated\nWHERE ValidationResult = 'ACCEPT';<\/pre><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">The <code>NOT NULL<\/code> and <code>CHECK<\/code> constraints reinforce the rules that were already applied during validation. At this point, <code>geography::Point()<\/code> no longer operates directly on the raw feed or on a query containing both valid and invalid rows. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Instead, it now operates on the <strong>materialized set of accepted coordinate pairs <em>only<\/em><\/strong>.<\/p>\n\n\n\n<h4 id=\"h-build-the-stored-geography-points\" class=\"wp-block-heading\">Build the stored geography points<\/h4>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:tsql decode:true \">INSERT INTO dbo.ValidLocations\n    (LocationName, Latitude, Longitude, GeoPoint, SourceImportRowID)\nSELECT\n    LocationName,\n    Latitude,\n    Longitude,\n    geography::Point(Latitude, Longitude, 4326),\n    ImportRowID\nFROM #GPSDemoAccepted;<\/pre><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">Keeping validation and construction in separate statements is deliberate. I think this approach also makes the logic easier to follow, as a <strong><a href=\"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/sql-server-cte-basics\/\" target=\"_blank\" rel=\"noreferrer noopener\">common table expression (CTE)<\/a> <\/strong>is not a materialized result, and SQL Server doesn&#8217;t guarantee that predicates or expressions will be evaluated in the order they appear in a query. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Therfore, putting a filter and <code>geography::Point()<\/code> in the same statement does <em>not<\/em> guarantee that invalid rows will always be removed before the constructor is evaluated. In this example, the accepted values have <a href=\"https:\/\/techcommunity.microsoft.com\/blog\/sqlserver\/predicate-ordering-is-not-guaranteed\/383075\" target=\"_blank\" rel=\"noreferrer noopener\"><strong>already been stored in <code>#GPSDemoAccepted<\/code> before the constructor runs<\/strong><\/a>.<\/p>\n\n\n\n<h3 id=\"h-latitude-and-longitude-order-point-vs-wkt\" class=\"wp-block-heading\">Latitude and longitude order: <code>Point()<\/code> vs WKT<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><code>geography::Point()<\/code> takes latitude, longitude, and <code>SRID<\/code> in that order. <strong><a href=\"https:\/\/en.wikipedia.org\/wiki\/Well-known_text_representation_of_geometry\" target=\"_blank\" rel=\"noreferrer noopener\">Well-known text (WKT)<\/a><\/strong> represents a point using the opposite coordinate order: longitude first, followed by latitude. The following two expressions describe the same point in London:<\/p>\n\n\n\n<h4 id=\"h-two-forms-of-the-same-reference-point\" class=\"wp-block-heading\">Two forms of the same reference point<\/h4>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:tsql decode:true \">DECLARE @ReferencePoint geography =\n    geography::Point(51.5074, -0.1278, 4326);\n\nDECLARE @ReferenceFromWKT geography =\n    geography::STGeomFromText(\n        N'POINT(-0.1278 51.5074)',\n        4326\n    );<\/pre><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">When the London point is displayed as WKT, it appears as <code>POINT (-0.1278 51.5074)<\/code>. In this format, <strong>longitude comes before latitude<\/strong>. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">I also ran a test using <code>STDistance()<\/code> between the <code>Point()<\/code> and WKT versions, and the result was zero. This confirms that both expressions describe the same location.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">I use <code>SRID 4326<\/code> in this example because the feed is assumed to contain WGS 84 coordinates. Assigning <code>SRID 4326<\/code> doesn&#8217;t transform coordinates from another reference system into WGS 84. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">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 <code>SRID 4326<\/code>.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The complete load wraps its output changes in a transaction with <code>XACT_ABORT<\/code> enabled, and a <code>TRY...CATCH<\/code> 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. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The successful sample load then produces six accepted rows and six rejected rows from the 12 staged records.<\/p>\n\n\n\n<h2 id=\"h-step-6-keep-the-rejected-rows-for-review\" class=\"wp-block-heading\">Step 6: Keep the rejected rows for review<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>A rejected row should keep enough information to explain what went wrong <em>and<\/em> to trace the result back to the original import.<\/strong> <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">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.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The raw coordinate strings are copied from staging <em>without<\/em> being normalized or rewritten. This is important because details such as <code>NULL<\/code>, an empty value, or whitespace can help explain what the source system <em>actually supplied<\/em>.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">To review the rejected rows after the load:<\/p>\n\n\n\n<h4 id=\"h-review-the-rejected-input\" class=\"wp-block-heading\">Review the rejected input<\/h4>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:tsql decode:true \">SELECT\n    ImportRowID,\n    LocationName,\n    RawLatitude,\n    RawLongitude,\n    RejectCode,\n    RejectReason\nFROM dbo.GPSImportRejects\nORDER BY ImportRowID;<\/pre><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">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. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Also, preserving the original input still lets us distinguish between a field that was <em>not<\/em> supplied, and one that arrived as whitespace.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">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 <strong><a href=\"https:\/\/www.red-gate.com\/simple-talk\/opinion\/editorials\/sql-server-management-studio-is-as-relevant-as-ever\/\" target=\"_blank\" rel=\"noreferrer noopener\">SQL Server Management Studio (SSMS)<\/a><\/strong> results grid.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The two output tables enforce uniqueness separately, but the schema doesn&#8217;t actually contain one single rule that <em>guarantees<\/em> every staged row appears in exactly one output table. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">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.<\/p>\n\n\n\n<h2 id=\"h-step-7-find-locations-within-10-km-with-stdistance\" class=\"wp-block-heading\">Step 7: Find locations within 10 km with <code>STDistance()<\/code><\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">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.<\/p>\n\n\n\n<h4 id=\"h-find-points-within-10-kilometres\" class=\"wp-block-heading\">Find points within 10 kilometres<\/h4>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:tsql decode:true \">DECLARE @ReferencePoint geography =\n    geography::Point(51.5074, -0.1278, 4326);\n\nDECLARE @RadiusMetres float = 10000.0;\n\nSELECT\n    v.SourceImportRowID,\n    v.LocationName,\n    CONVERT(decimal(12,3),\n        v.GeoPoint.STDistance(@ReferencePoint)) AS DistanceMetres,\n    CONVERT(decimal(10,3),\n        v.GeoPoint.STDistance(@ReferencePoint) \/ 1000.0) AS DistanceKm\nFROM dbo.ValidLocations AS v\nWHERE v.GeoPoint.STDistance(@ReferencePoint) &lt;= @RadiusMetres\nORDER BY\n    v.GeoPoint.STDistance(@ReferencePoint),\n    v.SourceImportRowID;<\/pre><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">For <code>SRID 4326<\/code>, <code>STDistance()<\/code> returns the distance in metres. The supporting checks also confirm the corresponding unit information in <code>sys.spatial_reference_systems<\/code>. Dividing the result by 1000.0 converts the displayed value to kilometres.<\/p>\n\n\n\n<h4 id=\"h-locations-returned-by-the-10km-query\" class=\"wp-block-heading\">Locations returned by the 10km query<\/h4>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><thead><tr><td><strong>Source ID<\/strong><\/td><td><strong>Location<\/strong><\/td><td><strong>Distance (m)<\/strong><\/td><td><strong>Distance (km)<\/strong><\/td><\/tr><\/thead><tbody><tr><td>1<\/td><td>London Central<\/td><td>0.000<\/td><td>0.000<\/td><\/tr><tr><td>2<\/td><td>Camden Service Point<\/td><td>3954.178<\/td><td>3.954<\/td><\/tr><tr><td>3<\/td><td>Greenwich Service Point<\/td><td>9806.393<\/td><td>9.806<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">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.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">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 &#8211; <em>not<\/em> the actual distance to the intended Karachi location!<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Put simply, a <strong>plausible numeric result does <em>not<\/em> prove that the source coordinates describe the correct place.<\/strong><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The radius comparison uses the original <code>STDistance()<\/code> value in metres. Rounding is applied only to the columns shown in the result set, while the <strong><a href=\"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/the-basic-t-sql-select-statement\/#the-where-clause:~:text=.SalesTerritory%3B-,The%20WHERE%20clause,-The%20SELECT%20statement\" target=\"_blank\" rel=\"noreferrer noopener\"><code>WHERE<\/code> clause<\/a><\/strong> and ordering use the <em>unrounded<\/em> distance. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">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.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><code>STDistance()<\/code> 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&#8217;t indicate the accuracy of the GPS.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">If the two geography values have different <code>SRID<\/code>s, <code>STDistance() <\/code>returns <code>NULL<\/code>. Because a comparison with <code>NULL<\/code> doesn&#8217;t satisfy the radius predicate, an <code>SRID<\/code> mismatch could cause a row to be excluded without appearing as an error. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The query therefore assumes that all points use the same reference system.<\/p>\n\n\n\n<h2 id=\"h-do-you-actually-need-a-spatial-index\" class=\"wp-block-heading\">Do you actually <em>need<\/em> a spatial index?<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">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 <em>not<\/em> include timing or logical-read comparisons.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">For a larger table, SQL Server can use a spatial index with an <code>STDistance()<\/code> predicate such as:<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><tbody><tr><td><code>GeoPoint.STDistance(@ReferencePoint) &lt;= @RadiusMetres<\/code><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">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.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">A spatial index also requires a <strong>clustered primary key<\/strong>. <code>dbo.ValidLocations<\/code> already has one on <code>LocationID<\/code>, although this example doesn&#8217;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. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>The actual execution plan should also be examined<\/strong>. A 10 km search in a dense city can behave very differently from a much broader search over the same table.<\/p>\n\n\n\n<h2 id=\"h-conclusion-what-validation-can-t-catch-swapped-coordinates\" class=\"wp-block-heading\">Conclusion: what validation <em>can&#8217;t<\/em> catch (swapped coordinates)<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">The sample import finishes with <strong>six accepted locations and six rejected rows<\/strong>. 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. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">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.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">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. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">That does <em>not<\/em> 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. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Furthermore, preserving the original source values makes it possible to investigate a suspicious row and apply a correction &#8211; but <strong>only when there is enough evidence to justify it.<\/strong><\/p>\n\n\n\n<section id=\"my-first-block-block_fd74507cdb2230fd71126edf7c66525f\" class=\"my-first-block alignwide\">\n    <div class=\"bg-brand-600 text-base-white py-5xl px-4xl rounded-sm bg-gradient-to-r from-brand-600 to-brand-500 red\">\n        <div class=\"gap-4xl items-start md:items-center flex flex-col md:flex-row justify-between\">\n            <div class=\"flex-1 col-span-10 lg:col-span-7\">\n                <h3 class=\"mt-0 font-display mb-2 text-display-sm\">Subscribe to the Simple Talk newsletter<\/h3>\n                <div class=\"child:last-of-type:mb-0\">\n                                            All the latest articles, guides, podcasts, and more &#8211; delivered straight to your inbox.                                    <\/div>\n            <\/div>\n                                            <a href=\"https:\/\/www.red-gate.com\/simple-talk\/subscribe\/\" class=\"btn btn--secondary btn--lg\" aria-label=\"Subscribe now: Subscribe to the Simple Talk newsletter\">Subscribe now<\/a>\n                    <\/div>\n    <\/div>\n<\/section>\n\n\n<section id=\"faq\" class=\"faq-block my-5xl\">\n    <h2>FAQs<\/h2>\n\n            <h3 class=\"mt-4xl\">1. How do you validate latitude and longitude in SQL Server?<\/h3>\n        <div class=\"faq-answer\">\n            <p dir=\"ltr\">Store the raw values as <code dir=\"ltr\">nvarchar<\/code>, trim spaces, and check the format with pattern rules before converting with <code dir=\"ltr\">TRY_CONVERT()<\/code> to <code dir=\"ltr\">decimal(9,6)<\/code> for latitude and <code dir=\"ltr\">decimal(10,6)<\/code> 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 <code dir=\"ltr\">geography::Point()<\/code>.<\/p>\n        <\/div>\n            <h3 class=\"mt-4xl\">2. Why does geography::Point() fail on some coordinates?<\/h3>\n        <div class=\"faq-answer\">\n            <p dir=\"ltr\">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&#8217;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.<\/p>\n        <\/div>\n            <h3 class=\"mt-4xl\">3. Does geography::Point() take latitude or longitude first?<\/h3>\n        <div class=\"faq-answer\">\n            <p dir=\"ltr\"><code dir=\"ltr\">geography::Point()<\/code> takes latitude, then longitude, then the SRID. Well-known text (WKT) reverses this, writing <code dir=\"ltr\">POINT(longitude latitude)<\/code>. <code dir=\"ltr\">geography::Point(51.5074, -0.1278, 4326)<\/code> and <code dir=\"ltr\">STGeomFromText(N'POINT(-0.1278 51.5074)', 4326)<\/code> describe the same London point, and <code dir=\"ltr\">STDistance()<\/code> between them returns zero.<\/p>\n        <\/div>\n            <h3 class=\"mt-4xl\">4. How do you find locations within 10 km in SQL Server?<\/h3>\n        <div class=\"faq-answer\">\n            <p dir=\"ltr\">Create a reference point, set the radius to 10,000 metres, and filter with <code dir=\"ltr\">STDistance()<\/code>. For SRID 4326, <code dir=\"ltr\">STDistance()<\/code> returns metres, so <code dir=\"ltr\">GeoPoint.STDistance(@ReferencePoint) &lt;= 10000<\/code> returns everything within 10 km. Both points must share the same SRID, or <code dir=\"ltr\">STDistance()<\/code> returns <code dir=\"ltr\">NULL<\/code> and rows drop out silently.<\/p>\n        <\/div>\n            <h3 class=\"mt-4xl\">5. Can SQL Server detect swapped latitude and longitude?<\/h3>\n        <div class=\"faq-answer\">\n            <p dir=\"ltr\">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&#8217;t auto-swap values, because an unusual but correct location could be corrupted.<\/p>\n        <\/div>\n    <\/section>\n","protected":false},"excerpt":{"rendered":"<p>Learn how to validate latitude and longitude in SQL Server, reject bad rows with reasons, build geography points safely and run a 10 km distance query.&hellip;<\/p>\n","protected":false},"author":347262,"featured_media":103263,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143527,143523,53,143524],"tags":[4168,4170,4150,4151],"coauthors":[159417],"class_list":["post-113405","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-database-administration-sql-server","category-databases","category-featured","category-sql-server","tag-database","tag-database-administration","tag-sql","tag-sql-server"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/113405","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/347262"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=113405"}],"version-history":[{"count":22,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/113405\/revisions"}],"predecessor-version":[{"id":113991,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/113405\/revisions\/113991"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media\/103263"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=113405"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=113405"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=113405"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=113405"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}