Search code examples
sql-serversql-server-2008sql-server-2008-r2spatialgeography

how to get the index of a point from geography?


How can i get the index of a point which is in my LineString?
I am using SQL Server 2008 R2.

I do have a geography type where a LineString is saved in.
I want now to get the index of two points on this LineString. So that I know which one occurs first.
Is this somehow possible?
Because right now i'm doing it for my self with a while loop... but it's really slow when i've got some more data in my database :/

EDIT: Ok, right now i'm trying to follow the solution from SQL to use CHARINDEX.
Some more background info:
I do have a geo point, I do have a linestring. I did get the intersecting points with a radius around my point from the linestring. Ok, now i want to try to get with the first intersecting point the index from this point on the LineString.
So i do have in my linestring some numbers like these patterns "1.123456 or 12.123456 or 123.123456" and my search point is also something like "1.123456 or 12.123456 or 123.123456"

The Problem is, that STIntersection gives me some different numbers back which are variable at the fractional digits. I thought about some string formatting, but i don't know how i should solve this. If there would be some nice regex features i think it would make my life easyier :) I had a look through all of these functions but couldn't find anything for my needs.

Maybe some more experienced people could help me with that.

Thanks!

Solution

  • In case the datatype is varchar used. Please see below.

    You can use CharIndex

    DECLARE @document varchar(64)

SELECT @document = 'abcdef12345wuerzelchen'
SELECT CHARINDEX('abc', @document)

    Once you have got the first occurrence point, Now you can check for another.

    Declare @position int
Set @position = CHARINDEX('abc', @document)    
SELECT CHARINDEX('wuerzelchen', @document, @position)

    For more information you can check here