1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47
| CREATE TABLE [dbo].[VILLE_1](
[LIB_VILLE] [nvarchar](255) NULL,
[CODE_INSEE] [float] NOT NULL,
[LATITUDE] [nvarchar](255) NULL,
[LONGITUDE] [nvarchar](255) NULL
) ON [PRIMARY]
INSERT INTO [dbo].[VILLE_1]([LIB_VILLE], [CODE_INSEE], [LATITUDE], [LONGITUDE]) VALUES ('Bruyères', 88078, '48.2', '6.716667')
INSERT INTO [dbo].[VILLE_1]([LIB_VILLE], [CODE_INSEE], [LATITUDE], [LONGITUDE]) VALUES ('Champ-le-Duc', 88086, '48.2', '6.716667')
SELECT (acos(1)) AS A_COS,
cos(radians(cast(isnull(v1.LATITUDE, '0') AS float)))
*cos(radians(cast(isnull(v2.LATITUDE, '0') AS float)))
*cos(radians(cast(isnull(v2.LONGITUDE, '0') AS float))
-radians(cast(isnull(v1.LONGITUDE, '0') AS float))
)
+sin(radians(cast(isnull(v1.LATITUDE, '0') AS float)))
*sin(radians(cast(isnull(v2.LATITUDE, '0') AS float))) AS VALEUR_CALCULEE
FROM VILLE_1 v1
INNER JOIN VILLE_1 v2 ON v1.LIB_VILLE > v2.LIB_VILLE
WHERE isnull(v1.LATITUDE, '') <> ''
AND isnull(v1.LONGITUDE, '') <> ''
AND isnull(v2.LATITUDE, '') <> ''
AND isnull(v2.LONGITUDE, '') <> ''
AND abs(abs(cast(isnull(v1.LATITUDE, '0') AS float)) - abs(cast(isnull(v2.LATITUDE, '0') AS float))) < 1
AND abs(abs(cast(isnull(v1.LONGITUDE, '0') AS float)) - abs(cast(isnull(v2.LONGITUDE, '0') AS float))) < 1
ORDER BY v1.LIB_VILLE, v2.LIB_VILLE
SELECT (acos(
cos(radians(cast(isnull(v1.LATITUDE, '0') AS float)))
*cos(radians(cast(isnull(v2.LATITUDE, '0') AS float)))
*cos(radians(cast(isnull(v2.LONGITUDE, '0') AS float))
-radians(cast(isnull(v1.LONGITUDE, '0') AS float))
)
+sin(radians(cast(isnull(v1.LATITUDE, '0') AS float)))
*sin(radians(cast(isnull(v2.LATITUDE, '0') AS float)))
)
) AS A_COS
FROM VILLE_1 v1
INNER JOIN VILLE_1 v2 ON v1.LIB_VILLE <> v2.LIB_VILLE
WHERE isnull(v1.LATITUDE, '') <> ''
AND isnull(v1.LONGITUDE, '') <> ''
AND isnull(v2.LATITUDE, '') <> ''
AND isnull(v2.LONGITUDE, '') <> ''
AND abs(abs(cast(isnull(v1.LATITUDE, '0') AS float)) - abs(cast(isnull(v2.LATITUDE, '0') AS float))) < 1
AND abs(abs(cast(isnull(v1.LONGITUDE, '0') AS float)) - abs(cast(isnull(v2.LONGITUDE, '0') AS float))) < 1
ORDER BY v1.LIB_VILLE, v2.LIB_VILLE |
Partager