June 23, 2017 at 3:09 pm
I have the longitude and latitude for my location. I don't know how to find the area of the property. Our employees must be within 100 feet of a location before they can clock i using our app.
I need the variables after the longitude latitude. I have put them in bold, underline. I think they are called quarter nuts.
SELECT geography ::STPolyFromText( 'POLYGON((174.6596469283104 -36.724981172899945, 174.6591963171959 -36.72647744053394, 174.6586598753929 -36.72787049109019, 174.65816634893417 -36.72903994611866, 174.66164249181747 -36.72924631868662, 174.66250079870224 -36.72909153931266, 174.6627797484398 -36.72658063033066, 174.6617712378502 -36.72587549728961, 174.66084855794907 -36.72544553494063, 174.66059106588364 -36.724981172899945, 174.6596469283104 -36.724981172899945))', 4326);
June 28, 2017 at 11:53 am
gailcole - Friday, June 23, 2017 3:09 PMI have the longitude and latitude for my location. I don't know how to find the area of the property. Our employees must be within 100 feet of a location before they can clock i using our app.
I need the variables after the longitude latitude. I have put them in bold, underline. I think they are called quarter nuts.
SELECT geography ::STPolyFromText( 'POLYGON((174.6596469283104 -36.724981172899945, 174.6591963171959 -36.72647744053394, 174.6586598753929 -36.72787049109019, 174.65816634893417 -36.72903994611866, 174.66164249181747 -36.72924631868662, 174.66250079870224 -36.72909153931266, 174.6627797484398 -36.72658063033066, 174.6617712378502 -36.72587549728961, 174.66084855794907 -36.72544553494063, 174.66059106588364 -36.724981172899945, 174.6596469283104 -36.724981172899945))', 4326);
Okay, you want the area of your location, but then you mention that your employees must be within 100 feet of a location before they can clock in. I'm not entirely sure what you are trying to solve for. Your geography data specifies a polygon, that appears to be defined by 11 points. Objects with that many sides are a bit hard to get the area of, but then why would you need that information when you are talking about employees needing to be within 100 feet of a given location? I would expect that polygon data to be describing the individual lines representing the boundaries of your location, and what you may need instead is a way to use geography functions to measure the shortest distance from a given latitude and longitude (or POINT) to the nearest point on your boundary. I'm not real familiar with the geography data types or functions, but as I do have the math background, you need a function that can determine distance between your employees lat, long and the boundary of your location. I'd like to think such a geographic function exists. I just don't have the familiarity to know. Google it...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 29, 2017 at 3:29 pm
sgmunson - Wednesday, June 28, 2017 11:53 AMgailcole - Friday, June 23, 2017 3:09 PMI have the longitude and latitude for my location. I don't know how to find the area of the property. Our employees must be within 100 feet of a location before they can clock i using our app.
I need the variables after the longitude latitude. I have put them in bold, underline. I think they are called quarter nuts.
SELECT geography ::STPolyFromText( 'POLYGON((174.6596469283104 -36.724981172899945, 174.6591963171959 -36.72647744053394, 174.6586598753929 -36.72787049109019, 174.65816634893417 -36.72903994611866, 174.66164249181747 -36.72924631868662, 174.66250079870224 -36.72909153931266, 174.6627797484398 -36.72658063033066, 174.6617712378502 -36.72587549728961, 174.66084855794907 -36.72544553494063, 174.66059106588364 -36.724981172899945, 174.6596469283104 -36.724981172899945))', 4326);Okay, you want the area of your location, but then you mention that your employees must be within 100 feet of a location before they can clock in. I'm not entirely sure what you are trying to solve for. Your geography data specifies a polygon, that appears to be defined by 11 points. Objects with that many sides are a bit hard to get the area of, but then why would you need that information when you are talking about employees needing to be within 100 feet of a given location? I would expect that polygon data to be describing the individual lines representing the boundaries of your location, and what you may need instead is a way to use geography functions to measure the shortest distance from a given latitude and longitude (or POINT) to the nearest point on your boundary. I'm not real familiar with the geography data types or functions, but as I do have the math background, you need a function that can determine distance between your employees lat, long and the boundary of your location. I'd like to think such a geographic function exists. I just don't have the familiarity to know. Google it...
I have been researching, doing tutorials and googling for a few days. I initially wanted to know if the users were on the property. Since i couldn't make that work i wanted to check if they were within a radius. I am usinf stbuffer but it is still a guess. The distance us in units but ms doesn't say what the units are. They
seem to be 1/2 a meter.
June 30, 2017 at 9:09 am
I don't know about the units, but it appears that you need either STContains or STDistance. If your polygon borders are inclusive of the 100 foot distance, then you'll need STContains, which returns a bit value, or otherwise you'll need STDistance, and thus the knowledge on units as well. Here's a UDF that will determine if the Latitude and Longitude passed to it are within the polygon you posted:CREATE FUNCTION dbo.udf_IsOnTheProperty (
@Latitude decimal(16,13),
@Longitude decimal(16,13)
)
RETURNS bit
AS
BEGIN
DECLARE @Location AS geography = geography::STPolyFromText('POLYGON((174.6596469283104 -36.724981172899945, 174.6591963171959 -36.72647744053394, 174.6586598753929 -36.72787049109019, 174.65816634893417 -36.72903994611866, 174.66164249181747 -36.72924631868662, 174.66250079870224 -36.72909153931266, 174.6627797484398 -36.72658063033066, 174.6617712378502 -36.72587549728961, 174.66084855794907 -36.72544553494063, 174.66059106588364 -36.724981172899945, 174.6596469283104 -36.724981172899945))', 4326);
DECLARE @Employee AS geography = geography::Parse('POINT(' + CONVERT(varchar(18), @Latitude) + ' ' + CONVERT(varchar(18), @Longitude) + ')');
RETURN (SELECT @Location.STContains(@Employee))
END;
GO
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 3, 2017 at 6:17 am
sgmunson - Friday, June 30, 2017 9:09 AMI don't know about the units, but it appears that you need either STContains or STDistance. If your polygon borders are inclusive of the 100 foot distance, then you'll need STContains, which returns a bit value, or otherwise you'll need STDistance, and thus the knowledge on units as well. Here's a UDF that will determine if the Latitude and Longitude passed to it are within the polygon you posted:CREATE FUNCTION dbo.udf_IsOnTheProperty (
@Latitude decimal(16,13),
@Longitude decimal(16,13)
)
RETURNS bit
AS
BEGIN
DECLARE @Location AS geography = geography::STPolyFromText('POLYGON((174.6596469283104 -36.724981172899945, 174.6591963171959 -36.72647744053394, 174.6586598753929 -36.72787049109019, 174.65816634893417 -36.72903994611866, 174.66164249181747 -36.72924631868662, 174.66250079870224 -36.72909153931266, 174.6627797484398 -36.72658063033066, 174.6617712378502 -36.72587549728961, 174.66084855794907 -36.72544553494063, 174.66059106588364 -36.724981172899945, 174.6596469283104 -36.724981172899945))', 4326);
DECLARE @Employee AS geography = geography::Parse('POINT(' + CONVERT(varchar(18), @Latitude) + ' ' + CONVERT(varchar(18), @Longitude) + ')');
RETURN (SELECT @Location.STContains(@Employee))
END;
GO
Where do I get the long and lat that defines the property in the polygon function? That was what I was originally trying to do.
July 3, 2017 at 7:03 am
I was presuming that the SQL code you posted originally, which contained a number of latitude and longitude values, was the polygon representing the boundaries of your location. If that's not the case, then you'll either need a fairly accurate GPS that can provide those figures to you as you walk the property boundaries, or you'll need to hire a geo-mapping service to do it for you.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 4, 2017 at 7:46 am
Thank you. Those were from an example. I think I'll use the radius from stbuffer(). Do you know the measure for the units? I appears to be a half meter.
July 4, 2017 at 7:46 am
Thank you. Those were from an example. I think I'll use the radius from stbuffer(). Do you know the measure for the units? I appears to be a half meter.
July 5, 2017 at 6:41 am
gailcole - Tuesday, July 4, 2017 7:46 AMThank you. Those were from an example. I think I'll use the radius from stbuffer(). Do you know the measure for the units? I appears to be a half meter.
Not sure about the units, but take a look at the example shown here:
https://docs.microsoft.com/en-us/sql/t-sql/spatial-geography/stbuffer-geography-data-type
That page's example makes it look like units are 1 meter, unless I'm misinterpreting things...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 5, 2017 at 3:57 pm
sgmunson - Wednesday, July 5, 2017 6:41 AMgailcole - Tuesday, July 4, 2017 7:46 AMThank you. Those were from an example. I think I'll use the radius from stbuffer(). Do you know the measure for the units? I appears to be a half meter.Not sure about the units, but take a look at the example shown here:
https://docs.microsoft.com/en-us/sql/t-sql/spatial-geography/stbuffer-geography-data-type
That page's example makes it look like units are 1 meter, unless I'm misinterpreting things...
Thanks. I interpreted it as a meter as well but when I measure the actual distance from point to point on google it is about half. My solution will work for what I need. I just don't want employees logging in from home.
July 6, 2017 at 6:37 am
gailcole - Wednesday, July 5, 2017 3:57 PMsgmunson - Wednesday, July 5, 2017 6:41 AMgailcole - Tuesday, July 4, 2017 7:46 AMThank you. Those were from an example. I think I'll use the radius from stbuffer(). Do you know the measure for the units? I appears to be a half meter.Not sure about the units, but take a look at the example shown here:
https://docs.microsoft.com/en-us/sql/t-sql/spatial-geography/stbuffer-geography-data-type
That page's example makes it look like units are 1 meter, unless I'm misinterpreting things...
Thanks. I interpreted it as a meter as well but when I measure the actual distance from point to point on google it is about half. My solution will work for what I need. I just don't want employees logging in from home.
I'm not entirely sure that Google maps distances are necessarily all that accurate. I'd be interested in their mathematical basis for those calculations before I'd rely on them, because it's far easier mathematically to just compute the "straight through the earth, point to point distance" than to take the time to compute the arc of the circle that should exist between those two points and then get the length of that arc. Yes, it' still relatively simple mathematics, but the computations necessary are going to consume a much larger amount of compute time, and Google, being in a business where they kind of HAVE to be as efficient as possible, would assuredly choose to consume as little cpu as possible, and not worry about those kind of inaccuracies, as for the vast majority of their users, any slight deviations are not going to matter very much. You also have to consider that they may well have permanent distance calculations stored in a database somewhere such that they can use calculations that rely on that stored data and not just on geodesics mathematics. Again, all in the name of efficiency.
However, that said, it would be relatively easy to compute distance with both figures, take the average, and then go actually measure the property and see where in the range relative to the average the distance you measure, falls. I'd do that for at least three straight line edges, none of which are parallel, and then compute back to the necessary units and use that number going forward.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
January 20, 2018 at 8:55 pm
I know that this topic has been over for a while but....
Be aware that at distances of 100 feet (or even 20 miles) there is little need to use the geographic distance functions. They are INTENSELY calculation intensive, and in mass quantities can bring a server to its knees. Instead, use the geographic distance function to create a table which has the adjusted distance per meter (or mile) for each degree of latitude. Then use it in conjunction with the Pythagorean theorem to calculate distance as if the earth was flat. We actually had this approach blessed by "Spatial Ed" a few years back and we work with a database of millions of properties that have to be compared to each other.
But wait, I hear you say, that means that a measurement might be off by a fraction of an inch or even (shudder) an inch. Are you seriously worried about that? You have to realize that all distances are arbitrary. There is nothing sacred about a radius of 100 meters, or a quarter mile. Wherever you draw the line for a radius distance, there will always be a few more points just a fraction of an inch outside that radius. Make the radius 100.01 meters and there will almost certainly be something sitting right on 100.015. So don't sweat the 6th decimal place.
Finally, use the stdistance function instead of testing to see if points are within a buffered polygon. Buffering will likely wind up using more CPU cycles than needed to get your result. If you're only concerned about measuring a couple dozen points against a polygon it won't kill you. But when you get to really big numbers it becomes a problem. Best of luck.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply