Getting miles from Geography Stored Procedure

I'm new to using the Geography datatype, but I've figured out how to use it to get a list of zipcodes from a point (in the stored procedure I just have the point as varchar and I provide it with the "Longitude Latitude") so that was great.

However, the next thing I need to be able to show is roughly the miles from the point. I've looked at several answers and couldn't find anything.

Here's my stored procedure:

ALTER PROCEDURE [dbo].[GetZipCodesByDistance]
@point varchar(500),
@distance int
AS
BEGIN
SET NOCOUNT ON;

declare @geoPoint geography
set @geoPoint = geography::STGeomFromText('POINT (' + @point + ')', 4326)
                           .STBuffer(@distance / 0.0006213712)
SELECT   zc.zip_id AS ZipId
        ,zc.zip_code AS ZipCodeId
        ,zc.zip_type AS ZipType
        ,zc.zip_city AS ZipCity
        ,zc.zip_utc AS ZipUtc
        ,zc.zip_dst AS ZipDst
        ,zc.zip_latitude AS ZipLatitude
        ,zc.zip_longitude AS ZipLongitude,
        (Geo.MakeValid()).STDistance(@geoPoint)
FROM zip_code zc
WHERE zc.Geo.STIntersects(@geoPoint) = 1

So, my question is... how do I get it to return miles from the point? I tried to add the column "(Geo.MakeValid()).STDistance(@geoPoint)" but it returns all zeros.

Thank you for your help!

Upvotes: 1

Answers (4)

AJ Tatum

Reputation: 713

Alright, figured out a way to make this work.

The stored procedure looks like:

ALTER PROCEDURE [dbo].[GetZipCodesByDistance] @point VARCHAR(500),
@distance INT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @geoPoint GEOGRAPHY
SET @geoPoint = GEOGRAPHY::STGeomFromText('POINT (' + @point + ')', 4326);

DECLARE @searchArea GEOGRAPHY
SET @searchArea = @geoPoint.STBuffer(@distance / 0.0006213712)

DECLARE @ZipDistance TABLE(
ZipCode VARCHAR(5),
ZipLatitude DECIMAL(9,6),
ZipLongitude DECIMAL(9,6),
Distance DECIMAL(9,2)
)

INSERT INTO @ZipDistance
SELECT
        zc.zip_code AS ZipCode
        ,zc.zip_latitude AS ZipLatitude
        ,zc.zip_longitude AS ZipLongitude
        ,CAST((((Geo.MakeValid()).STDistance(@geoPoint))/1609.34) AS DECIMAL(9,2)) AS Distance
FROM zip_code zc
WHERE zc.Geo.STIntersects(@searchArea) = 1

SELECT * FROM @ZipDistance
END

Then I created a model:

public class ZipCodeWithDistance
{
    public string ZipCode { get; set; }
    public decimal ZipLatitude { get; set; }
    public decimal ZipLongitude { get; set; }
    public decimal Distance { get; set; }
}

In the DbContext, I have the following:

public List<ZipCodeWithDistance> GetZipCodes(string point, int distance)
{
    var zipCodes = this.Database.SqlQuery<ZipCodeWithDistance>(
    "GetZipCodesByDistance @point, @distance",
    new SqlParameter("point", point),
    new SqlParameter("distance", distance)
    ).ToList();

    return zipCodes;
}

In the FacilityModel, I have:

[NotMapped]
public decimal Distance { get; set; }

Finally, in the Service, I have the following:

public IQueryable<Facility>GetFacilitiesByZipCodes(List<ZipCodeWithDistance> zipCodes)
{
    var zipCodeName = zipCodes.Select(x => x.ZipCode).ToList();
    var facilities = oandpDbContext.Facilities.Where(x => zipCodeName.Contains(x.ZipCode.Substring(0, 5))).ToList();
    facilities.ForEach(x =>
    {
        var zip = zipCodes.FirstOrDefault(y => y.ZipCode == x.ZipCode.Substring(0, 5));
        if (zip != null)
        {
            x.Distance = zip.Distance;
        }
    });
return facilities.AsQueryable();
}

Not sure if it's the best/most efficient way to get this done, but it works. Any thoughts or comments welcomed! Thank you, AJ

Upvotes: 0

Damien_The_Unbeliever

Reputation: 239646

I'd suggest introducing a separate variable to hold the point versus your buffered area:

ALTER PROCEDURE [dbo].[GetZipCodesByDistance]
@point varchar(500),
@distance int
AS
BEGIN
SET NOCOUNT ON;

declare @geoPoint geography
set @geoPoint = geography::STGeomFromText('POINT (' + @point + ')', 4326);

declare @searchArea geography
set @searchArea = @geoPoint.STBuffer(@distance / 0.0006213712)

select  zc.zip_id AS ZipId
        ,zc.zip_code AS ZipCodeId
        ,zc.zip_type AS ZipType
        ,zc.zip_city AS ZipCity
        ,zc.zip_utc AS ZipUtc
        ,zc.zip_dst AS ZipDst
        ,zc.zip_latitude AS ZipLatitude
        ,zc.zip_longitude AS ZipLongitude,
        (Geo.MakeValid()).STDistance(@geoPoint)
        FROM zip_code zc
where zc.Geo.STIntersects(@searchArea) = 1

I tried to add the column "(Geo.MakeValid()).STDistance(@geoPoint)" but it returns all zeros.

Previously you were computing the distance between the found points and the search area - which is of course 0 since we've already established (via STIntersects) that they're within the area. So I've changed things around so that the STDistance call is using the point rather than the area.

(Of course, I'd also recommend, if at all possible that the parameter of the stored procedure be changed so that you're passed an already constructed geography rather than a string. But that may not always be possible)

Upvotes: 0

Silly John

Reputation: 1704

I still did not understand what actually is your requirement. We have a similar requirement to find distance between zip codes. See a sample function that does the work

CREATE FUNCTION Wrk.ZipDistance
(
    @Zip1 VARCHAR(20)
    ,@Zip2 VARCHAR(20)
)
RETURNS FLOAT
AS
BEGIN
    DECLARE @LatLong1 GEOGRAPHY
    DECLARE @LatLong2 GEOGRAPHY
    DECLARE @Result FLOAT
    SELECT
        @LatLong1 = LatLong
    FROM
        PostalCode
    WHERE
        PostalCode = @Zip1

    SELECT
        @LatLong2 = LatLong
    FROM
        PostalCode
    WHERE
        PostalCode = @Zip2

    SELECT @Result = @Lat1.STDistance(@Lat2) 

    RETURN @Result/1000
END

Hope this helps

Upvotes: 1

Juan Carlos Oropeza

Reputation: 48187

Try this one

SELECT geography::STGeomFromText('POINT(' + zc.zip_latitude
                                    + ' ' + zc.zip_longitude + ')'
                                 , 4326).STDistance(@geoPoint);

Upvotes: 1

Getting miles from Geography Stored Procedure

Answers (4)

Related Questions