Can someone help me debug this problem. It starts of running fine, but then it stops and gives the below message. I have been unable to find any records in the database that look incorrect. Is there any way to find out what record(s) is causing this problem.
DECLARE @g geography;
Select ParcelIDNumber, *, geography::STGeomFromText('POINT([TIGER Longitude] [TIGER Latitude])', 4326)
FROM [dbo].[EPC_Parcel_Data] EPC JOIN GeocodedAddresses GA
ON EPC.ParcelIDNumber = GA.[Record ID Number]
Where EPC.ParcelIDNumber > 0 and [TIGER Longitude] > '' and [TIGER Latitude] > ''
Msg 6522, Level 16, State 1, Line 143
A .NET Framework error occurred during execution of user-defined routine or aggregate "geography":
System.FormatException: 24141: A number is expected at position 12 of the input. The input has [TIGER.
System.FormatException:
at Microsoft.SqlServer.Types.WellKnownTextReader.RecognizeDouble()
at Microsoft.SqlServer.Types.WellKnownTextReader.ParsePointText(Boolean parseParentheses)
at Microsoft.SqlServer.Types.WellKnownTextReader.ParseTaggedText(OpenGisType type)
at Microsoft.SqlServer.Types.WellKnownTextReader.Read(OpenGisType type, Int32 srid)
at Microsoft.SqlServer.Types.SqlGeography.ParseText(OpenGisType type, SqlChars taggedText, Int32 srid)
at Microsoft.SqlServer.Types.SqlGeography.GeographyFromText(OpenGisType type, SqlChars taggedText, Int32 srid)
.
Completion time: 2024-03-27T13:12:20.9944615-06:00
March 28, 2024 at 12:43 pm
I've not used geography:, but perhaps this post is a similar issue?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
March 28, 2024 at 12:51 pm
From ChatGPT:
It seems like there's a formatting issue with your latitude and longitude values. The error message indicates that it's expecting a number but encountered something else at position 12 of the input. You should check the data in the columns [TIGER Longitude] and [TIGER Latitude] to ensure they contain valid numerical values. There might be records where these columns contain unexpected characters or are not properly formatted as numbers. You can try querying the data directly from the [dbo].[EPC_Parcel_Data] table and inspecting the values in those columns to identify any anomalies.
March 28, 2024 at 2:22 pm
To calculate the points using column values would require to concatenate the string. Afaik if there are bad values you could use TRY_CAST with the 'geography' data type
with geo_cte(lat, long) as (
select 10, 10 union all
select 20, 20 union all
select 30, 100000)
select --geography::STGeomFromText(N'POINT(lat, long)', 4326) not_like_this,
geography::STGeomFromText(concat(N'point(', lat, N' ', long, N')'), 4326) as point_geo
from geo_cte
where try_cast(concat(N'point(', lat, N' ', long, N')') as geography) is not null;
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
Guys,
Thanks so much for your replies that you sent, I found the problem with the formatting of the command to convert the lat/long to the geography data that I needed
OLD
Select ParcelIDNumber, *, geography::STGeomFromText('POINT([TIGER Longitude] [TIGER Latitude])', 4326)
NEW
Select ParcelIDNumber, *, geography::STGeomFromText('POINT(' + [TIGER Longitude] + ' ' + [TIGER Latitude] + ')', 4326)
Hope that this helps the next person that has this problem.
Mark
March 30, 2024 at 12:37 am
Thanks for posting your discovery, Mark.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply