Module: DbSerializer::Utilities::SQL

Defined in:
lib/db_serializer/utilities.rb

Overview

SQL utilities for spatial queries

Class Method Summary collapse

Class Method Details

.feature_collection(features, opts = {}) ⇒ String

This method returns a string containing a SQL query to build a GeoJSON FeatureCollection.

The result of the SQL query generated by this method contains one row with one column. It contains a serialized GeoJSON FeatureCollection.

Example of GeoJSON FeatureCollection: {"type": "FeatureCollection", "features": [{"id": 1, "type": "Feature", "geometry": {"type": "MultiLineString", "coordinates": [[[x,y]]]}, "properties": {"id": 1, "name": "Paris"}}]}

To learn more about FeatureCollections see the specifications: wiki.geojson.org/GeoJSON_draft_version_6#FeatureCollection

Examples:

features = MyActiveRecordModel.select(geojson_attribute([:id]))
query = feature_collection(features)
ActiveRecord::Base.connection.query_value(query)

Parameters:

  • features (String, ActiveRecord::Relation) —

    query of the features to include

  • opts (Hash) (defaults to: {})

Options Hash (opts):

  • :geojson_column (String, Symbol) —

    name of the column containing the GeoJSON features to include in the collection

  • :output_column (String, Symbol) —

    name of the output column

Returns:

  • (String) —

    SQL to get a FeatureCollection



39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
 
# File 'lib/db_serializer/utilities.rb', line 39

def feature_collection(features, opts = {})
  opts = {
    geojson_column: :geojson,
    output_column: :json
  }.merge(opts)

  geojson_column = opts[:geojson_column]
  output_column = opts[:output_column]
  inner_query = features.is_a?(String) ? features : features.to_sql

  <<-SQL
    SELECT jsonb_build_object(
      'type', 'FeatureCollection',
      'features', COALESCE(jsonb_agg(features.#{geojson_column}), '[]')
    ) AS #{output_column} FROM (#{inner_query}) features
  SQL
end

.geojson_attribute(columns, opts = {}) ⇒ String

This method returns a string containing part of a SQL query that can be used in a SELECT statement to add a GeoJSON feature column to a query.

The SQL column generated by this method contains a serialized GeoJSON feature.

Example of GeoJSON feature: {"id": 1, "type": "Feature", "geometry": {"type": "MultiLineString", "coordinates": [[[x,y]]]}, "properties": {"id": 1, "name": "Paris"}}

To learn more about GeoJSON features see the specifications: wiki.geojson.org/GeoJSON_draft_version_6#Feature

Examples:

with SQL:

geojson = DbSerializer::Utilities::SQL.geojson_attribute([:id])
records = ActiveRecord::Base.connection.exec_query(
  "SELECT id, geometry, #{geojson} FROM table"
)
record = records.first

# print a String containing a serialized GeoJSON feature, check example in the note
puts record['geojson']

with an ActiveRecord::Base

geojson = DbSerializer::Utilities::SQL.geojson_attribute([:id])
records = Model.select(geojson)
record = records.first

# print a String containing a serialized GeoJSON feature, check example in the note
puts record.geojson_before_type_cast

# print a Hash
puts record.geojson

Parameters:

  • columns (Array<String, Symbol>) —

    SQL columns to include in the properties of the GeoJSON feature, must be accessible in the underlying table

  • opts (Hash) (defaults to: {})

Options Hash (opts):

  • :geometry_column (Symbol) —

    defaults to :geometry

  • :output_column (Symbol) —

    defaults to :geojson

Returns:

  • (String) —

    SQL SELECT statement to add a column named geojson to a SQL query



100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
 
# File 'lib/db_serializer/utilities.rb', line 100

def geojson_attribute(columns, opts = {})
  opts = {
    geometry_column: :geometry,
    output_column: :geojson
  }.merge(opts)

  filtered_columns = columns.reject { |column| column.to_s == opts[:geometry_column].to_s }
  columns = filtered_columns.map { |column| "'#{column}',#{column}" }.join(',')

  geometry = "ST_AsGeoJSON(#{opts[:geometry_column]})::jsonb"
  properties = "json_build_object(#{columns})"
  output_column = opts[:output_column]

  <<-SQL
    jsonb_build_object(
      'type', 'Feature',
      'id', id,
      'geometry', #{geometry},
      'properties', #{properties}
    ) AS #{output_column}
  SQL
end