rails清理

pvcm50d1 于 2021-09-29 发布在 Java

关注(0)|答案(1)|浏览(471)

我试图用子查询清理sql查询，但每次都会出现语法错误或无效几何体错误。对于清理，我使用清理sql数组函数。
SQL查询

SELECT ST_Distance('SRID=4326;POINT(0 0)'::geometry, subqry.centroid)
FROM 
 (SELECT ST_AsText(ST_Centroid('SRID=4326;MULTIPOINT ( 0 0, 0 0, 0 0, 0 0 )'))::geography as centroid
 ) as subqry;

第一种方法

ActiveRecord::Base::sanitize_sql_array(["select ST_Distance('SRID=4326;POINT(? ?)'::geometry, :sub_query", sub_query: "(SELECT ST_Centroid(SRID=4326;'MULTIPOINT ( ? ?, ? ?, ? ?, ? ? )'::geometry) as centroid) as sub_query", min_longitude, min_latitude, min_longitude, min_latitude, min_longitude, max_latitude, max_longitude, min_latitude, max_longitude, max_latitude])

第二种方法
这里我得到“srid=4326；se“<--几何体中位置13处的分析错误

ActiveRecord::Base::sanitize_sql_array(["ST_Distance('SRID=4326;POINT(? ?)'::geometry, 'SRID=4326; SELECT ST_AsText(ST_Centroid(MULTIPOINT ( ? ?, ? ?, ? ?, ? ? ))) as centroid'::geometry)", min_longitude, min_latitude, min_longitude, min_latitude, min_longitude, max_latitude, max_longitude, min_latitude, max_longitude, max_latitude])

postgis中的st_质心&st_距离法示例

st_centroid

SELECT ST_AsText(ST_Centroid('MULTIPOINT ( -1 0, -1 2, -1 3, -1 4, -1 7, 0 1, 0 3, 1 1, 2 0, 6 0, 7 8, 9 8, 10 6 )'));

st_distance

SELECT ST_Distance(
        'SRID=4326;POINT(-72.1235 42.3521)'::geometry,
        'SRID=4326;LINESTRING(-72.1260 42.45, -72.123 42.1546)'::geometry
    );

sql ruby-on-rails ruby ruby-on-rails-6 sanitization

来源：https://stackoverflow.com/questions/68498332/rails-sanitize-for-subquery

1条答案

按热度按时间

but5z9lq1#

你可以通过一些小技巧和大量arel（这将为你处理卫生问题）构建任何你想要的查询。
警告：这可能看起来不漂亮，但它可以非常灵活，并应产生预期的结果(这只会产生所需的查询，如帖子所示（我不知道所需的语法是否正确，因为我从未使用过postgis）

class PostGISQuery
   def initialize(srid,points,point)
     @srid = srid
     @points = points 
     @point = point 
   end 

   def build_query
     return @query if @query
     multi_point = Arel::Nodes::NamedFunction.new("MULTIPOINT",[lat_long(@points)])
     centroid = Arel::Nodes::NamedFunction.new('ST_AsText',[
            Arel::Nodes::NamedFunction.new('ST_Centroid',[
                Arel.sql("SRID=#{@srid};#{multi_point.to_sql}")
            ])
         ])
     mgr = Arel::SelectManager.new 
     mgr.project(cast_geometry(centroid).as('centroid'))
     table = Arel::Table.new('subqry')
     answer= Arel::Nodes::NamedFunction.new('ST_Distance',[
        cast_geometry(Arel.sql("SRID=#{@srid};#{Arel::Nodes::NamedFunction.new('POINT',lat_long([@point])).to_sql}")),
        table[:centroid]
     ])
     mgr2 = Arel::SelectManager.new(Arel::Nodes::As.new(mgr,Arel.sql(table.name)))
     @query = mgr2.project(answer.as('answer'))
   end 

   def execute 
     ActiveRecord::Base.connection.exec_query(build_query.to_sql)
   end
   private 
     def cast_geometry(arel)
       Arel::Nodes::NamedFunction.new("CAST",[arel.as('geometry')])
     end

     def lat_long(arr)
       arr.map {|a| Arel.sql(a.join(' '))}
     end
 end

使用

srid = 4326
points = [[1, 0], [-1, 2], [-1, 3], [-1, 4], [-1, 7], [0, 1], [0, 3], [1, 1], [2, 0], [6, 0], [7, 8], [9, 8], [10, 6]]
point = [-72.1235, 42.3521]
qry = PostGISQuery.new(srid,points,point) 
qry.execute

# => ActiveRecord::Result

sql生成：

SELECT 
  ST_Distance(
    CAST('SRID=4326;POINT(-72.1235 42.3521)' AS geometry), 
    "subqry"."centroid") AS answer 
  FROM (
    SELECT 
      CAST(ST_AsText(
             ST_Centroid(
               'SRID=4326;MULTIPOINT(1 0, -1 2, -1 3, -1 4, -1 7, 0 1, 0 3, 1 1, 2 0, 6 0, 7 8, 9 8, 10 6)'
             )
           ) AS geometry) AS centroid
    ) AS subqry

赞(0）回复(0）举报 2021-09-29

我来回答

rails清理

1条答案

相关问题

热门标签

最新问答