Module: OvertureMaps::Database

Defined in:
lib/overture_maps/database.rb

Overview

PostGIS helpers. Identifiers are quoted and values sanitized — nothing user-supplied is interpolated into SQL.

Class Method Summary collapse

Class Method Details

.bounding_box_query(table_name, south, west, north, east, column_name = "geometry") ⇒ Object 



41
42
43
44
45
46
47
 
# File 'lib/overture_maps/database.rb', line 41

def bounding_box_query(table_name, south, west, north, east, column_name = "geometry")
  exec_sanitized(
    "SELECT * FROM #{quote_ident(table_name)} " \
    "WHERE #{quote_ident(column_name)} && ST_MakeEnvelope(?, ?, ?, ?, 4326)::geography",
    Float(west), Float(south), Float(east), Float(north)
  )
end

.create_spatial_index(table_name, column_name = "geometry") ⇒ Object 



18
19
20
21
22
23
24
 
# File 'lib/overture_maps/database.rb', line 18

def create_spatial_index(table_name, column_name = "geometry")
  index = quote_ident("#{table_name}_#{column_name}_idx")
  connection.execute(
    "CREATE INDEX IF NOT EXISTS #{index} ON #{quote_ident(table_name)} " \
    "USING GIST (#{quote_ident(column_name)})"
  )
end

.drop_spatial_index(table_name, column_name = "geometry") ⇒ Object 



26
27
28
 
# File 'lib/overture_maps/database.rb', line 26

def drop_spatial_index(table_name, column_name = "geometry")
  connection.execute("DROP INDEX IF EXISTS #{quote_ident("#{table_name}_#{column_name}_idx")}")
end

.enable_postgis ⇒ Object 



14
15
16
 
# File 'lib/overture_maps/database.rb', line 14

def enable_postgis
  connection.execute("CREATE EXTENSION IF NOT EXISTS postgis")
end

.geometry_info(table_name, column_name = "geometry") ⇒ Object 



34
35
36
37
38
39
 
# File 'lib/overture_maps/database.rb', line 34

def geometry_info(table_name, column_name = "geometry")
  exec_sanitized(
    "SELECT type, srid FROM geometry_columns WHERE f_table_name = ? AND f_geometry_column = ?",
    table_name.to_s, column_name.to_s
  ).first
end

.nearest_neighbors(table_name, lat, lng, limit = 10, column_name = "geometry") ⇒ Object 



49
50
51
52
53
54
55
56
57
 
# File 'lib/overture_maps/database.rb', line 49

def nearest_neighbors(table_name, lat, lng, limit = 10, column_name = "geometry")
  column = quote_ident(column_name)
  exec_sanitized(
    "SELECT *, ST_Distance(#{column}, ST_SetSRID(ST_MakePoint(?, ?), 4326)::geography) AS distance " \
    "FROM #{quote_ident(table_name)} " \
    "ORDER BY #{column} <-> ST_SetSRID(ST_MakePoint(?, ?), 4326)::geography LIMIT ?",
    Float(lng), Float(lat), Float(lng), Float(lat), Integer(limit)
  )
end

.postgis_available? ⇒ Boolean

Returns:

  • (Boolean) 


8
9
10
11
12
 
# File 'lib/overture_maps/database.rb', line 8

def postgis_available?
  connection.extension_enabled?("postgis")
rescue StandardError
  false
end

.reindex_spatial(table_name, column_name = "geometry") ⇒ Object 



30
31
32
 
# File 'lib/overture_maps/database.rb', line 30

def reindex_spatial(table_name, column_name = "geometry")
  connection.execute("REINDEX INDEX #{quote_ident("#{table_name}_#{column_name}_idx")}")
end