I'm trying to get all the road data within a boundary and to load this data into R.
I compared Overpass vs PostGIS and PostGIS at least for me was a magnitude slower.
It is 116.322 sec vs. 7.937 sec :
Is it possible to get this PostGIS query faster? This was my approach:
library(osmdata)
library(rpostgis)
##PostGis
bb<-c( 11.23644, 47.80507, 11.88825, 48.24233)
conn <- RPostgreSQL::dbConnect("PostgreSQL", host = MYHOST,
dbname = "osm_data", user = "postgres", password = MYPASSWORD)
pgPostGIS(conn)
a<-pgGetGeom(conn, c("public", "planet_osm_roads"), geom = "way", gid = "osm_id",boundary = bb,
other.cols = TRUE, clauses = "WHERE highway = 'primary' OR highway = 'secondary' OR highway = 'tertiary'")
a<-st_as_sf(a)
nrow(a)
[1] 65881
#116.322 sec elapsed
##Overpass
my_osm_data <- opq(bbox = bb,timeout = 180,memsize = 104857600) %>%
add_osm_feature(
key = 'highway',
value = c("primary","secondary", "tertiary",
"!crossing","!bus_stop","!milestone",
"!platform","!rest_area","!speed_camera",
"!street_lamp","!services","!stop",
"!traffic_mirror","!traffic_signals","!cycleway","!path")) %>%
osmdata_sf(quiet = FALSE) %>%
unique_osmdata()
#7.937 sec elapsed
As you can see the difference between overpass and PostGIS is significant.
I was wondering if it would be possible to optimize the PostGIS query?
I think I have a mistake somewhere in my PostGIS query.
The PostGIS database is loaded with openstreetmap through osm2pgsql:
osm_data=# \d
List of relations
Schema | Name | Type | Owner
----------+--------------------+----------+----------
public | geography_columns | view | postgres
public | geometry_columns | view | postgres
public | planet_osm_line | table | postgres
public | planet_osm_nodes | table | postgres
public | planet_osm_point | table | postgres
public | planet_osm_polygon | table | postgres
public | planet_osm_rels | table | postgres
public | planet_osm_roads | table | postgres
public | planet_osm_ways | table | postgres
public | spatial_ref_sys | table | postgres
topology | layer | table | postgres
topology | topology | table | postgres
topology | topology_id_seq | sequence | postgres
schema_name table_name geom_column geometry_type type
1 public planet_osm_line way LINESTRING GEOMETRY
2 public planet_osm_point way POINT GEOMETRY
3 public planet_osm_polygon way GEOMETRY GEOMETRY
4 public planet_osm_roads way LINESTRING GEOMETRY
Table "public.planet_osm_roads"
Column | Type | Collation | Nullable | Default
--------------------+---------------------------+-----------+----------+---------
osm_id | bigint | | |
access | text | | |
addr:housename | text | | |
addr:housenumber | text | | |
addr:interpolation | text | | |
admin_level | text | | |
aerialway | text | | |
aeroway | text | | |
amenity | text | | |
area | text | | |
barrier | text | | |
bicycle | text | | |
brand | text | | |
bridge | text | | |
boundary | text | | |
building | text | | |
construction | text | | |
UPDATE:
I changed the way im doing a request to PostGIS:
library(sf)
library(RPostgreSQL)
library(tictoc)
pw <- MYPASSWORD
drv <- dbDriver("PostgreSQL")
con <- dbConnect(drv, dbname = "osm_data",
host = "localhost", port = 5432,
user = "postgres", password = pw)
tic()
sf_data = st_read(con, query = "SELECT osm_id,name,highway,way
FROM planet_osm_roads
WHERE highway = 'primary' OR highway = 'secondary' OR highway = 'tertiary'AND ST_Contains(
ST_Transform(
ST_MakeEnvelope(11.2364353533134,47.8050651144447,11.8882527806375,48.2423300001326,4326)
,3857)
,planet_osm_roads.way);")
toc()
RPostgreSQL::dbDisconnect(con)
