Datasets

Here are the links used in the Evaluation in Section 3:

• amenity.tsv
• boundary.tsv
• building.tsv
• highway.tsv
• power.tsv.

Setup

Setup PostgreSQL and PostGIS on Ubuntu 24.04

Install the required packages:

sudo apt update
sudo apt install postgresql postgresql-contrib postgis postgresql-16-postgis-3 gdal-bin

Next, create a new database storage in a directory of your choice.

export POSTGIS_DIR=/local/data-ssd/postgis/spatialjoin
sudo mkdir -p ${POSTGIS_DIR} && sudo chown postgres:postgres ${POSTGIS_DIR}
sudo -u postgres /usr/lib/postgresql/16/bin/initdb -D ${POSTGIS_DIR}
sudo vim ${POSTGIS_DIR}/postgresql.conf

In the file ${POSTGIS_DIR}/postgresql.conf, set the following:

work_mem = 4MB
max_worker_processes = 8
max_parallel_workers_per_gather = 2
max_parallel_workers = 8

Afterwards, restart Postgres with the selected database storage directory:

sudo su - postgres -c "/usr/lib/postgresql/16/bin/pg_ctl -D ${POSTGIS_DIR} -l logfile start"

Create a database

Create a database spatialjoin_db and enable PostGIS.

sudo su - postgres -c "createdb spatialjoin_db"
psql -U postgres -d spatialjoin_db -c "CREATE EXTENSION postgis;"

Fill database tables with the data and build indexes

Download the data files linked above in the working directory. Then do the following:

export NAME=OSM
sudo -u postgres psql -d spatialjoin_db -c "DROP TABLE IF EXISTS ${NAME}; DROP TABLE IF EXISTS ${NAME}_loader;"
sudo -u postgres psql -d spatialjoin_db -c "CREATE TABLE ${NAME} (id VARCHAR PRIMARY KEY, class VARCHAR, type VARCHAR, geom GEOMETRY);"
sudo -u postgres psql -d spatialjoin_db -c "CREATE TABLE ${NAME}_loader (id VARCHAR, class VARCHAR, type VARCHAR, geom_text VARCHAR);"
for CLASS in boundary amenity power highway building; do echo -n "$(date '+%F %T') " && echo "$CLASS ..." && sudo -u postgres psql -d spatialjoin_db -c "COPY ${NAME}_loader FROM '$(pwd)/${CLASS}.tsv' WITH (FORMAT csv, DELIMITER E'\t', HEADER true);"; done
sudo -u postgres psql -d spatialjoin_db -c "INSERT INTO ${NAME} (id, class, type, geom) SELECT DISTINCT ON (id) id, class, type, ST_GeomFromText(geom_text, 4326) FROM ${NAME}_loader;"
sudo -u postgres psql -d spatialjoin_db -c "DROP table ${NAME}_loader;"
sudo -u postgres psql -d spatialjoin_db -c "SELECT COUNT(*) FROM ${NAME};"
sudo -u postgres psql -d spatialjoin_db -c "CREATE INDEX ${NAME}_geom_idx ON ${NAME} USING GIST (geom);"
sudo -u postgres psql -d spatialjoin_db -c "CREATE INDEX ${NAME}_id_idx ON ${NAME} (id);"
sudo -u postgres psql -d spatialjoin_db -c "CREATE INDEX ${NAME}_class_type_id_idx ON ${NAME} (class, type, id);"
sudo -u postgres psql -d spatialjoin_db -c "\dt+ public.*" -c "\di+ public.*"

Evaluate QLever+

To get the evaluation times for QLever, run the queries at the URLs provided below:

Q1, Q2, Q3, Q4, Q5.

Evaluate Postgres

To get the evaluation results for Postgres, do the following:

export NAME=osm
export DB=spatialjoin_db
sudo -u postgres psql -d $DB -c "SELECT COUNT(*) FROM osm o1, osm o2 WHERE o1.id = 'osmrelation:2171347' AND o2.class = 'power' AND ST_Contains(o1.geom, o2.geom);"
sudo -u postgres psql -d $DB -c "SELECT COUNT(*) FROM osm o1, osm o2 WHERE o1.id = 'osmrelation:51477' AND o2.class = 'power' AND ST_Contains(o1.geom, o2.geom);"
sudo -u postgres psql -d $DB -c "SELECT COUNT(*) FROM osm o1, osm o2 WHERE o1.class = 'building' AND o2.class = 'power' AND ST_Intersects(o1.geom, o2.geom);"
sudo -u postgres psql -d $DB -c "SELECT COUNT(*) FROM osm o1, osm o2 WHERE o1.class = 'highway' AND o1.type = 'residential' AND o2.class = 'highway' AND o2.type = 'motorway' AND ST_Intersects(o1.geom, o2.geom);"
sudo -u postgres psql -d $DB -c "SELECT o1.id AS region, COUNT(*) AS count FROM osm o1, osm o2 WHERE o1.class = 'boundary' AND o1.type = 'administrative' AND o2.class = 'amenity' AND o2.type = 'post_box' AND ST_Contains(o1.geom, o2.geom) GROUP BY o1.id ORDER BY count DESC LIMIT 1;"

Evaluate Postgres+

TODO: add Postgres+ queries

Evaluate Overpass

All queries were evaluated against the official Overpass API instance at overpass-turbo.eu.

Q1: All residential streets in Luxembourg

[out:json];
relation(2171347);
map_to_area;
way(area)[highway=residential];
out count;

Q2: All streets in Germany

[out:json];
relation(51477);
map_to_area;
way(area)[highway];
out count;

Q3: All buildings under a powerline

[out:json];
way[power=line]->.power_lines;
way[building]->.building;
way.power_lines(around.building:0);
out count;

Q4: All intersections between residential streets

[out:json];
[highway=residential]->.res_streets;
way.res_streets(around.res_streets:0);
out count;

Q5: Number of post boxes by country

[out:json];
rel[boundary][admin_level=2];
map_to_area;
foreach->.d(
  (.d;);out;
  node(area.d)[amenity=post_box];
  out count;
);