Bonjour à tous,
je suis en train de préparer une petite routine qui permet d'extraire tous les codes postaux à partir d'un code postal donné et d'un rayon en kms.
J'utilise les bases geonames pour cela (www.geonames.org).
CREATE TABLE geoname
(
geonameid integer,
name character varying(200),
asciiname character varying(200),
alternatenames character varying(4000),
latitude double precision,
longitude double precision,
fclass character(1),
fcode character varying(10),
country character varying(2),
cc2 character varying(60),
admin1 character varying(80),
admin2 character varying(80),
admin3 character varying(80),
admin4 character varying(80),
population integer,
elevation integer,
gtopo30 integer,
timezone character varying(40),
moddate date
);
CREATE TABLE countryinfo
(
iso_alpha2 character(2),
iso_alpha3 character(3),
iso_numeric integer,
fips_code character varying(3),
name character varying(200),
capital character varying(200),
areainsqkm double precision,
population integer,
continent character(2),
languages character varying(200),
currency character(3),
geonameid integer
);
insert into countryinfo(iso_alpha2,name) values ('FR','France');
insert into geoname(name,fcode,country,latitude,longitude) values ('Lille','59000','FR',50.628,3.059);
insert into geoname(name,fcode,country,latitude,longitude) values ('Roubaix','59100','FR',50.7,3.167);
insert into geoname(name,fcode,country,latitude,longitude) values ('Hordain','59111','FR',50.267,3.317);
J'ai créé quelques fonctions - qui fonctionnent! - :
CREATE OR REPLACE FUNCTION haversine(double precision, double precision, double precision, double precision)
RETURNS double precision AS
'
/*
Retourne la distance de Haversine deux points
Appel : Latitude1,Longitude1, Latitude2, Longitude2 en degrés, min.
Retourne : distance en kms.
*/
DECLARE
Latitude1 alias for $1;
Longitude1 alias for $2;
Latitude2 alias for $3;
Longitude2 alias for $4;
distance float8;
BEGIN
distance := 6372.795 * 3.1415926 *
sqrt(
(Latitude1-Latitude2)*(Latitude1-Latitude2)
+
cos(Latitude1/57.29578)*cos(Latitude2/57.29578)*(Longitude1-Longitude2)*(Longitude1-Longitude2)
)
/ 180;
return distance;
END;
'
LANGUAGE 'plpgsql' VOLATILE STRICT;
CREATE OR REPLACE FUNCTION litlatitude(bpchar, bpchar)
RETURNS double precision AS
'
/*
Renvoie la latitude en fonction du pays et du code postal
Appel : select * from litLatitude(pays,codepostal)
Exemple : select * from litLatitude("FR","69640");
*/
select latitude from geoname where country like $1 and fcode like $2 limit 1;
'
LANGUAGE 'sql' VOLATILE STRICT;
CREATE OR REPLACE FUNCTION litlongitude(bpchar, bpchar)
RETURNS double precision AS
'
/*
Renvoie la longitude en fonction du pays et du code postal
Appel : select * from litLongitude(pays,codepostal)
Exemple : select * from litLongitude("FR","69640");
*/
select longitude from geoname where country like $1 and fcode like $2 limit 1;
'
LANGUAGE 'sql' VOLATILE STRICT;
Et une fonction qui devrait renvoyer tous les codes postaux à partir d'une simple instruction du type
select * from getPostalCode('FR','59000',80);
Je devrais avoir tous les codes postaux dans les 80 kms autour de Lille (y compris la Belgique que j'ai dans la base).
create type sortie as (country character(2), countryname character(200), fcode character(8));
create or replace function getPostalCode (character(2), character(8), double precision)
returns setof sortie as '
declare
myCountry alias for $1;
myFcode alias for $2;
distance alias for $3;
maLatitude float8;
maLongitude float8;
centre record;
r sortie;
begin
maLatitude := litlatitude(myCountry,myFcode);
maLongitude := litlongitude(myCountry,myFcode);
for r in (
SELECT distinct
geoname.country as country, countryinfo.name as countryname, geoname.fcode as fcode
FROM geoname, countryinfo
WHERE haversine(geoname.Latitude,geoname.Longitude,maLatitude,maLongitude) <= distance
and
geoname.country::character(2)= countryinfo.iso_alpha2
order by geoname.country, geoname.fcode
)
loop
return next r;
end loop;
return;
end
'
LANGUAGE 'plpgsql' VOLATILE STRICT;
Or cette fonction me renvoie une erreur que je n'arrive pas à cerner malgré de très nombreux essais
WARNING: plpgsql: ERROR during compile of getpostalcode near line 12
ERROR: missing .. at end of SQL expression
********** Erreur **********
ERROR: missing .. at end of SQL expression
Une âme charitable verrait-elle directement où je commets une erreur ?
Merci d'avance.
Alain
