I have a transaction table with LAT/LONG variable. I do like to match this with ABS boundary table (ie ASGS_2016_SA3_Points) to bring the SA3 Code and Boundary name. Do any one have a some code to match two tables using lat/long variable.
Any help on this will be highly appreciated.
Kind Regards
Jay
Please provide sample data as
Create table #location(long , lat )
Insert into #location
Select '74848484748', '84847474747' union
Etc
Transaction Table with Lat/Long Info: (Tab1)
ID Dates PLong PLat Pickup_Coord
10783898 2019-04-07 149.839857 -29.47039 149.839857,-29.470390
10781761 2019-04-07 150.931578 -33.873666 150.931578,-33.873666
10781797 2019-04-07 151.178333 -33.896667 151.178333,-33.896667
10781733 2019-04-07 150.784091 -34.028431 150.784091,-34.028431
10781945 2019-04-07 151.547338 -33.272007 151.547338,-33.272007
SA3 sample data (Tab2)
SA3_CODE SA3_NAME LONGITUDE LATITUDE
10102 Queanbeyan 149.6468291 -35.52366613
10103 Snowy Mountains 148.8922014 -36.44734746
10104 South Coast 149.847822 -36.58575406
10105 Goulburn - Mulwaree 149.6137882 -34.5003047
10106 Young - Yass 148.6899067 -34.64561683
I like to include Code and Name in my transaction table Tab1.
Any help on this will be highly appreciated.
CREATE TABLE [Tab1](
ID [float] NULL,
Date_Response [Date] NULL,
[PLong] [float] NULL,
[PLat] [float] NULL,
Pickup_Coord nvarchar null,
) ON [PRIMARY]
GO
insert into [Tab1]
values
(10783898, '2019-04-07', 149.839857, -29.47039, '149.839857,-29.470390'),
(10781761, '2019-04-07', 150.931578, -33.873666, '150.931578,-33.873666'),
(10781797, '2019-04-07', 151.178333, -33.896667, '151.178333,-33.896667'),
(10781733, '2019-04-07', 150.784091, -34.028431, '150.784091,-34.028431'),
(10781945, '2019-04-07', 151.547338, -33.272007, '151.547338,-33.272007')
CREATE TABLE [SA3](
code [float] NULL,
Name nvarchar NULL,
[LONGITUDE] [float] NULL,
[LATITUDE] [float] NULL
) ON [PRIMARY]
GO
insert into [SA3]
values (10102, 'Queanbeyan', 149.6468291, -35.52366613),
(10103, 'Snowy Mountains', 148.8922014, -36.44734746),
(10104, 'South Coast', 149.847822, -36.58575406),
(10105, 'Goulburn - Mulwaree', 149.6137882, -34.5003047),
(10106, 'Young - Yass', 148.6899067, -34.64561683)
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 + '))'