Ruby Rails Postgis - 在多边形中查找所有点

Question

我需要一些关于使用activerecord-postgis-adapter在rails中构建sql查询的帮助。我已经阅读了很多资料，但现在有点卡壳了，非常感谢任何帮助。

我有两个模型Events和Areas：

Events有一个'geometry'列，类型为Point。

class Event < ActiveRecord::Base
  self.rgeo_factory_generator = RGeo::Geos.factory_generator    
end

t.spatial  "geometry", :limit => {:srid=>4326, :type=>"polygon", :geographic=>true}

区域具有一个“geometry”列，其类型为多边形。

class Area < ActiveRecord::Base 
  self.rgeo_factory_generator = RGeo::Geos.factory_generator
end

t.spatial  "geometry", :limit => {:srid=>4326, :type=>"point", :geographic=>true}

我可以在谷歌地图上创建并绘制事件和区域，也可以通过点击地图并保存到数据库来创建区域。

我希望能够执行以下两个查询：

1. @area.events - 显示一个区域内的所有事件 2. @event.areas - 显示某个单一事件所在的所有区域

我知道我的要求可能有些过多，但是任何帮助都将不胜感激。

非常感谢！

- nodrog

---

只是想澄清一下...你是在寻找一个SQL脚本还是Ruby答案？这个问题的Postgres SQL脚本相对来说比较简单（使用ST_contains），如果你提供这两个表的架构和它们之间的关系，我可以给你一个完整的脚本。 - Twelfth

嗨Twelfth...我想在Rails中实现它，但不确定该如何做，我想在Areas和Events之间建立一个连接表AreasEvents，例如，我想查看在给定区域内的事件，并查看给定事件所在的所有区域，但我无法看到如何使用标准的Rails关联来实现这一点。由于将有大量的事件，因此需要快速解决方案，因此我认为需要使用ST_contains。我尚未在表格之间实现任何关系。这有帮助吗？ - nodrog

Answer 1

这是一个快速的方法。这些只会返回 ActiveRecord 对象数组。

class Area
  def events
    Event.joins("INNER JOIN areas ON areas.id=#{id} AND st_contains(areas.geometry, events.geometry)").all
  end
end

class Event
  def areas
    Area.joins("INNER JOIN events ON events.id=#{id} AND st_contains(areas.geometry, events.geometry)").all
  end
end

你可能应该添加记忆化机制（缓存结果），以便每次调用方法时不必查询数据库。这应该很容易实现，我把它留给读者自己练习。

也许有可能通过一个真正的Rails关联代理（这样你就可以获得所有Rails关联的好处）来使其变得更为复杂和完整。不过我还没有研究过这方面的内容。无论如何，它都不会成为标准的Rails关联，因为你并没有存储ID。

Twelfth是对的：你应该为两个表创建空间索引。Activerecord-postgis-adapter 应该能够在你的迁移中轻易地实现这些。

change_table :events do |t|
  t.index :geometry, :spatial => true
end

change_table :areas do |t|
  t.index :geometry, :spatial => true
end

如果在安装postgis时遇到问题，最近我写了一些关于这方面的博客文章。请查看http://www.daniel-azuma.com/blog/archives/category/tech/georails。我也是rgeo和activerecord-postgis-adapter的作者，如果您在某些方面卡住了，我很乐意提供帮助。

- Daniel Azuma

---

谢谢Daniel，这真的很好，是的，我已经正确安装了PostGIS，我得到的错误是因为我认为我的列应该是地理位置...顺便说一句，你的宝石真的很棒，非常感谢你为它们所做的一切工作。 - nodrog

嗨，丹尼尔，这是我找到的最好的答案。你有没有考虑过如何使用关联代理使其工作？顺便说一下，博客系列非常棒，感谢你提供的所有rgeo好东西 :) - Nomas Prime

Answer 2

这个答案对于你来说可能是一个逐步进行的工作。我对ruby on rails不熟悉，但我应该能帮助你完成数据库部分。

你有两个表，Area存储多边形，Event存储事件作为单个点（如果事件也是一个区域且你正在尝试挑选重叠区域，则会更加复杂...如果事件是单个点，则可以使用此方法）。

Select *
from area a inner join event e on 1=1

这将创建一个包含每个区域与每个事件连接的列表……如果您有500个事件和20个区域，此查询将返回10,000行。现在，您想要过滤这些内容，只保留已加入区域内的事件。我们可以使用st_contains来实现这一点，因为st_contains(polygon,point)：

where st_contains(a.polygon,e.point) = 't'

如果您运行此代码，它应该会为区域内的所有事件给出a.,e.。现在只需要计算您想要计算的内容即可。

select a.id, count(1)
from area a inner join event e on 1=1
where st_contains(a.polygon,e.point) = 't'
group by 1

这将为您提供一个按ID列出所有区域及其中活动计数的列表。将a.id替换为e.id将给出一个事件ID列表，以及它们所在区域的数量。

不幸的是，我不知道如何在Ruby中表达这些查询，但您需要了解的数据库概念在这里...

为了考虑速度，您应该研究Postgres拥有的GIStree索引...索引化的多边形的性能呈指数级增长。

编辑：

PostGIS是随Postgres一起提供的一个contrib文件，但未在标准安装中存在...您需要找到此contrib文件。这些将在数据库中安装一系列GIS函数，包括ST_Contains。（功能位于数据库中，因此请确保在使用的数据库中安装功能）

PostGIS contrib文件安装的第二个内容是template_postGIS数据库，这是几何数据类型所需的（在安装之前，geom作为数据类型不存在）。

- Twelfth

---

你可以将这种类型的 SQL 语句包装在 Area.find_by_sql('select a.* from area a ...') 中，以便从 ActiveRecord 中获取对象。顺便说一下，很抱歉破坏了酷炫的 666 声望，但总得有人这么做。 - mu is too short

谢谢...我只需要随机地给10个项目投反对票就能把它拿回来了 ;) - Twelfth

谢谢，这很棒。我有两个问题：1. 我遇到了以下错误 https://gist.github.com/1685908 ，我认为我的所有数据都在 SRID 中，所以是因为我的数据库没有安装 postgis 函数吗？2. 你提到了 GIStree，我进行了快速搜索，但没有找到任何信息，你能给我一个链接吗？再次感谢，这真的很有帮助。 - nodrog

啊，抱歉...我以为你已经安装了contrib文件。PostgreSQL有一系列的“contrib”文件，它们是PostgreSQL数据库的功能性附加组件（通常作为一些函数实现）...DBlink提供了调用不同数据库的能力，但它并没有包含在标准安装中。PostGIS也是一个contrib文件...请阅读答案编辑，我会在那里提供这些信息。 - Twelfth

第二个问题的答案：http://www.postgresql.org/docs/8.1/static/gist.html 我认为它在技术上是一个btree_gist索引...我相信这与Oracle中的r-tree概念相同。 - Twelfth

感谢twefth，结果证明我确实已经添加了postgis函数，只是我混淆了几何和地理参数。现在一切都正常了！现在只需要解决active_record的问题，并且摆脱find_by_sql。再次感谢您的帮助。 - nodrog