SQL Server 2008: Spatial Query - Return 5 closest sites

This might help you Well, I managed to do it in a CLR user defined function.
It is not quite a "substring" method per se, but I needed to subdivide a LINESTRING into multiple LINESTRINGs each of either X units in length or as short as the subpoints forming the LINESTRING segment were (it is not very clear, I know!)

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using Microsoft.SqlServer.Types;
using System.Collections;

public partial class UserDefinedFunctions
{
    /// <summary>
    /// Take a LINESTRING and return a sub LINESTRING from it given the
    /// starting point and the distance to move
    /// </summary>
    /// <param name="inputLine"></param>
    /// <param name="divideEveryDistance"></param>
    /// <returns></returns>
    #region DivideLineString
    [Microsoft.SqlServer.Server.SqlFunction(FillRowMethodName = "FillDivideLineStringRow", TableDefinition = "segment geography")]
    public static IEnumerable DivideLineString(Microsoft.SqlServer.Types.SqlGeography inputLine, double divideEveryDistance)
    {
        // ArrayList to hold the resulting rows
        ArrayList resultCollection = new ArrayList();

        // Check that the input geography is a LINESTRING
        if (!inputLine.InstanceOf("LINESTRING"))
            throw new ArgumentException("This operation may only be executed on LineString instances.");

        // If the input distance is less than or equal zero
        // just return the original linestring
        if (divideEveryDistance <= 0)
        {
            resultCollection.Add(inputLine);
            return resultCollection;
        }

        // Builder to hold the aggregated LINESTRING
        SqlGeographyBuilder subLinestringBuilder;

        // Initialize the starting point to the start point of the input LINESTRING
        SqlGeography startPoint = inputLine.STStartPoint();
        SqlGeography currentPoint = null;

        // Initialize the starting index to the first point on the input LINESTRING
        int currentPointIndex = 1;

        // Loop on all the points on the input LINESTRING
        while (currentPointIndex < inputLine.STNumPoints())
        {
            // Initialize the builder
            subLinestringBuilder = new SqlGeographyBuilder();
            subLinestringBuilder.SetSrid(4326);
            subLinestringBuilder.BeginGeography(OpenGisGeographyType.LineString);

            // Start with the starting point of the line
            subLinestringBuilder.BeginFigure((double)startPoint.Lat, (double)startPoint.Long);

            // Distance traversed accumulator
            double currentDistance = 0;

            // While we didn't cover the required divide distance and we're still within the boundaries of the input LINESTRING
            while (currentDistance < divideEveryDistance && currentPointIndex < inputLine.STNumPoints())
            {
                // Calculate the distance between the startPoint and the nth point
                currentPoint = inputLine.STPointN(currentPointIndex);
                currentDistance = (double)startPoint.STDistance(currentPoint);

                // Add the currentPoint to the subLineString
                subLinestringBuilder.AddLine((double)currentPoint.Lat, (double)currentPoint.Long);

                // Visit the next point
                currentPointIndex++;
            }

            // We covered the required divide distance,
            // Move on to the next segment of the line
            if (currentPoint != null)
                // Set the startpoint of the next segment to be the last point we visited
                startPoint = SqlGeography.Point((double)currentPoint.Lat, (double)currentPoint.Long, 4326);

            // If we reached the end of the LINESTRING, create a segment between the last point
            // we visited and the end point of the LINESTRING
            if (currentPointIndex >= inputLine.STNumPoints())
            {
                // Add the endpoint of the original linestring
                subLinestringBuilder.AddLine((double)inputLine.STEndPoint().Lat, (double)inputLine.STEndPoint().Long);
            }

            // End the current line segment
            subLinestringBuilder.EndFigure();
            subLinestringBuilder.EndGeography();

            // Add the row to the result collection
            resultCollection.Add(subLinestringBuilder.ConstructedGeography);
        }

        // We're done, return the table
        return resultCollection;
    } 
    #endregion

    /// <summary>
    /// Method required to fill the table-valued function
    /// </summary>
    /// <param name="obj"></param>
    /// <param name="geography"></param>
    #region FillDivideLineStringRow
    private static void FillDivideLineStringRow(Object obj, out SqlGeography geography)
    {
        geography = (SqlGeography)obj;
    } 
    #endregion
};

I hope this helps . I am working on a project that maintains an SQL database. In this database I have a table called AirportZone with an attribute named "area" of type geometry. Also, I maintain a table OfferRequest with an attribute named "location" of type geography. Due to the fact that I want to see if an OfferRequest occured in an Airport area, I want to define a complex SQL query that will return all the requests that were made from a user in a certain area. Something like: , The above query can be implemented as:

DECLARE @tmp_geo geometry;
SET @tmp_geo = geography::STGeomFromText(CAST(AirportZone.area as varchar(max));
SELECT OfferRequest.offer_id FROM OfferRequest, AirportZone WHERE
@tmp_geo.STIntersects(OfferRequest.location) = 1 AND AirportZone.name = 'given_name';

wish of those help I strongly encourage you to take a closer look at the geospatial features in SQL Server instead of asking for the actual solution. It is a powerful feature but might decrease performance if used in the wrong way.
The following query should do what you want:

DECLARE @center GEOGRAPHY

SET @center = geography::Point(46.969345, 8.592703, 4326)

SELECT TOP 10
    [Physical_Address_Street]
    , [Physical_Address_Local]
    , [Physical_Address_State]
    , [Physical_Address_Zip]
    , [Lat]
    , [Long]
    , [Phone_Number]
FROM Gas_Stations
WHERE Location_Type = 1
ORDER BY @center.STDistance(Location) ASC

may help you . Late answer, but if it still helps (or for anyone else) you should be able to do the following with SQL 2008.

DECLARE @point GEOMETRY = GEOMETRY::STPointFromText('POINT(0 0)', 0);
DECLARE @line GEOMETRY = GEOMETRY::STLineFromText('POINT(10 10, 20 20)', 0);

SELECT STIntersection(@point.STBuffer(@point.STDistance(@line)));