From: Tom Hughes Date: Sun, 20 Oct 2019 18:13:30 +0000 (+0100) Subject: Merge remote-tracking branch 'upstream/pull/2383' X-Git-Tag: live~2395 X-Git-Url: https://git.openstreetmap.org/rails.git/commitdiff_plain/9508368d37fe6e9d54a2d51ed8d886261ec4a9e9?hp=1bf2a540d06cc4c51648d821fe39488f763003b7 Merge remote-tracking branch 'upstream/pull/2383' --- diff --git a/.travis.yml b/.travis.yml index e4c81e4c1..7137c2268 100644 --- a/.travis.yml +++ b/.travis.yml @@ -19,11 +19,7 @@ before_script: - sed -e "/idle_in_transaction_session_timeout/d" -e 's/ IMMUTABLE / /' -e "s/AS '.*libpgosm.*',/AS 'libpgosm',/" -e "/^--/d" db/structure.sql > db/structure.expected - psql -U postgres -c "CREATE DATABASE openstreetmap" - psql -U postgres -c "CREATE EXTENSION btree_gist" openstreetmap - - make -C db/functions libpgosm.so - - ln db/functions/libpgosm.so /tmp - - psql -U postgres -c "CREATE FUNCTION maptile_for_point(int8, int8, int4) RETURNS int4 AS '/tmp/libpgosm', 'maptile_for_point' LANGUAGE C STRICT" openstreetmap - - psql -U postgres -c "CREATE FUNCTION tile_for_point(int4, int4) RETURNS int8 AS '/tmp/libpgosm', 'tile_for_point' LANGUAGE C STRICT" openstreetmap - - psql -U postgres -c "CREATE FUNCTION xid_to_int4(xid) RETURNS int4 AS '/tmp/libpgosm', 'xid_to_int4' LANGUAGE C STRICT" openstreetmap + - psql -U postgres -f db/functions/functions.sql openstreetmap - cp config/travis.database.yml config/database.yml - cp config/example.storage.yml config/storage.yml - touch config/settings.local.yml diff --git a/CONFIGURE.md b/CONFIGURE.md index 91a64119e..a78162b67 100644 --- a/CONFIGURE.md +++ b/CONFIGURE.md @@ -131,3 +131,5 @@ If you want to deploy The Rails Port for production use, you'll need to make a f * The included version of the GPX importer is slow and/or completely inoperable. You should consider using [the high-speed GPX importer](https://git.openstreetmap.org/gpx-import.git/). * Make sure you generate the i18n files and precompile the production assets: `RAILS_ENV=production rake i18n:js:export assets:precompile` * Make sure the web server user as well as the rails user can read, write and create directories in `tmp/`. +* If you want to use diff replication then you will need to install the shared library special SQL functions for the `xid_to_int4` function, for which there is no pure SQL alternative. (See the bottom of [INSTALL.md](INSTALL.md)) +* If you expect to serve a lot of `/changes` API calls, then you might also want to install the shared library versions of the SQL functions. diff --git a/INSTALL.md b/INSTALL.md index cdf3bc556..4c2c7c6c8 100644 --- a/INSTALL.md +++ b/INSTALL.md @@ -31,8 +31,8 @@ sudo apt-get update sudo apt-get install ruby2.5 libruby2.5 ruby2.5-dev bundler \ libmagickwand-dev libxml2-dev libxslt1-dev nodejs \ apache2 apache2-dev build-essential git-core phantomjs \ - postgresql postgresql-contrib libpq-dev postgresql-server-dev-all \ - libsasl2-dev imagemagick libffi-dev libgd-dev libarchive-dev libbz2-dev + postgresql postgresql-contrib libpq-dev libsasl2-dev \ + imagemagick libffi-dev libgd-dev libarchive-dev libbz2-dev sudo gem2.5 install bundler ``` @@ -46,7 +46,7 @@ For Fedora, you can install the minimum requirements with: sudo dnf install ruby ruby-devel rubygem-rdoc rubygem-bundler rubygems \ libxml2-devel js \ gcc gcc-c++ git \ - postgresql postgresql-server postgresql-contrib postgresql-devel \ + postgresql postgresql-server postgresql-contrib \ perl-podlators ImageMagick libffi-devel gd-devel libarchive-devel \ bzip2-devel nodejs-yarn ``` @@ -182,20 +182,10 @@ psql -d openstreetmap -c "CREATE EXTENSION btree_gist" ### PostgreSQL Functions -We need to install special functions into the PostgreSQL databases, and these are provided by a library that needs compiling first. +We need to install some special functions into the PostgreSQL database: ``` -cd db/functions -make libpgosm.so -cd ../.. -``` - -Then we create the functions within each database. We're using `pwd` to substitute in the current working directory, since PostgreSQL needs the full path. - -``` -psql -d openstreetmap -c "CREATE FUNCTION maptile_for_point(int8, int8, int4) RETURNS int4 AS '`pwd`/db/functions/libpgosm', 'maptile_for_point' LANGUAGE C STRICT" -psql -d openstreetmap -c "CREATE FUNCTION tile_for_point(int4, int4) RETURNS int8 AS '`pwd`/db/functions/libpgosm', 'tile_for_point' LANGUAGE C STRICT" -psql -d openstreetmap -c "CREATE FUNCTION xid_to_int4(xid) RETURNS int4 AS '`pwd`/db/functions/libpgosm', 'xid_to_int4' LANGUAGE C STRICT" +psql -d openstreetmap -f db/functions/functions.sql ``` ### Database structure @@ -233,3 +223,44 @@ Note that the OSM map tiles you see aren't created from your local database - th # Configuration After installing this software, you may need to carry out some [configuration steps](CONFIGURE.md), depending on your tasks. + +# Installing compiled shared library database functions (optional) + +There are special database functions required by a (little-used) API call, the migrations and diff replication. The former two are provided as *either* pure SQL functions or a compiled shared library. The SQL versions are installed as part of the recommended install procedure above and the shared library versions are recommended only if you are running a production server making a lot of `/changes` API calls or need the diff replication functionality. + +If you aren't sure which you need, stick with the SQL versions. + +Before installing the functions, it's necessary to install the PostgreSQL server development packages. On Ubuntu this means: + +``` +sudo apt-get install postgresql-server-dev-all +``` + +On Fedora: + +``` +sudo dnf install postgresql-devel +``` + +The library then needs compiling. + +``` +cd db/functions +make libpgosm.so +cd ../.. +``` + +If you previously installed the SQL versions of these functions, we'll need to delete those before adding the new ones: + +``` +psql -d openstreetmap -c "DROP FUNCTION IF EXISTS maptile_for_point" +psql -d openstreetmap -c "DROP FUNCTION IF EXISTS tile_for_point" +``` + +Then we create the functions within each database. We're using `pwd` to substitute in the current working directory, since PostgreSQL needs the full path. + +``` +psql -d openstreetmap -c "CREATE FUNCTION maptile_for_point(int8, int8, int4) RETURNS int4 AS '`pwd`/db/functions/libpgosm', 'maptile_for_point' LANGUAGE C STRICT" +psql -d openstreetmap -c "CREATE FUNCTION tile_for_point(int4, int4) RETURNS int8 AS '`pwd`/db/functions/libpgosm', 'tile_for_point' LANGUAGE C STRICT" +psql -d openstreetmap -c "CREATE FUNCTION xid_to_int4(xid) RETURNS int4 AS '`pwd`/db/functions/libpgosm', 'xid_to_int4' LANGUAGE C STRICT" +``` diff --git a/db/functions/functions.sql b/db/functions/functions.sql new file mode 100644 index 000000000..5ed00ea63 --- /dev/null +++ b/db/functions/functions.sql @@ -0,0 +1,70 @@ +-------------------------------------------------------------------------------- +-- SQL versions of the C database functions. +-- +-- Pure pl/pgsql versions are *slower* than the C versions, and not recommended +-- for production use. However, they are significantly easier to install, and +-- require fewer dependencies. +-------------------------------------------------------------------------------- + +-- tile_for_point function returns a Morton-encoded integer representing a z16 +-- tile which contains the given (scaled_lon, scaled_lat) coordinate. Note that +-- these are passed into the function as (lat, lon) and should be scaled by +-- 10^7. +-- +-- The Morton encoding packs two dimensions down to one with fairly good +-- spatial locality, and can be used to index points without the need for a +-- proper 2D index. +CREATE OR REPLACE FUNCTION tile_for_point(scaled_lat int4, scaled_lon int4) + RETURNS int8 + AS $$ +DECLARE + x int8; -- quantized x from lon, + y int8; -- quantized y from lat, +BEGIN + x := round(((scaled_lon / 10000000.0) + 180.0) * 65535.0 / 360.0); + y := round(((scaled_lat / 10000000.0) + 90.0) * 65535.0 / 180.0); + + -- these bit-masks are special numbers used in the bit interleaving algorithm. + -- see https://graphics.stanford.edu/~seander/bithacks.html#InterleaveBMN + -- for the original algorithm and more details. + x := (x | (x << 8)) & 16711935; -- 0x00FF00FF + x := (x | (x << 4)) & 252645135; -- 0x0F0F0F0F + x := (x | (x << 2)) & 858993459; -- 0x33333333 + x := (x | (x << 1)) & 1431655765; -- 0x55555555 + + y := (y | (y << 8)) & 16711935; -- 0x00FF00FF + y := (y | (y << 4)) & 252645135; -- 0x0F0F0F0F + y := (y | (y << 2)) & 858993459; -- 0x33333333 + y := (y | (y << 1)) & 1431655765; -- 0x55555555 + + RETURN (x << 1) | y; +END; +$$ LANGUAGE plpgsql IMMUTABLE; + + +-- maptile_for_point returns an integer representing the tile at the given zoom +-- which contains the point (scaled_lon, scaled_lat). Note that the arguments +-- are in the order (lat, lon), and should be scaled by 10^7. +-- +-- The maptile_for_point function is used only for grouping the results of the +-- (deprecated?) /changes API call. Please don't use it for anything else, as +-- it might go away in the future. +CREATE OR REPLACE FUNCTION maptile_for_point(scaled_lat int8, scaled_lon int8, zoom int4) + RETURNS int4 + AS $$ +DECLARE + lat CONSTANT DOUBLE PRECISION := scaled_lat / 10000000.0; + lon CONSTANT DOUBLE PRECISION := scaled_lon / 10000000.0; + zscale CONSTANT DOUBLE PRECISION := 2.0 ^ zoom; + pi CONSTANT DOUBLE PRECISION := 3.141592653589793; + r_per_d CONSTANT DOUBLE PRECISION := pi / 180.0; + x int4; + y int4; +BEGIN + -- straight port of the C code. see db/functions/maptile.c + x := floor((lon + 180.0) * zscale / 360.0); + y := floor((1.0 - ln(tan(lat * r_per_d) + 1.0 / cos(lat * r_per_d)) / pi) * zscale / 2.0); + + RETURN (x << zoom) | y; +END; +$$ LANGUAGE plpgsql IMMUTABLE; diff --git a/db/structure.sql b/db/structure.sql index 47f3cf7a0..dce83214d 100644 --- a/db/structure.sql +++ b/db/structure.sql @@ -132,27 +132,57 @@ CREATE TYPE public.user_status_enum AS ENUM ( -- Name: maptile_for_point(bigint, bigint, integer); Type: FUNCTION; Schema: public; Owner: - -- -CREATE FUNCTION public.maptile_for_point(bigint, bigint, integer) RETURNS integer - LANGUAGE c STRICT - AS '$libdir/libpgosm.so', 'maptile_for_point'; +CREATE FUNCTION public.maptile_for_point(scaled_lat bigint, scaled_lon bigint, zoom integer) RETURNS integer + LANGUAGE plpgsql IMMUTABLE + AS $$ +DECLARE + lat CONSTANT DOUBLE PRECISION := scaled_lat / 10000000.0; + lon CONSTANT DOUBLE PRECISION := scaled_lon / 10000000.0; + zscale CONSTANT DOUBLE PRECISION := 2.0 ^ zoom; + pi CONSTANT DOUBLE PRECISION := 3.141592653589793; + r_per_d CONSTANT DOUBLE PRECISION := pi / 180.0; + x int4; + y int4; +BEGIN + -- straight port of the C code. see db/functions/maptile.c + x := floor((lon + 180.0) * zscale / 360.0); + y := floor((1.0 - ln(tan(lat * r_per_d) + 1.0 / cos(lat * r_per_d)) / pi) * zscale / 2.0); + + RETURN (x << zoom) | y; +END; +$$; -- -- Name: tile_for_point(integer, integer); Type: FUNCTION; Schema: public; Owner: - -- -CREATE FUNCTION public.tile_for_point(integer, integer) RETURNS bigint - LANGUAGE c STRICT - AS '$libdir/libpgosm.so', 'tile_for_point'; - - --- --- Name: xid_to_int4(xid); Type: FUNCTION; Schema: public; Owner: - --- - -CREATE FUNCTION public.xid_to_int4(xid) RETURNS integer - LANGUAGE c IMMUTABLE STRICT - AS '$libdir/libpgosm.so', 'xid_to_int4'; +CREATE FUNCTION public.tile_for_point(scaled_lat integer, scaled_lon integer) RETURNS bigint + LANGUAGE plpgsql IMMUTABLE + AS $$ +DECLARE + x int8; -- quantized x from lon, + y int8; -- quantized y from lat, +BEGIN + x := round(((scaled_lon / 10000000.0) + 180.0) * 65535.0 / 360.0); + y := round(((scaled_lat / 10000000.0) + 90.0) * 65535.0 / 180.0); + + -- these bit-masks are special numbers used in the bit interleaving algorithm. + -- see https://graphics.stanford.edu/~seander/bithacks.html#InterleaveBMN + -- for the original algorithm and more details. + x := (x | (x << 8)) & 16711935; -- 0x00FF00FF + x := (x | (x << 4)) & 252645135; -- 0x0F0F0F0F + x := (x | (x << 2)) & 858993459; -- 0x33333333 + x := (x | (x << 1)) & 1431655765; -- 0x55555555 + + y := (y | (y << 8)) & 16711935; -- 0x00FF00FF + y := (y | (y << 4)) & 252645135; -- 0x0F0F0F0F + y := (y | (y << 2)) & 858993459; -- 0x33333333 + y := (y | (y << 1)) & 1431655765; -- 0x55555555 + + RETURN (x << 1) | y; +END; +$$; SET default_tablespace = ''; diff --git a/script/vagrant/setup/provision.sh b/script/vagrant/setup/provision.sh index c181376c9..5eb49b24e 100644 --- a/script/vagrant/setup/provision.sh +++ b/script/vagrant/setup/provision.sh @@ -19,7 +19,7 @@ apt-get upgrade -y apt-get install -y ruby2.5 libruby2.5 ruby2.5-dev \ libmagickwand-dev libxml2-dev libxslt1-dev nodejs \ apache2 apache2-dev build-essential git-core phantomjs \ - postgresql postgresql-contrib libpq-dev postgresql-server-dev-all \ + postgresql postgresql-contrib libpq-dev \ libsasl2-dev imagemagick libffi-dev libgd-dev libarchive-dev libbz2-dev gem2.5 install rake gem2.5 install --version "~> 1.16.2" bundler @@ -38,18 +38,29 @@ if [ "$db_user_exists" != "1" ]; then sudo -u vagrant psql -c "create extension btree_gist" openstreetmap sudo -u vagrant psql -c "create extension btree_gist" osm_test fi -# build and set up postgres extensions -pushd db/functions -sudo -u vagrant make -sudo -u vagrant psql openstreetmap -c "CREATE OR REPLACE FUNCTION maptile_for_point(int8, int8, int4) RETURNS int4 AS '/srv/openstreetmap-website/db/functions/libpgosm.so', 'maptile_for_point' LANGUAGE C STRICT" -sudo -u vagrant psql openstreetmap -c "CREATE OR REPLACE FUNCTION tile_for_point(int4, int4) RETURNS int8 AS '/srv/openstreetmap-website/db/functions/libpgosm.so', 'tile_for_point' LANGUAGE C STRICT" -sudo -u vagrant psql openstreetmap -c "CREATE OR REPLACE FUNCTION xid_to_int4(xid) RETURNS int4 AS '/srv/openstreetmap-website/db/functions/libpgosm.so', 'xid_to_int4' LANGUAGE C STRICT" -popd + + +# install PostgreSQL functions +sudo -u vagrant psql -d openstreetmap -f db/functions/functions.sql +################################################################################ +# *IF* you want a vagrant image which supports replication (or perhaps you're +# using this script to provision some other server and want replication), then +# uncomment the following lines (until popd) and comment out the one above +# (functions.sql). +################################################################################ +#pushd db/functions +#sudo -u vagrant make +#sudo -u vagrant psql openstreetmap -c "CREATE OR REPLACE FUNCTION maptile_for_point(int8, int8, int4) RETURNS int4 AS '/srv/openstreetmap-website/db/functions/libpgosm.so', 'maptile_for_point' LANGUAGE C ST#RICT" +#sudo -u vagrant psql openstreetmap -c "CREATE OR REPLACE FUNCTION tile_for_point(int4, int4) RETURNS int8 AS '/srv/openstreetmap-website/db/functions/libpgosm.so', 'tile_for_point' LANGUAGE C STRICT" +#sudo -u vagrant psql openstreetmap -c "CREATE OR REPLACE FUNCTION xid_to_int4(xid) RETURNS int4 AS '/srv/openstreetmap-website/db/functions/libpgosm.so', 'xid_to_int4' LANGUAGE C STRICT" +#popd + + # set up sample configs if [ ! -f config/database.yml ]; then sudo -u vagrant cp config/example.database.yml config/database.yml fi touch config/settings.local.yml # migrate the database to the latest version -sudo -u vagrant rake db:migrate +sudo -u vagrant bundle exec rake db:migrate popd