module DbSerializer::Utilities::SQL
SQL
utilities for spatial queries
Public Class Methods
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
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