Postgres specific setup
# Create a master postgres and 5 worker on different disks and ports
sudo pg_createcluster 10 user_master -p 50506
sudo pg_createcluster 10 user_worker1 -p 50507 -d /home/user/postgres/worker1
sudo pg_createcluster 10 user_worker2 -p 50508 -d /home/user/postgres/worker2
sudo pg_createcluster 10 user_worker3 -p 50509 -d /diskb/user/postgres/worker3
sudo pg_createcluster 10 user_worker4 -p 50510 -d /diskc/user/postgres/worker4
sudo pg_createcluster 10 user_worker5 -p 50511 -d /diskd/user/postgres/worker5
# Start the postgres installations
sudo pg_ctlcluster 10 user_master start
sudo pg_ctlcluster 10 user_worker1 start
sudo pg_ctlcluster 10 user_worker2 start
sudo pg_ctlcluster 10 user_worker3 start
sudo pg_ctlcluster 10 user_worker4 start
sudo pg_ctlcluster 10 user_worker5 start
# Setup a user 'username' with password 'supersecret' (adjust the ports to your setup)
for i in 50506 50507 50508 50509 50510 50511; do sudo su - postgres -c "psql -p $i -c 'CREATE ROLE username LOGIN SUPERUSER PASSWORD 'supersecret';"; done
# Create a database named database
for i in 50506 50507 50508 50509 50510 50511; do sudo su - postgres -c "psql -p $i -c 'CREATE DATABASE database;'"; done
# List active postgres processes
pg_lsclusters
# Perform a SQL query on all local postgres worker installations
for i in 50507 50508 50509 50510 50511; do sudo su - postgres -c "psql -p $i -d database -c 'SELECT * FROM users'"; done
# Deinstallation
sudo pg_dropcluster --stop 10 user_master
sudo pg_dropcluster --stop 10 user_worker1
sudo pg_dropcluster --stop 10 user_worker2
sudo pg_dropcluster --stop 10 user_worker3
sudo pg_dropcluster --stop 10 user_worker4
sudo pg_dropcluster --stop 10 user_worker5
Data for tests
CREATE TABLE users (
id SERIAL PRIMARY KEY,
firstname VARCHAR(100) NOT NULL,
lastname VARCHAR(100) NULL,
age INTEGER
);
INSERT INTO users (firstname, lastname, age) values('Jan', 'Jansen', 25);
INSERT INTO users (firstname, lastname, age) values('Max', 'Maxen', 28);
INSERT INTO users (firstname, lastname, age) values('Klaus', 'Klausen', 38);
INSERT INTO users (firstname, lastname, age) values('Jörg', 'Jansen', 60);
INSERT INTO users (firstname, lastname, age) values('James', 'Jansen', 25);
INSERT INTO users (firstname, lastname, age) values('Klaus', 'Jansen', 53);
database=# select * from users;
id | firstname | lastname | age
----+-----------+----------+-----
1 | Jan | Jansen | 25
2 | Max | Maxen | 28
3 | Klaus | Klausen | 38
4 | Jörg | Jansen | 60
5 | James | Jansen | 25
6 | Klaus | Jansen | 53
(6 rows)
MySQL specific setup
To utilize multiple disks in one system, mysqld_multi can be used to start multiple MySQL-Servers with different data directories. Add the following lines to your /etc/mysql/mysql.cnf:
[mysqld_multi]
mysqld = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
log = /var/log/mysql/mysqld_multi.log
user = mysql
pass = secret
[mysqld1]
user = mysql
pid-file = /var/run/mysqld/mysqld1.pid
socket = /var/run/mysqld/mysqld1.sock
basedir = /usr
datadir = /home/user/mysql/worker1
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql
bind-address = 127.0.0.1
query_cache_size = 16M
log_error = /var/log/mysql/mysqld1_error.log
expire_logs_days = 10
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci
secure_file_priv = ""
port = 20101
[mysqld2]
user = mysql
pid-file = /var/run/mysqld/mysqld2.pid
socket = /var/run/mysqld/mysqld2.sock
basedir = /usr
datadir = /home/user/mysql/worker2
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql
bind-address = 127.0.0.1
query_cache_size = 16M
log_error = /var/log/mysql/mysqld2_error.log
expire_logs_days = 10
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci
secure_file_priv = ""
port = 20102
[mysqld3]
user = mysql
pid-file = /var/run/mysqld/mysqld3.pid
socket = /var/run/mysqld/mysqld3.sock
basedir = /usr
datadir = /diskb/user/mysql/worker3
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql
bind-address = 127.0.0.1
query_cache_size = 16M
log_error = /var/log/mysql/mysqld3_error.log
expire_logs_days = 10
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci
secure_file_priv = ""
port = 20103
[mysqld4]
user = mysql
pid-file = /var/run/mysqld/mysqld4.pid
socket = /var/run/mysqld/mysqld4.sock
basedir = /usr
datadir = /diskc/user/mysql/worker4
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql
bind-address = 127.0.0.1
query_cache_size = 16M
log_error = /var/log/mysql/mysqld4_error.log
expire_logs_days = 10
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci
secure_file_priv = ""
port = 20104
[mysqld5]
user = mysql
pid-file = /var/run/mysqld/mysqld5.pid
socket = /var/run/mysqld/mysqld5.sock
basedir = /usr
datadir = /diskd/user/mysql/worker5
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql
bind-address = 127.0.0.1
query_cache_size = 16M
log_error = /var/log/mysql/mysqld5_error.log
expire_logs_days = 10
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci
secure_file_priv = ""
port = 20105
You can list all your MySQL-Multi installations by calling mysqld_multi report.
If you are using AppArmour, add the following lines to your MySQL-AppArmour configuration /etc/apparmor.d/usr.sbin.mysqld:
/var/run/mysqld/ r,
/var/run/mysqld/** rwk,
/run/mysqld/ r,
/run/mysqld/** rwk,
and calling apparmor_parser -r /etc/apparmor.d/usr.sbin.mysqld.
Now the data directories can be created and initialized. In the following example, one MySQL master MySQL-installation (port 20101) and four worker MySQL-installations (port 20102 20103 20104 20105) are made:
mkdir -p /home/user/mysql/master
mkdir -p /home/user/mysql/worker1
mkdir -p /diskb/user/mysql/worker2
mkdir -p /diskc/user/mysql/worker3
mkdir -p /diskd/user/mysql/worker4
chown mysql.mysql /home/user/mysql/master
chown mysql.mysql /home/user/mysql/worker1
chown mysql.mysql /diskb/user/mysql/worker2
chown mysql.mysql /diskc/user/mysql/worker3
chown mysql.mysql /diskd/user/mysql/worker4
mysqld --initialize-insecure --user=mysql --datadir=/home/user/mysql/master
mysqld --initialize-insecure --user=mysql --datadir=/home/user/mysql/worker2
mysqld --initialize-insecure --user=mysql --datadir=/diskb/user/mysql/worker3
mysqld --initialize-insecure --user=mysql --datadir=/diskc/user/mysql/worker4
mysqld --initialize-insecure --user=mysql --datadir=/diskd/user/mysql/worker5
The MySQL installations can now be started by executing mysqld_multi start. The MySQL installations are now running.
# Setup the user for mysql shutdown (see mysqld_multi setting above)
#
# Note: '...' has to be replaced by concrete values
#
export MYSQL_USER=...
export MYSQL_PASSWORD=...
export MYSQL_MASTER_DB...
export MYSQL_WORKER_DB=...
for i in 20101 20102 20103 20104 20105; do mysql -u root -h 127.0.0.1 -P $i -e "GRANT SHUTDOWN ON *.* TO 'mysql'@'localhost' IDENTIFIED BY 'secret';"; done
# Setup a user for the basic engine
# MySQL dinstingushes between users that are connecting over a pipe (host=LOCALHOST) and users that are connecting
# over the network (HOST='%'). To be able to connect from both sources, the user has to created twice.
for i in 20101 20102 20103 20104 20105; do mysql -u root -h 127.0.0.1 -P $i -e "CREATE USER '$MYSQL_USER'@'localhost' IDENTIFIED BY '$MYSQL_PASSWORD';"; done
for i in 20101 20102 20103 20104 20105; do mysql -u root -h 127.0.0.1 -P $i -e "CREATE USER '$MYSQL_USER'@'%' IDENTIFIED BY '$MYSQL_PASSWORD';"; done
# Grant the file permission to the user
for i in 20101 20102 20103 20104 20105; do mysql -u root -h 127.0.0.1 -P $i -e "GRANT FILE ON *.* TO '$MYSQL_USER'@'localhost';"; done
for i in 20101 20102 20103 20104 20105; do mysql -u root -h 127.0.0.1 -P $i -e "GRANT FILE ON *.* TO '$MYSQL_USER'@'%';"; done
# Create the masterdb
mysql -u root -h 127.0.0.1 -P 20101 -e "CREATE DATABASE $MYSQL_MASTER_DB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"
# Create the workerdb for the basic engine (UTF-8 encoding is required for importing shapefiles later)
for i in 20102 20103 20104 20105; do mysql -u root -h 127.0.0.1 -P $i -e "CREATE DATABASE $MYSQL_WORKER_DB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"; done
# Grant the permissions for the master database
mysql -u root -h 127.0.0.1 -P 20101 -e "GRANT ALL ON $MYSQL_MASTER_DB.* TO '$MYSQL_USER'@'localhost';"
mysql -u root -h 127.0.0.1 -P 20101 -e "GRANT ALL ON $MYSQL_MASTER_DB.* TO '$MYSQL_USER'@'%';"
# Grant the permissions for the worker database
for i in 20102 20103 20104 20105; do mysql -u root -h 127.0.0.1 -P $i -e "GRANT ALL ON $MYSQL_WORKER_DB.* TO '$MYSQL_USER'@'localhost';"; done
for i in 20102 20103 20104 20105; do mysql -u root -h 127.0.0.1 -P $i -e "GRANT ALL ON $MYSQL_WORKER_DB.* TO '$MYSQL_USER'@'%';"; done
# Set a root password ('secret' should be replaced by a real password)
for i in 20101 20102 20103 20104 20105; do mysql -u root -h 127.0.0.1 -P $i -e "ALTER USER 'root'@'localhost' IDENTIFIED BY 'secret';"; done
for i in 20101 20102 20103 20104 20105; do mysql -u root -h 127.0.0.1 -P $i -e "ALTER USER 'root'@'localhost' IDENTIFIED BY '%';"; done
MySQL settings
MySQL restricts file imports and exports to one specific directory. However, the BasicEngine requires that each user can import/export tables from the user's home directory.
select * INTO outfile "/tmp/table" from users;
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
mysql> SHOW VARIABLES LIKE "secure_file_priv";
+------------------+-----------------------+
| Variable_name | Value |
+------------------+-----------------------+
| secure_file_priv | /var/lib/mysql-files/ |
+------------------+-----------------------+
To solve the problem, the setting secure_file_priv has to be disabled.
echo 'secure_file_priv=""' >> /etc/mysql/mysql.conf.d/mysqld.cnf
/etc/init.d/mysql restart
In addition, AppArmor can prevent MySQL from reading/writing files into the home directory of the users. This can be solved by adding additional rules to the AppArmour configuration (/etc/apparmor.d/usr.sbin.mysqld).
# Allow import / export of the SECONDO basic engine
@{HOME}/filetransfer/ r,
@{HOME}/filetransfer/** rwk,
# Additional SECONDO database directories should also be covered
#
# /diskb/ r,
# /diskb/** rwk,
After the configuration is changed, the changed AppArmour profile needs to be applied ( sudo apparmor_parser -r /etc/apparmor.d/usr.sbin.mysqld) and MySQL needs to be restarted (sudo service mysql restart).
Data for tests
CREATE TABLE users (
id int AUTO_INCREMENT,
firstname VARCHAR(100) NOT NULL,
lastname VARCHAR(100) NULL,
age INTEGER,
PRIMARY KEY (id)
);
INSERT INTO users (firstname, lastname, age) values('Jan', 'Jansen', 25);
INSERT INTO users (firstname, lastname, age) values('Max', 'Maxen', 28);
INSERT INTO users (firstname, lastname, age) values('Klaus', 'Klausen', 38);
INSERT INTO users (firstname, lastname, age) values('Jörg', 'Jansen', 60);
INSERT INTO users (firstname, lastname, age) values('James', 'Jansen', 25);
INSERT INTO users (firstname, lastname, age) values('Klaus', 'Jansen', 53);
mysql> select * from users;
+----+-----------+----------+------+
| id | firstname | lastname | age |
+----+-----------+----------+------+
| 1 | Jan | Jansen | 25 |
| 2 | Max | Maxen | 28 |
| 3 | Klaus | Klausen | 38 |
| 4 | Jörg | Jansen | 60 |
| 5 | James | Jansen | 25 |
| 6 | Klaus | Jansen | 53 |
+----+-----------+----------+------+
6 rows in set (0.01 sec)
BasicEngine
Connecting to Postgres
let WorkersPG = [const rel(tuple([Host: string, Port: int,
Config: string, DBUser: string, DBPass: string, DBPort: int, DBName: string])) value
(
("127.0.0.1" 50550 "SecondoConfig.ini" "user" "supersecret" 50507 "workerdb")
("127.0.0.1" 50551 "SecondoConfig.ini" "user" "supersecret" 50508 "workerdb")
("127.0.0.1" 50552 "SecondoConfig.ini" "user" "supersecret" 50509 "workerdb")
("127.0.0.1" 50553 "SecondoConfig.ini" "user" "supersecret" 50510 "workerdb")
("127.0.0.1" 50554 "SecondoConfig.ini" "user" "supersecret" 50511 "workerdb")
)]
query be_init_cluster('pgsql', 'user', 'supersecret', 50506, 'masterdb', WorkersPG)
Connecting to MySQL
let WorkersMySQL = [const rel(tuple([Host: string, Port: int,
Config: string, DBUser: string, DBPass: string, DBPort: int, DBName: string])) value
(
("127.0.0.1" 50550 "SecondoConfig.ini" "user" "supersecret" 13301 "workerdb")
("127.0.0.1" 50551 "SecondoConfig.ini" "user" "supersecret" 13302 "workerdb")
("127.0.0.1" 50552 "SecondoConfig.ini" "user" "supersecret" 13303 "workerdb")
("127.0.0.1" 50553 "SecondoConfig.ini" "user" "supersecret" 13304 "workerdb")
("127.0.0.1" 50554 "SecondoConfig.ini" "user" "supersecret" 13305 "workerdb")
)]
query be_init_cluster('mysql', 'user', 'supersecret', 13300, 'masterdb', WorkersMySQL)
Demo queries
# Partition relation roads, count elements and reduce result
query be_mcommand('CREATE EXTENSION postgis')
query be_command('CREATE EXTENSION postgis')
query be_mcommand("DROP TABLE roads")
query be_mcommand("DROP TABLE roads_count")
query be_part_hash("roads", "osm_id", 60)
query be_mquery('SELECT count(*) FROM roads', 'roads_count')
query be_union('roads_count');
query be_collect('SELECT * FROM roads_count') consume
query be_collect('SELECT * FROM roads_count') sum[Count]
query be_collect('SELECT count(*) FROM roads') consume
query be_partGrid(’roads’, ’gid’, ’geog’, 5.8, 50.3, 0.2, 20);
# Share table with all worker
query be_share('users');
# Validate the given query
query be_validate_query("SELECT * FROM users")
# Partition and re-partition releation roads
query be_part_hash("users", "firstname", 60)
query be_mcommand("DROP TABLE users")
query be_repart_hash("users", "firstname", 60)
query be_repart_hash("users", "lastname", 60)
query be_repart_hash("users", "age", 60)
# Build example grid
CALL ST_CREATE_STATIC_GRID(100, 100, 0.5, 0.5, 0, 0);
# List grid in MySQL
SELECT id, ST_AsWKT(cell), ST_SRID(cell) FROM grid_table;
# Partition spatial data (postgres)
query be_part_grid('water','gid', 'geog', 5.8, 50.3, 0.2, 20)
# Partition spatial data (MySQL)
query be_part_grid('water', 'OGR_FID', 'SHAPE', 5.8, 50.3, 0.2, 20)
Working with spatial data
In this section, the handling of spatial data with the BasicEngine is shown.
Import OSM Data (Postgres)
# Ensure Postgis is installed (contains shp2pgsql)
apt-get install postgis
# Download data
mkdir nrw_data
cd nrw_data
wget http://download.geofabrik.de/europe/germany/nordrhein-westfalen-latest-free.shp.zip
unzip nordrhein-westfalen-latest-free.shp.zip
# Convert to Postgres
# (-s = projection, -d = drop table -G = use the geography type)
# 4326 is WGS 84
shp2pgsql -s 4326 -d -G gis_osm_buildings_a_free_1 public.buildings > buildings.sql
shp2pgsql -s 4326 -d -G gis_osm_landuse_a_free_1 public.landuse > landuse.sql
shp2pgsql -s 4326 -d -G gis_osm_natural_free_1 public.natural > natural.sql
shp2pgsql -s 4326 -d -G gis_osm_places_free_1 public.places > places.sql
shp2pgsql -s 4326 -d -G gis_osm_pofw_a_free_1 public.pofw > pofw.sql
shp2pgsql -s 4326 -d -G gis_osm_pois_free_1 public.pois > pois.sql
shp2pgsql -s 4326 -d -G gis_osm_railways_free_1 public.railway > railway.sql
shp2pgsql -s 4326 -d -G gis_osm_roads_free_1 public.roads > roads.sql
shp2pgsql -s 4326 -d -G gis_osm_traffic_free_1 public.traffic > traffic.sql
shp2pgsql -s 4326 -d -G gis_osm_transport_free_1 public.transport > transport.sql
shp2pgsql -s 4326 -d -G gis_osm_water_a_free_1 public.water > water.sql
shp2pgsql -s 4326 -d -G gis_osm_waterways_free_1 public.waterways > waterways.sql
# Import SQL into Postgres
for i in *.sql; do
echo "Importing $i"
psql -h localhost -p 5432 -U <USER> -d <DATABASE> -f $i
done
Import OSM Data (MySQL)
Shapefiles are loaded into MySQL using ogr2ogr.
# Ensure gdal-bin is installed (contains ogr2ogr and ogrinfo)
apt-get install gdal-bin
# Download data
mkdir nrw_data
cd nrw_data
wget http://download.geofabrik.de/europe/germany/nordrhein-westfalen-latest-free.shp.zip
unzip nordrhein-westfalen-latest-free.shp.zip
export MYSQL_USER=...
export MYSQL_PASS=...
export MYSQL_DB=masterdb
export MYSQL_MASTER_PORT=20101
# Import shapefiles into MySQL
# (-nln layername)
# (-skipfailures to ignore some UTF-8 encoding errors)
#
# Use the MyISAM storage engine for performance reasons.
# ogr2ogr does not encapsulate the inserts in bulk transactions.
# Therefore, one transaction per insert would be performed when
# the INNODB storage engine is used, which leads to poor import
# performance.
#
# 'natural' is a reserved keyword in SQL. Therefore, the natural data
# is imported into the table natural_data
#
ogr2ogr -f MySQL MySQL:$MYSQL_DB,user=$MYSQL_USER,password=$MYSQL_PASS,port=$MYSQL_MASTER_PORT,host=127.0.0.1 gis_osm_buildings_a_free_1.shp -nln buildings -update -overwrite -lco engine=MyISAM -skipfailures
ogr2ogr -f MySQL MySQL:$MYSQL_DB,user=$MYSQL_USER,password=$MYSQL_PASS,port=$MYSQL_MASTER_PORT,host=127.0.0.1 gis_osm_landuse_a_free_1.shp -nln landuse -update -overwrite -lco engine=MyISAM -skipfailures
ogr2ogr -f MySQL MySQL:$MYSQL_DB,user=$MYSQL_USER,password=$MYSQL_PASS,port=$MYSQL_MASTER_PORT,host=127.0.0.1 gis_osm_natural_free_1.shp -nln natural_data -update -overwrite -lco engine=MyISAM -skipfailures
ogr2ogr -f MySQL MySQL:$MYSQL_DB,user=$MYSQL_USER,password=$MYSQL_PASS,port=$MYSQL_MASTER_PORT,host=127.0.0.1 gis_osm_places_free_1.shp -nln places -update -overwrite -lco engine=MyISAM -skipfailures
ogr2ogr -f MySQL MySQL:$MYSQL_DB,user=$MYSQL_USER,password=$MYSQL_PASS,port=$MYSQL_MASTER_PORT,host=127.0.0.1 gis_osm_pofw_a_free_1.shp -nln pofw -update -overwrite -lco engine=MyISAM -skipfailures
ogr2ogr -f MySQL MySQL:$MYSQL_DB,user=$MYSQL_USER,password=$MYSQL_PASS,port=$MYSQL_MASTER_PORT,host=127.0.0.1 gis_osm_pois_free_1.shp -nln pois -update -overwrite -lco engine=MyISAM -skipfailures
ogr2ogr -f MySQL MySQL:$MYSQL_DB,user=$MYSQL_USER,password=$MYSQL_PASS,port=$MYSQL_MASTER_PORT,host=127.0.0.1 gis_osm_railways_free_1.shp -nln railways -update -overwrite -lco engine=MyISAM -skipfailures
ogr2ogr -f MySQL MySQL:$MYSQL_DB,user=$MYSQL_USER,password=$MYSQL_PASS,port=$MYSQL_MASTER_PORT,host=127.0.0.1 gis_osm_roads_free_1.shp -nln roads -update -overwrite -lco engine=MyISAM -skipfailures
ogr2ogr -f MySQL MySQL:$MYSQL_DB,user=$MYSQL_USER,password=$MYSQL_PASS,port=$MYSQL_MASTER_PORT,host=127.0.0.1 gis_osm_traffic_free_1.shp -nln traffic -update -overwrite -lco engine=MyISAM -skipfailures
ogr2ogr -f MySQL MySQL:$MYSQL_DB,user=$MYSQL_USER,password=$MYSQL_PASS,port=$MYSQL_MASTER_PORT,host=127.0.0.1 gis_osm_transport_free_1.shp -nln transport -update -overwrite -lco engine=MyISAM -skipfailures
ogr2ogr -f MySQL MySQL:$MYSQL_DB,user=$MYSQL_USER,password=$MYSQL_PASS,port=$MYSQL_MASTER_PORT,host=127.0.0.1 gis_osm_water_a_free_1.shp -nln water -update -overwrite -lco engine=MyISAM -skipfailures
ogr2ogr -f MySQL MySQL:$MYSQL_DB,user=$MYSQL_USER,password=$MYSQL_PASS,port=$MYSQL_MASTER_PORT,host=127.0.0.1 gis_osm_waterways_free_1.shp -nln waterways -update -overwrite -lco engine=MyISAM -skipfailures
# Change SRID to 4326 (WGS84)
for i in buildings landuse natural_data places pofw pois railways roads traffic transport water waterways; do
echo "Fixig the spatial reference identifier (SRID) of the spatial data in table $i"
mysql -u $MYSQL_USER -p$MYSQL_PASS --port $MYSQL_MASTER_PORT -h 127.0.0.1 $MYSQL_DB -e "UPDATE $i SET SHAPE = ST_GeomFromText(ST_AsText(SHAPE), 4326);"
done
# Change storage backend in MySQL to InnoDB
for i in buildings landuse natural_data places pofw pois railways roads traffic transport water waterways; do
echo "Converting table $i to INNODB storage engine"
mysql -u $MYSQL_USER -p$MYSQL_PASS --port $MYSQL_MASTER_PORT -h 127.0.0.1 $MYSQL_DB -e "ALTER TABLE $i ENGINE=InnoDB;"
done
Retrieve summary information
ogrinfo MySQL:$MYSQL_DB,user=$MYSQL_USER,password=$MYSQL_PASS,port=$MYSQL_MASTER_PORT,host=127.0.0.1 buildings -so
Layer name: buildings
Geometry: Polygon
Feature Count: 116182
Extent: (5.866689, 50.363445) - (9.409564, 52.531316)
Layer SRS WKT:
GEOGCS["GCS_WGS_1984",
DATUM["WGS_1984",
SPHEROID["WGS_84",6378137,298.257223563]],
PRIMEM["Greenwich",0],
UNIT["Degree",0.017453292519943295],
AUTHORITY["EPSG","4326"]]
FID Column = OGR_FID
Geometry Column NOT NULL =
osm_id: String (10.0)
code: Real (4.0)
fclass: String (28.0)
name: String (100.0)
type: String (20.0)
# Fetch information about the SIRD 4326
SELECT * FROM INFORMATION_SCHEMA.ST_SPATIAL_REFERENCE_SYSTEMS WHERE SRS_ID = 4326\G
# Get the SRID of the spatial data
SELECT ST_SRID(SHAPE) FROM waterways LIMIT 1;