module DbSerializer::Utilities::SQL

SQL utilities for spatial queries

Public Class Methods

feature_collection(features, opts = {}) click to toggle source

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

@example

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

@param features [String, ActiveRecord::Relation] query of the features to include @param opts [Hash] @option opts [String, Symbol] :geojson_column name of the column containing

the GeoJSON features to include in the collection

@option opts [String, Symbol] :output_column name of the output column @return [String] SQL to get a FeatureCollection

# 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 = {}) click to toggle source

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

@example 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']

@example 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

@param columns [Array<String, Symbol>] SQL columns to include in the properties

of the GeoJSON feature, must be accessible in the underlying table

@param opts [Hash] @option opts [Symbol] :geometry_column defaults to :geometry @option opts [Symbol] :output_column defaults to :geojson @return [String] SQL SELECT statement to add a column named geojson to a SQL query

# 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