Hi Harishgg1,
Thank you very much for your attention on this post. SA3 table has a bigger boundary. I believe my transaction table need to match anything falling into that boundary.
Every transnational LAT/LONG is not available in SA3 table. Is there a way to check anything falling into the lat/long boundary.
Regards
Jay
I guess, transaction file has LAT/LONG for every single point. What I am trying to find all those point into a bigger boundary SA3 area. The software like MapInfo does this. Wondering how is there a way to do the same in SQL server.
/*
Microsoft SQL Server 2016
5 meters 16 foot
*/
create table #gps(locationname varchar(50), latitude float, longitude float,
gps varchar(1500), GeoPoint geography)
insert into #gps(locationname, latitude,longitude, gps)
select 'Ferguson Airport', 30.397101,-87.348347, '' union
select 'Brownsville Middle School', 30.432645,-87.257409,
'0xE610000000010CF2D2'union
select 'Warrington Middle School', 30.4064807891846,
-87.2722015380859, '0xE610000000010CF2D2' union
select 'The Pines At Warrington', 30.4064807891818,
-87.2722015386914,
'0xE610000000010CF2D2' union
select 'Saufley Field', 30.460156,-87.340107, ''
Update #gps Set GeoPoint = GEOGRAPHY::Point(latitude, longitude, 4326)
;with cteBoomBam
as
(
SELECT A.locationname _fromLocation, B.locationname _toLocation
,InMeters = A.GeoPoint.STDistance(B.GeoPoint)
,InMiles = A.GeoPoint.STDistance(B.GeoPoint) / 1609.344
FROM #gps A
cross apply #gps B
where a.locationname <> b.locationname
)
select * From cteBoomBam where InMeters <= 5
--select @@VERSION
drop table #gps
Might this lead you in the right direction
hi Jaynal
One way would be to find the closest point ...
Example
Transaction table
Lat 100
SA3 Table ....
London
Lat 110
NewYork
Lat 80
Do the difference and take the closest one as Area
Difference Latitude ...
Transaction table lat = 100
SA3 Table London lat = 110 so difference of lat 110 - 100 = 10
SA3 table NewYork lat = 80 so difference of lat 80 - 100 = 20
since Transaction table lat is closest to London ( diff lat 10 )
then its London Area
is this something like how to do it ????
Thank you Yosiasz. This seems the way. I will try and advise you accordingly.
Regards
Jay.
What is the end goal of what you are trying to do? In plain English, with no technical terminologies
I'm not sure if I interpret your question correct. But it seems to me that you want to check if a point lies within an area (polygon) defined by a number of points.
The STDistance() function gives no indication if the point lies within an area, only how far it is from another point.
The easiest way would be if ABS provides polygons of the SA3 areas and not only a number of unordered points.
If they do, you can use the STIntersects() function as in this example:
Declare @point geometry
Declare @polygon geometry
SET @polygon = geometry::STGeomFromText('POLYGON ((-88.2 41.5, -88.2 41.6, -88.3 41.6, -88.3 41.5, -88.2 41.5))', 4326)
SET @point = geometry::STGeomFromText('POINT (-88.22 41.50000001)', 4326)
Select @point.STIntersects(@polygon) -- STIntersects() returns 1 if the point lies within the polygon
SET @point = geometry::STGeomFromText('POINT (-88.0 41.50000001)', 4326)
Select @point.STIntersects(@polygon) -- STIntersects() returns 0 if the point does not lie within the polygonAs Wim said, you need to first plot your polygon, then see if your other locations fall into that polygon.
using sql server
added the following long,lat for Brogo, New South Wales 2550, Australia
(10999999,'2019-04-01', 149.767249, -36.503551, '149.767249, -36.503551')
and it shows as intersecting. you would need to do some visual and data validations on your own though.
DECLARE @BuildString NVARCHAR(MAX), @closepolygon NVARCHAR(MAX), @PolygonFromPoints geography ;
select @BuildString= COALESCE(@BuildString + ',', '') + CAST(a.Longitude AS NVARCHAR(50)) + ' ' + CAST(a.Latitude AS NVARCHAR(50))
from [SA3] a
order by code
select top 1 @closepolygon = CAST(tak.Longitude AS NVARCHAR(50)) + ' ' + CAST(tak.Latitude AS NVARCHAR(50))
from [SA3] tak
order by code
SET @BuildString = ' POLYGON((' + @BuildString + ', ' + @closepolygon + '))'
SET @PolygonFromPoints = geography::STPolyFromText(@BuildString, 4326);
select @PolygonFromPoints
;with src
as
(
select geography::STGeomFromText('POINT(' + CAST([PLong] AS VARCHAR(20)) + ' ' + CAST(PLat AS VARCHAR(20)) + ')', 4326) as geoPoint, *
from Tab1
)
select case geoPoint.STIntersects(@PolygonFromPoints)
when 0 then 'Does not Intersect'
else 'Intersects' end as Status ,*
From src
Using google maps manually
overlapped showing brogo being inside the polygon
declare @doublepolygon varchar(max) = 'POLYGON((149.647 -35.5237,148.892 -36.4473,149.848 -36.5858,149.614 -34.5003,148.69 -34.6456, 149.647 -35.5237), (150.784 -34.0284,150.932 -33.8737,151.178 -33.8967,151.547 -33.272,148.743 -35.7563,149.84 -29.4704,149.767 -36.5036, 150.784 -34.0284))'
declare @polygonsoverlap geography = geography::STPolyFromText(@doublepolygon, 4326);
select @polygonsoverlap
Hi Win_Leys,
This is exactly my issue is. Transaction file has all the individual point(?AT/LONG). I want to find the area(Polygon) of ABS SA3 table that has LAT/Long but not one-to-one. How actually I can match if my point in transaction table will fits the range of that bigger LAT/LONG boundary.
In plain term, if my point(LONG/LAT) in transaction table in the range of (ie Queanbeyan LONG/LAT 149.6468291 / -35.52366613). How could I check an individual point with a bigger LONG/LAT boundary.
SA3 table data.
Regards
Jay
I have Googled a bit and found this: https://www.abs.gov.au/AUSSTATS/abs@.nsf/Lookup/1270.0.55.001Explanatory%20Notes1July%202016?OpenDocument
I don't have enough experience in this field to help you any further. I would suggest to contact ABS. You won't be the first with questions and to my experience, such organisations are very willing to help.
hi
so basically what you are saying is
if you have point lets say Lat 5 which is for
city Chicago
It will be in area which is
State Illinois
It will also be in a wider range which is
Country U.S.A
What exactly is it that you want to check ????
The same way you check if it is in State ,
is the same way you will check in Country
What exactly are you wanting to do ???
Harish,
The Australian Bureau of Statistics (ABS) has divided the country in different area's.
They also defined a hierarchy of area's. The lowest is Statistical Area 1 (SA1).
An SA4 area consists of a number of SA3's.
An SA3 area consists of a number of SA2's.
And an SA2 area consists of a number of SA1's.
It's similar to the hierarchy you described.with city, state, country, ..
He wants to check for a location (point), defined by a longitude and latitude, in what SA3 area it is located.
Converting a longitude - latitude location to a SQL Server GEOGRAPHY POINT is easy.
SET @point = geometry::STGeomFromText('POINT (-88.22 41.50000001)', 4326)
The challenge is to convert the open data from ABS to a SQL Server GEOGRAPHY POLYGON.
Once that is done, the rest is easy.
Select @point.STIntersects(@polygon) -- STIntersects() returns 1 if the point lies within the polygon, 0 otherwise
Hi Wim
I don't think its a challenge
to convert the open data from ABS to a SQL Server GEOGRAPHY POLYGON.
Please correct me if i am wrong
These sort of things have been going on for decades !!!
More like just finding it .. As we are not used to this sort of thing !!!
We are not in this field !!!
OR
Round About ... WorkArounds !!!
hi Wim
do the below links help
https://uwescience.github.io/SQL-geospatial-tutorial/04-geospatialQueries/
You get a polygon by stringing long lat pairs separated by a comma. The first pair should also show up as the last pair to close the polygon loop. Not sure what more you are looking for?
select @BuildString= COALESCE(@BuildString + ',', '') + CAST(a.Longitude AS NVARCHAR(50)) + ' ' + CAST(a.Latitude AS NVARCHAR(50))
from [SA3] a
order by code
select top 1 @closepolygon = CAST(tak.Longitude AS NVARCHAR(50)) + ' ' + CAST(tak.Latitude AS NVARCHAR(50))
from [SA3] tak
order by code
SET @BuildString = ' POLYGON((' + @BuildString + ', ' + @closepolygon + '))'
Hi yosiasz
You get a polygon by stringing long lat pairs separated by a comma.
The earth is a globe. the order of the points that define the polygon matters. Suppose you want to create a polygon of the North Pole. Depending on the order of the points (West-ward or East-ward) the system will interpret the polygon as defining the North Pole - as intended - or the whole planet, except the North Pole.
Agreed. A certain Order is indeed necessary
Hi Harris,
Good Morning.
I appreciate your attention on this topic.
I like to project my response time performance (let’s say) by City/State/Country based on Lat/long in transaction table.
More specifically, I want to make a group Green/Yellow/Red based on response time within 7/10/15 minutes by City/State/Country.
I have lat/long in all level data. But that is not one-to-one match.
How could I match my individual lat/long point of transaction table against lat/long in respective city/state/country lat/long.
Then I could say City a,b,c are green, d,x are Yellow… Again State I,P are Red.. based on my transaction table lat/long.
I hope this makes sense.
Regards
Jaynal
there has to be some way of mapping !!!
this has been going on for
" Hundreds of Years "
I mean Lat Long and Maps
Needs some Research and Development !!!!
I have never done this !!! dont have that much idea