0

I've added a geography column to my table (MICROSOFT SQL SERVER) and inserted geography point data in it (4326). Now I am able to compute distance of all points from a specific point using below query:

DECLARE @X geography = geography::Point(0, 0 , 4326);
SELECT TOP(4) *, LOCATION.STDistance(@
FROM [ADDRESS] A
WHERE A.LOCATION IS NOT NULL 
ORDER BY LOCATION.STDistance(@X) ASC

and it works. However I would like to compute distance using Euclidean distance formula: sqrt((y1-y2)^2 + (x1-x2)^2)) and would like to have two separate columns for x and y positions if it's possible.

Cœur
  • 37,241
  • 25
  • 195
  • 267
Shane
  • 128
  • 1
  • 3
  • 15
  • Absoulte Positiones - that french ? – Caffeinated Jan 27 '15 at 00:32
  • 3
    Yes, SQL server has a [SQRT function](https://msdn.microsoft.com/en-us/library/ms176108.aspx) so you can just implement that yourself using two columns can't you? But there's the [geometry type](https://msdn.microsoft.com/en-us/library/cc280487.aspx) for Euclidean distances which will do this all for you, albeit stored in a single column. – Rup Jan 27 '15 at 00:39
  • 1
    What is the difficulty that you're having? – David Faber Jan 27 '15 at 00:40

1 Answers1

2

Thanks Rup :) I changed the type from geography to geometry with SRID 0!

UPDATE [ADDRESS]
SET LOCATION = geometry::Point(1, 1, 0)
WHERE ADDRESSCODE = 4


DECLARE @X geometry = geometry::Point(0, 0 , 0)
SELECT TOP(4) *, LOCATION.STDistance(@X)
FROM [ADDRESS] A
WHERE LOCATION IS NOT NULL 
ORDER BY LOCATION.STDistance(@X) ASC
Shane
  • 128
  • 1
  • 3
  • 15