开头先交代一下背景。之前有个项目要做附近的店铺推荐用户一打开App就要按距离排序、圈选区域里的门店、计算配送范围那时候项目技术栈是Elixir PhoenixORM用的Ecto。查了一圈资料关于PostGIS和Ecto怎么配合的文章要么太老要么只讲了安装要么直接甩一堆SQL让人自己翻译成Ecto Query。我自己从踩坑到跑通再到上线前做索引优化整个过程折腾了差不多一周所以这篇打算把这个组合从安装、建表、查询到性能优化的完整链路写清楚重点是那些文档里不会写、但实际开发八九不离十会遇到的问题。先说一个现象最近好几个群里都在问PostGIS安装失败怎么办而且问的人一多就说明不是个例。PostGIS本身不是一个能直接夹在Ecto里的库它工作在数据库那一层很多Elixir开发者不熟悉PostgreSQL的扩展安装机制所以第一道坎往往不是Ecto代码而是数据库这边根本没把PostGIS装好。下面先从安装失败这件事讲起这里面有一套固定的排查顺序按这个顺序走能省掉大量瞎试的时间。1. PostGIS安装失败的完整排查链路八成问题出在这几个环节1.1 按数据库版本选PostGIS版本这个对应关系先查清楚PostGIS不是独立运行的软件它是一组PostgreSQL扩展必须匹配PostgreSQL的版本。很多安装失败本质上是装了一个和当前PostgreSQL对不上的PostGIS包。比如Ubuntu上直接用APT安装sudo apt-get install postgis这样装到的很可能是针对系统默认PostgreSQL版本的PostGIS。假如你机器上有PostgreSQL 15而APT源默认对应的是PostgreSQL 14的PostGIS包那装完之后即使扩展创建成功也会出现一堆函数找不到的问题。所以第一步永远是确认两件事你的PostgreSQL具体是哪个大版本、有没有用系统包管理器之外的方式安装。我的建议是直接用官方源以PostgreSQL 15 PostGIS 3.4为例# 先装PostgreSQL官方源 sudo apt-get install -y postgresql-common sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh -y # 再装对应版本的PostGIS sudo apt-get install -y postgresql-15-postgis-3macOS上用Homebrew相对好处理但也要小心brew install postgis装到的扩展和你的PostgreSQL版本不一致。你在psql里执行SELECT version();看一眼再执行SELECT PostGIS_Version();看一眼能看出两边版本如果差得离谱基本就是装错源了。1.2 安装成功后仍然报函数不存在通常是扩展没启用PostGIS安装失败里最磨人的一种情况是包装好了连接数据库也正常但一执行空间查询就报function st_distance(geometry, geometry) does not exist。这是典型的扩展没有启用和操作系统层的安装无关。必须对当前数据库执行CREATE EXTENSION IF NOT EXISTS postgis;注意一个细节如果你有多个数据库这个命令需要在每个需要用到PostGIS的数据库里分别执行PostgreSQL的扩展是按数据库隔离的。有的开发者只在postgres库里执行了切到业务库就报函数不存在白白排查半天。还有一个小坑是权限。新版PostgreSQL里扩展安装需要数据库owner权限普通业务账号即使有建表权限也不一定有CREATE EXTENSION权限。如果是在受管数据库服务里比如云厂商的RDS情况又不一样通常要在控制台把PostGIS扩展启用或者在admin账号下执行。1.3 Windows和macOS上的特殊坑路径、版本、架构Windows上最常见的失败场景是安装PostGIS时选了配套的PostgreSQL版本但psql命令行连的是另一个版本的数据库。PostGIS安装包会在开始菜单里放一个PostGIS Bundle快捷方式它连的是自带的那套PostgreSQL。如果你日常开发用的是另外一个PostgreSQL服务哪怕端口都是5432连的实例不同扩展目录也不同报错信息就容易很诡异。所以Windows上先确认SHOW data_directory; SELECT extversion FROM pg_extension WHERE extname postgis;macOS上则经常遇到postgis.control文件找不到的问题。Homebrew安装的PostGIS默认扩展路径是/usr/local/share/postgresql15/extension或/opt/homebrew/share/postgresql15/extension如果shared_preload_libraries或者dynamic_library_path配置有问题即使CREATE EXTENSION不报错查询时加载空间函数也可能失败。可以通过下面的SQL检查扩展文件路径SELECT name, default_version, installed_version FROM pg_available_extensions WHERE name postgis;如果没有找到记录说明PostGIS虽然装了但PostgreSQL根本没扫描到它的安装文件这种只能去检查包的安装路径和dynamic_library_path。2. 为什么是PostGIS Ecto和GeoJSON手算、外部服务放在一起比2.1 用GeoJSON手算距离的教训边界情况能让代码乱成一锅粥在引入PostGIS之前常见的方案是应用层自己算。大概流程是把所有POI的经纬度加载到内存或者每次请求都从数据库查出经纬度列表然后用Haversine公式算距离。这套方案在小数据量、单机部署、不要求实时性的场景下是能跑的但一旦数据量超过几万条或者接口需要同时做范围过滤和距离排序问题就来了。一是网络开销。每次请求都把全量POI拉出来在应用层算数据库的查询时间也许只有几十毫秒但传输几万行JSON的时间可能已经让接口超时了。二是边界情况。Haversine公式处理短距离还算凑合但一旦要判断“点是否在多边形内”“两条路径是否相交”手写公式的复杂度会迅速爆炸。我在之前的项目里写过一个判断“点是否在圆形区域内”的工具函数看起来简单加上地球曲率修正、边界值判断之后代码接近一百行后期维护成本很高。三是聚合和索引的优势完全用不上。PostGIS在数据库层面就能完成“先圈定一个范围再在范围内计算和排序”而且有GiST空间索引支撑。应用层手算做不到这一点或者说需要自己再造一个空间索引的轮子这个轮子大概率不如数据库的成熟。下面对比三套方案的差异方案数据量门槛边界查询支持索引支持维护成本应用层Haversine手算万级以内勉强弱多边形判断要手写无高公式和边界自己维护Elasticsearch Geo相关能力适合海量数据较强自带高额外集群和业务库有同步延迟PostGIS Ecto千万级无压力强SQL层面直接支持GiST/SP-GiST低扩展就在业务库里2.2 PostGIS在数据量上来之后依然能打从索引结构说起PostGIS的索引依赖PostgreSQL的GiST。这个索引结构和常见的B-Tree不一样B-Tree适合精确匹配和范围查询而GiST更擅长处理“多维空间数据”的搜索。拿“附近的店”这个查询来说传统SQL写法是WHERE lat BETWEEN ? AND ? AND lng BETWEEN ? AND ?虽然也能跑但本质上是在两个维度上分别圈范围MySQL和PostgreSQL对这种二维查询的优化非常有限。而PostGIS的GiST索引能直接按空间位置把数据组织成树状结构查询时顺着树往下走可以快速排除掉大量“完全不可能在目标区域内”的数据。我在一个数据量接近三百万行的表上测试过应用层Haversine全量计算的耗时大概在700毫秒到1秒之间而PostGIS加上GiST索引之后同范围的查询加排序可以压到30毫秒以内。差距在数据量越大的时候越明显。2.3 这套组合的边界什么时候不该硬上PostGIS如果业务只是偶尔查一次坐标数据量几千行用PostGIS反而是过度设计。PostGIS的几何类型、空间索引、函数库都增加了系统复杂度而且geometry类型在应用层处理起来没有普通数值字段那么直观。以下场景我建议先用简单方案展示型的小工具、坐标点不超过一万的静态数据集、或者查询模式非常固定且没有空间运算需求。另外要留意Ecto对PostGIS的支持虽然已经成熟但它依赖Geo这个Elixir库做类型映射。如果团队对Elixir不熟对Geo的WKT、GeoJSON编码又陌生那调试成本会增加。这个组合适合的是已经确定要用Elixir/Phoenix、并且数据量确实需要数据库层面地理计算的项目而不是为了追新硬套。3. Ecto接上PostGIS的地基依赖、Repo配置与含空间字段的migration3.1 依赖与Repo配置三行代码把Geo.Postgis接进来Ecto本身不知道PostGIS的存在需要通过geo_postgis这个包让Ecto认识空间类型。先在mix.exs里加依赖defp deps do [ {:ecto_sql, ~ 3.0}, {:postgrex, ~ 0.17}, {:geo, ~ 3.4}, {:geo_postgis, ~ 3.4} ] end然后要在config里声明PostGIS的编码器config :geo, postgis: Geo.PostGIS这一步很容易漏。去年我接手一个同事的项目他加了geo_postgis依赖但没在config里声明结果migration跑得很快一查数据就报“无法编码Geo.Point”。因为Ecto在把Elixir结构体转成PostgreSQL二进制格式时不知道应该用PostGIS的编码器默认走的是Postgrex的extension路径两边对不上就会抛异常。Repo模块本身不用大改默认的use Ecto.Repo就行。真正需要改的是Postgrex初始化时加载扩展如果你用的是默认的postgrex协议实际上geo_postgis已经帮你在编译时注册了不太需要手写after_connect。但如果你的Repo还额外配置了crypto之类的东西要留意是不是走了自定义的Postgrex.Types模块。3.2 第一个migration从CREATE EXTENSION到空间字段migration的第一步是启用扩展这是数据库层面的事Ecto的migration机制能执行原始SQL所以直接写defmodule MyApp.Repo.Migrations.AddPostgisExtension do use Ecto.Migration def up do execute CREATE EXTENSION IF NOT EXISTS postgis end def down do execute DROP EXTENSION IF EXISTS postgis end end接下来建一张带空间字段的表。假设这是一张存储地标或POI的表defmodule MyApp.Repo.Migrations.CreatePlaces do use Ecto.Migration def change do create table(:places) do add :name, :string add :location, :geometry timestamps() end end end不过这里我建议直接指定几何类型和SRID不要用默认的。PostGIS的geometry类型如果不指定SRID默认是0表示未知坐标系这样后面做距离计算时会得到错误结果。比如统一用WGS84也就是GPS使用的坐标系add :location, :geometry, null: false上面的写法没指定SRID最好还是在PostGIS层面处理。一个比较稳的写法是migration里用execute建表或者在add之后用execute改列类型。实践中更常见的做法是直接在Ecto schema里定义了一个Geo.Point类型的字段而表字段仍然是无SRID约束的geometry但查询时通过ST_SetSRID强制指定。我个人的习惯是建表时就把SRID固定住省得后面每个查询都要用ST_SetSRID包裹。用execute修改也是可行的execute SELECT AddGeometryColumn(places, location, 4326, POINT, 2)然后用Ecto schema这样映射defmodule MyApp.Place do use Ecto.Schema import Geo.Ecto schema places do field :name, :string field :location, Geo.PostGIS.Geometry timestamps() end end这里字段类型用了Geo.PostGIS.Geometry而不是Geo.Point是因为AddGeometryColumn返回的geometry类型没有子类型信息Ecto难以推断是点还是线还是面。如果只是点可以更收敛地声明field :location, Geo.PostGIS.Point3.3 Ecto类型与PostGIS类型对照常见的不匹配问题下面这张表整理了我实际用到的对照关系拿来当参考很好用PostGIS数据库类型Ecto字段类型对应Geo结构体geometry(Point, 4326)Geo.PostGIS.Point%Geo.Point{}geometry(LineString, 4326)Geo.PostGIS.LineString%Geo.Point{}(和Point共用)geometry(Polygon, 4326)Geo.PostGIS.Polygon%Geo.Polygon{}geography(Point, 4326)同样是Geo.PostGIS.Point同左但计算走球面最容易踩的坑是“类型不匹配错误”。比如数据库列类型是geometry(Polygon, 4326)Ecto schema里却声明成Geo.PostGIS.Point插入数据时不会有明显报错但查询回来的时候Ecto不知道要把二进制转成什么结构体抛ArgumentError: cannot load。所以schema的字段类型必须和数据库列的真实类型严格对应别想当然。另一个常见问题是SRID不一致导致距离计算错误。Ecto插入一个%Geo.Point{coordinates: {lat, lng}}如果列是4326就没事但如果列是默认为0或者其他SRIDPostGIS计算时会按不同坐标系理解坐标结果会荒谬。我遇到过查出来距离两万多公里最后定位到是插入数据的SRID和查询条件的SRID不一致。解决方案是统一在应用层写入前用Geo.PostGIS转换或者统一在SQL里用ST_Transform。4. 把空间查询塞进Ecto Query四类场景的SQL到Elixir翻译实战4.1 “附近的人”场景距离计算、排序与阈值过滤这是位置感知应用最高频的场景。SQL层面的标准写法是用ST_DWithin做距离过滤、ST_Distance做排序其中geography和geometry需要区分下面会说。先说Ecto里怎么表达。假设用户传进来一个坐标点{lat, lng}要查附近5公里内的POI然后按距离升序排列def nearby_places(%Geo.Point{} origin, radius_m) do query from p in Place, where: fragment(ST_DWithin(geography(?), geography(?), ?), p.location, ^origin, ^radius_m), select: %{ place: p, distance_m: fragment(ST_Distance(geography(?), geography(?)), p.location, ^origin) }, order_by: fragment(ST_Distance(geography(?), geography(?)), p.location, ^origin) Repo.all(query) end这里用了fragment因为Ecto没有内置的空间函数封装。注意两个geography()包裹它把geometry转成geography类型这样ST_DWithin返回的结果是以米为单位的真球面距离而不是度。如果省略转换默认按平面坐标算在高纬度地区误差大到离谱。order_by里重复写了两遍ST_Distance这不是最优写法但优点是无需子查询结构清晰。数据量大的时候可以考虑先做ST_DWithin过滤再在外部排序让数据库在排序前先收窄结果集。4.2 “在不在这个区域里”场景多边形圈选与包含判断这个场景常用于“我画的这个范围里有哪些门店”“某个点落在哪个行政区里”。PostGIS的ST_Contains负责判断Ecto里这样写def places_in_polygon(polygon_geom) do from p in Place, where: fragment(ST_Contains(?, ?), ^polygon_geom, p.location) end这里polygon_geom是一个%Geo.Polygon{}结构体。Ecto在传参时会自动把它编码成PostGIS的Polygon二进制。要注意坐标顺序Geo和PostGIS遵循的纬度在前经度在后的约定并不一致常规GPS坐标在插入和查询时必须保持在同一个顺序上否则画出来的范围会跑到另一个半球去。另一个坑是ST_Contains和ST_Intersects的边界语义。位于多边形边界上的点ST_Contains返回true而ST_Intersects也返回true但如果你想判断“严格落在内部不包含边界”得用ST_Contains配合ST_Touches的取反。业务上如果涉及“行政区域的边界线到底属于谁”的问题这个语义差别会被抬到台面上。4.3 空间索引能否命中的关键别在fragment里包函数空间索引需要一个前提条件查询语句里的空间字段不能被函数包裹住否则GiST索引失效数据库会退回全表扫描。最典型的反例是这样写from p in Place, where: fragment(ST_Distance(geography(p.location), geography(?)) ?, ^origin, ^radius_m)看起来逻辑和ST_DWithin差不多但在执行计划里ST_Distance是一个计算型谓词数据库没法直接用它走GiST索引于是定位一条记录就得算一次距离数据量大了之后性能非常难看。正确姿势是改用ST_DWithin它内部对GiST有专门支持数据库可以把“距离小于X”这个条件转换成边界盒比较先走索引缩小候选集再精确计算。这个差别在数据量超过十万行之后会特别明显。我的建议是能用ST_DWithin就绝不用ST_Distance做where过滤。这里整理了一张SQL写法对照表方便直接照着用需求推荐写法不推荐写法原因附近5公里内的点ST_DWithinST_Distance 5000前者可走GiST索引按距离排序ST_Distance无替代排序必须算距离点在多边形内ST_ContainsST_Intersects边界语义更精确两个点的直线距离ST_Distance自己写公式PostGIS内建优化求范围框选ST_MakeEnvelope 四点手写范围前者可走索引5. 上线前必须处理的性能与索引细节5.1 GiST索引创建方式与验证空间表建好之后第一件事是给空间字段创建GiST索引。PostgreSQL默认不会自动为geometry字段建索引没有索引的话哪怕查询写法再标准也是全表扫描数据量一大就崩。在Ecto migration里create index(:places, [location USING GIST], name: :places_location_gist_index)执行完用EXPLAIN ANALYZE验证一下查询计划。以下SQL可以直接在psql里跑EXPLAIN ANALYZE SELECT * FROM places WHERE ST_DWithin(geography(location), geography(ST_SetSRID(ST_MakePoint(116.0, 39.9), 4326)), 5000);看到Index Scan而不是Seq Scan才说明索引真正起到了作用。实际项目里还有一个需求如果经常用name字段加上空间条件做复合查询可以建一个GIST (location)和普通B-Tree的组合但PostgreSQL不支持直接把两种索引类型混在一起常见做法是分别建索引让查询规划器自行选择。大多数情况下单独的空间索引就够了。5.2 geometry还是geography这个选择影响计算精度和功能范围PostGIS里有两类类型geometry和geography。geometry把地球当成平面计算快但精度差geography把地球当成球面计算准但函数支持少、性能略低。如果项目只做小范围内的近邻查询平面geometry的误差在可接受范围内而且能享受更多函数支持比如缓冲区、布尔运算、投影变换很多算法只支持geometry。如果项目涉及跨城市、跨国家的距离查询或者业务要求距离结果必须符合真实地面距离那就要用geography或者在计算时临时用::geography转换。我之前做的配送范围查询就是用geography因为用户在乎的是“实际骑手要跑多少公里”不是纸上两点间的直线度。一个折中方案是表里存geometry(Point, 4326)需要精确距离时临时geography(column)需要做复杂几何运算时不转换。这个方案牺牲了一点存储和计算效率但灵活度最高。5.3 实测经验几组数据下的查询变化趋势以三百万行POI数据为例我分别测过几种写法结论可以当作参考查询方式数据量达到300万行时的行为无索引全表ST_Distance过滤查询范围全表扫描耗时800ms以上有GiST索引 ST_DWithin40ms以内范围越小越快有GiST索引但查询条件包了函数索引失效性能回落到全表扫描先圈矩形再精确计算表现不错但不比直接ST_DWithin好结论很清楚大部分场景下ST_DWithin GiST索引就是性价比最高的一套组合。另外要注意的是空间索引并不是建了就完事。如果数据表经常批量更新或删除索引膨胀会让查询越来越慢建议定期REINDEX TABLE places或者在低峰期做VACUUM ANALYZE。可以配合定时任务在凌晨执行。5.4 Ecto N1查询的隐藏坑用Ecto关联查询时很多人会在循环里逐条查距离比如在Enum.each里对每个用户执行一次距离查询这在数据量小的时候不显眼数据量大了就是N1灾难。正确做法是一次性取出所有关联记录再统一在内存里处理或者干脆用子查询把距离计算挪到数据库里。类似这种Repo.all( from p in Place, join: u in User, on: p.user_id u.id, select: %{place: p, distance: fragment(ST_Distance(geography(?), geography(?)), p.location, ^origin)} )这样一条SQL带出所有记录避免在循环里反复访问数据库。结尾个人经验与一个小技巧在这个项目里真正让我觉得“这个组合选对了”的时刻是后来产品经理临时要求加一个“按行政区统计门店数量”的报表。当时数据在同一个库里我用一个ST_Contains关联查询就写完了前后不超过半小时。如果当初用了应用层手算坐标的方案或者引入独立的地理服务这种临时需求处理起来会非常被动。最后分享两个小技巧。第一个是Ecto里用fragment写空间查询时参数一律用^插值别把外部变量拼进字符串否则一是SQL注入风险二是Postgrex无法正确编码Geo结构体。第二个是在本地开发时可以把PostGIS相关查询放进一个独立的GeospatialQuery模块把fragment字符串集中管理这样后面升级PostGIS或换数据库时改动范围会小很多。另外想提醒的是PostGIS的版本升级要谨慎。上次我把一个项目的PostGIS从3.2升到3.4结果某个ST_Distance的精度行为发生了变化导致一批距离阈值过滤的结果和以前不一致。升级前务必把线上的空间查询用例都回归一遍别信“小版本升级无感”这种话。这个教训是拿一个下午的线上问题换来的。