Create Geometry/Geography Field from Latitude & Longitude fields (SQL Server)
SELECT *,
geography::STGeomFromText('POINT(' +
CAST([Longitude] AS VARCHAR(20)) + ' ' +
CAST([Latitude] AS VARCHAR(20)) + ')', 4326) as GEOM,
geography::Point([Latitude], [Longitude], 4326) as SAME_GEOM
FROM view_name
WHERE (latitude <> 0) AND (longitude <> 0)
SQL Server - From longitude and latitude to geometry data type
You have a geometry, which stores a shape in Euclidean geometry, and you want to associate a point on the globe, represented by a latitude and longitude, with it, to see if it is inside. This won't work, due to the way SQL stores the data. You probably need to use Geography data types to check this - Latitude and longitude are points on a sphere (Actually geodetic data, since the earth is not quite a sphere.)
For more information on why they are different, see this explanation from microsoft. Also this answer on stackoverflow: GEOMETRY and GEOGRAPHY difference SQL Server 2008
To convert your data from geometry to geography, try:
Geography::STGeomFromText(cast(GeomCol as varchar(max)), 4326)
Then you can use the STIntersects
method, documented by microsoft here.
How to store longitude & latitude as a geography in sql server 2014?
How can i use the longitute and latitute to store location within a geography column?(because it's supposed to be only one geographic point not two right? not one for longitute and one for latitute?)
You can use geography::STPointFromText
/ geography::Point
to store longitude and latitude in a geography datatype.
SELECT geography::STPointFromText('POINT(' + CAST([Longitude] AS VARCHAR(20)) + ' ' + CAST([Latitude] AS VARCHAR(20)) + ')', 4326)
or
SELECT geography::Point(Latitude, Longitude , 4326)
Reference Link:
Update Geography column in table
Now that I've got the geography points, how can i select all the rows within a specific distance(in my case 2km)?
You can use STDistance
like this.
DECLARE @g geography;
DECLARE @h geography;
SET @g = geography::STGeomFromText('POINT(-122.35900 47.65129)', 4326);
SET @h = geography::STGeomFromText('POINT(-122.34720 47.65100)', 4326);
SELECT @g.STDistance(@h);
Reference Link:
Distance between two points using Geography datatype in sqlserver 2008?
Insert Query
DECLARE @GeoTable TABLE
(
id int identity(1,1),
location geography
)
--Using geography::STGeomFromText
INSERT INTO @GeoTable
SELECT geography::STGeomFromText('POINT(-122.35900 47.65129)', 4326)
--Using geography::Point
INSERT INTO @GeoTable
SELECT geography::Point(47.65100,-122.34720, 4326);
Get Distance Query
DECLARE @DistanceFromPoint geography
SET @DistanceFromPoint = geography::STGeomFromText('POINT(-122.34150 47.65234)', 4326);
SELECT id,location.Lat Lat,location.Long Long,location.STDistance(@DistanceFromPoint) Distance
FROM @GeoTable;
How to create calculated Geometry column that ignores NULL values in SQL Server
Try filtering the values first. Something like this:
ALTER TABLE Sites
ADD Geo2 AS IIF(Latitude IS NOT NULL AND Longitude IS NOT NULL, [geography]::Point(Longitude,Latitude,4326), NULL)
Group by value and create geography polyline from points (latitude and longitude) for each group in T-SQL
From SQL Server 2017+
you could use:
SELECT geography::STLineFromText('LINESTRING(' +
STRING_AGG(CONCAT(Longitude, ' ' ,Latitude), ',')
WITHIN GROUP(ORDER BY SortOrder) + ')' , 4326) AS geometry
,SensorId
FROM dbo.LongAndLats
GROUP BY SensorId
HAVING COUNT(*) > 1;
DBFiddle Demo
I've tried using a db_cursor but I get a separate result set for each group
Please avoid cursors, end each line with semicolon and stop using:
SELECT @BuildString = COALESCE(@BuildString + ',', '')
+ CAST([Longitude] AS NVARCHAR(50)) + ' ' + CAST([Latitude]
AS NVARCHAR(50))
FROM [LongAndLats]
WHERE SensorID = @SensorID
ORDER BY SortOrder;
Construct above may look ok, but it could lead to undefined behaviour. More info: nvarchar concatenation / index / nvarchar(max) inexplicable behavior
EDIT:
SQL Server 2012 version:
SELECT geography::STLineFromText('LINESTRING('
+ STUFF(
(SELECT ',' + CONCAT(Longitude, ' ' ,Latitude)
FROM dbo.LongAndLats t2
WHERE t1.SensorId = t2.SensorId
ORDER BY SortOrder
FOR XML PATH (''))
, 1, 1, '')
+ ')'
, 4326) AS geometry, SensorId
FROM dbo.LongAndLats t1
GROUP BY SensorId
HAVING COUNT(*) > 1;
DBFiddle Demo2
EDIT2:
To avoid:
A .NET Framework error occurred during execution of user-defined routine or aggregate "geography":
System.FormatException: 24117: The LineString input is not valid because it does not have enough points. A LineString must have at least two points.
you could add HAVING COUNT(*) > 1
;
FINAL EDIT:
If you have "garbage data", just filter it out(or add CHECK
constraint on that column):
"Latitude values must be between -90 and 90 degrees"
SELECT geography::STLineFromText('LINESTRING('
+ STUFF(
(SELECT ',' + CONCAT(Longitude, ' ' ,Latitude)
FROM dbo.LongAndLats t2
WHERE t1.SensorId = t2.SensorId
AND Latitude BETWEEN -90 and 90
AND Longitude BETWEEN -180 AND 180
ORDER BY SortOrder
FOR XML PATH (''))
, 1, 1, '')
+ ')'
, 4326) AS geometry, SensorId
FROM dbo.LongAndLats t1
WHERE Latitude BETWEEN -90 and 90
AND Longitude BETWEEN -180 AND 180
GROUP BY SensorId
HAVING COUNT(*) > 1;
DBFiddle Demo3
Geometry operations on latitude/longitude coordinates
I post as an answer because as comment is too long
geometry works well to the extent that your intersections are approximable to flat intersections.
the difference between geometry and geography consists in the fact that the former works by hypothesizing to work on plane surfaces the second on spherical surfaces. in the case in which the polygons in question are related to small areas in the order of a few thousand meters geometry works very well. the difference between measured distance by imagining that the points lie on a plane or that the points lie on the earth's sphere is so small as to be negligible. Unlike the question if the points are a few hundred kilometers in this case the distance measured in the plane or on the sphere is very different and proportionally is also the result of the intersection between these areas
Related Topics
How to Select and Order by Columns Not in Groupy by SQL Statement - Oracle
Sql Server Table Locks in Long Query - Solution: Nolock
Displaying Columns as Rows in SQL Server 2005
How to Insert Data Directly from Excel to Oracle Database
Sql Server 2012: Add a Linked Server to Postgresql
Creating Groups of Consecutive Days Meeting a Given Criteria
Most Efficient Method for Persisting Complex Types with Variable Schemas in Sql
Sql 2008 Vs 2012 Error: Incorrect Syntax Near The Keyword 'Compute'
Execute Procedure in a Trigger
Error While Uploading a Report
How to Use Sum for Bit Columns
Sql - Query to Insert a Column Value If It Does Not Exist in That Column
Sql Create Database If Not Exists, Unexpected Behaviour
Django Annotate() Multiple Times Causes Wrong Answers
Why Can't I Reorder My SQL Server Columns
Display Zero by Using Count(*) If No Result Returned for a Particular Case