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 48 49 50 51 52 53 54 55
| CREATE EXTENSION postGIS;
CREATE EXTENSION citext;
CREATE SCHEMA S_ZON; -- > schema geographic zones
CREATE DOMAIN S_ZON.D_LATITUDE DOUBLE PRECISION -- latitude coordinates
CHECK (VALUE BETWEEN -90 AND 90);
CREATE DOMAIN S_ZON.D_LONGITUDE DOUBLE PRECISION -- longitude coordinates
CHECK (VALUE BETWEEN -180 AND 180);
CREATE DOMAIN S_ZON.D_ZOOM SMALLINT -- zoom level display
CHECK (VALUE BETWEEN 0 AND 23);
CREATE SCHEMA S_REF; -- > schema reference
CREATE DOMAIN S_REF.D_NAME as CITEXT -- max 64 insensitive alphanumeric
CHECK (length(value) <= 64);
CREATE TABLE S_ZON.T_ZON -- > geographical zones in schema geographic destinations
( ZON_ID INT PRIMARY KEY,
ZON_label S_REF.D_NAME not null, -- destination name
ZON_latitude S_ZON.D_LATITUDE not null, -- latitude coordinates
ZON_longitude S_ZON.D_LONGITUDE not null, -- longitude coordinates
ZON_geo geography(point) -- postGIS type
);
insert into S_ZON.T_ZON values (49, 'France', 48.86, 2.35, 'point(48.867 2.35)), (262, 'Mauritius', -20.2833, 57.55, 'point(-20.2833 57.55));
/*POURQUOI LES RESULTATS SONT DIFFERENTS ??*/
-- 1) results 9 413,520 Km
SELECT zon_id, typ_id, zon_label, zon_latitude, zon_longitude,
111.045* DEGREES(ACOS(COS(RADIANS(latpoint))
* COS(RADIANS(z.zon_latitude))
* COS(RADIANS(longpoint) - RADIANS(z.zon_longitude))
+ SIN(RADIANS(latpoint))
* SIN(RADIANS(z.zon_latitude)))) AS distance_in_km
FROM s_zon.t_zon AS z
JOIN (
SELECT 48.86 AS latpoint, 2.35 AS longpoint
) AS p ON 1=1
WHERE Z.zon_id = 262 ;
-- 2) results 8 554,608 Km
SELECT (ST_Distance(toZ.zon_geo,fromZ.zon_geo)) / 1000,
ToZ.zon_label::text || ' - ' || fromZ.zon_label::text as distance_label_km
FROM s_zon.t_zon AS toZ CROSS JOIN s_zon.t_zon AS fromZ
WHERE toZ.zon_ID = 262 and fromZ.zon_ID = 49;
-- 3) results 8 554,608 Km
select st_distance( st_geographyfromtext ('POINT(48.867 2.35)'),
st_geographyfromtext ('point(-20.2833 57.55)'))/1000;
-- 4) results 8 554,608 Km
select st_distance( geometry ('0101000020E6100000AE47E17A146E4840CDCCCCCCCCCC0240'),
geometry ('0101000020E6100000F1F44A59864834C06666666666C64C40'))/1000; |
Partager