-- File get-country.sql
CREATE or REPLACE FUNCTION osml10n_get_country_o(feature geometry) RETURNS TEXT AS $$
DECLARE
country text;
BEGIN
SELECT country_code into country
from country_osm_grid
where st_contains(geometry, st_centroid(st_transform(feature,4326)))
order by area limit 1;
return country;
END;
$$ LANGUAGE 'plpgsql' STABLE;
CREATE or REPLACE FUNCTION osml10n_get_country_n(feature geometry) RETURNS TEXT AS $$
SELECT country_code
from country_osm_grid
where st_contains(geometry, st_centroid(st_transform(feature,4326)))
order by area
limit 1;
$$ LANGUAGE SQL STABLE;
Results
Importing get_country.sql...
Running 2 functions x 10 runs x 10,000 each...
...
% slower Function AVG of 8 runs MIN MAX STDEV
---------- ------------------------------------------- --------------- -------------- -------------- ----------
2) osml10n_get_country_n({random_geopoint}) 0:00:01.751501 0:00:01.741989 0:00:01.759559 0.00634216
21.0% 1) osml10n_get_country_o({random_geopoint}) 0:00:02.118865 0:00:02.101815 0:00:02.138070 0.0137719
Use SQL instead of PLPGSQL to gain 20% of performance. Measured with
profile-pg-func
tool. To test locally. Must havedocker
,make
, andgit
..env
file to use tools version 5.1 instead of 5.0 (it hasn't been upgraded just yet)get-country.sql
make start-db
to create a new docker databasemake bash
to start toolsmake destroy-db
Results