SQL Server 2019: Creating valid geography from problematic path data

Data and Databases
1

Hey folks, I’m having trouble with some path data in our database. We store routes as we get them from Google Maps. Most work fine but a few cause errors when we try to create geography objects.

The error says: ‘24200: The specified input does not represent a valid geography instance.’ I think it might be because these paths cross themselves.

Does anyone know how to generate geography objects from these tricky paths without errors? Here’s an example of the SQL code that’s giving me trouble:

DECLARE @path NVARCHAR(MAX) = 'LINESTRING(-87.8951 41.9507,-87.8968 41.9510,-87.8958 41.9520,...)';
SELECT geography::STGeomFromText(@path, 4269);

The actual path is much longer. Any suggestions would be appreciated. Thanks!

2

yo Lu_57Read, have u looked into the MakeValid() function? it can sometimes fix those tricky self-intersecting paths. might be worth a shot. also, double-check ur coordinate order - SQL Server expects (latitude, longitude) which is opposite of most systems. good luck mate!

3

I’ve encountered similar issues with problematic path data. One approach that’s worked well for me is using the STBuffer() function with a small value before attempting to create the geography object. This can help smooth out minor irregularities in the path.

Here’s an example of how you might modify your code:

DECLARE @path NVARCHAR(MAX) = 'LINESTRING(-87.8951 41.9507,-87.8968 41.9510,-87.8958 41.9520,...)';
DECLARE @geom geometry = geometry::STGeomFromText(@path, 4269);
SELECT geography::STGeomFromText(@geom.STBuffer(0.000001).STAsText(), 4269);

This method essentially creates a tiny buffer around your path, which can resolve self-intersections and other geometric inconsistencies. It’s not a perfect solution, but it’s been effective in many cases I’ve dealt with.

4

hey there! have you tried usin the STIsValid() function to check if ur paths are valid before creating geography objects? it might help pinpoint which paths are causing trouble. also, maybe try simplifying the paths first? like, reducing the number of points? just brainstorming here. what other approaches have u considered?