logo
down
shadow

SQL Server 2008: Spatial Query - Return 5 closest sites


SQL Server 2008: Spatial Query - Return 5 closest sites

Content Index :

SQL Server 2008: Spatial Query - Return 5 closest sites
Tag : sql-server-2008 , By : jan
Date : November 23 2020, 01:01 AM

I think the issue was by ths following , Try using the RANK function. I can't test since I don't have sample data so this might need a tweak but should be close...
;WITH cteDistances AS (
    SELECT 
         origin.Label AS OriginId
        ,dest.Label AS DestinationId
        ,origin.SP_Geometry.STDistance(dest.SP_Geometry) AS Distance
        ,RANK() OVER (PARTITION BY origin.Label ORDER BY origin.SP_Geometry.STDistance(dest.SP_Geometry)) AS DistanceRank
    FROM 
        SiteTable origin
        INNER JOIN SiteTable dest ON (dest.Label <> origin.Label)
    WHERE 
        origin.SP_Geometry.STDistance(dest.SP_Geometry) < 2500
)       

SELECT 
    OriginId, DestinationId, Distance
FROM 
    cteDistances 
WHERE 
    DistanceRank <= 5

Comments
No Comments Right Now !

Boards Message :
You Must Login Or Sign Up to Add Your Comments .

Share : facebook icon twitter icon

How can I return a substring of a LINESTRING in SQL Server 2008 Spatial?


Tag : sql-server , By : Lathentar
Date : March 29 2020, 07:55 AM
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
};

SQL Server 2008 spatial data select query


Tag : sql-server-2008 , By : PatrickSimonHenk
Date : March 29 2020, 07:55 AM
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';

I need a spatial query for SQL Server to return the ten closest locations


Tag : vb.net , By : Debashree
Date : March 29 2020, 07:55 AM
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

Closest Point on a Line in SQL Server Spatial 2008 R2


Tag : development , By : beng
Date : March 29 2020, 07:55 AM
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)));

SQL Server 2008 Spatial Query performance


Tag : sql-server-2008 , By : Jonathan Bernard
Date : March 29 2020, 07:55 AM
Related Posts Related QUESTIONS :
shadow
Privacy Policy - Terms - Contact Us © scrbit.com