时间戳

首页 > 解决方案 > 创建用于搜索数据库中数百万个地址的 Web 应用程序的最佳选择是什么？

amazon-web-services - 创建用于搜索数据库中数百万个地址的 Web 应用程序的最佳选择是什么？

问题描述

我正在开发一个 Node.js Web 应用程序，它允许用户搜索存储在后端 PostgreSQL 数据库中的世界上每一所学校（约 700 万）。

用户体验

用户将在谷歌地图上选择一个位置（带有可选字段，如学校类型、要显示的 N 所学校、以公里为单位的 M 半径），地图将显示 M 公里内排名前 N 的学校。用户选择的位置可能是也可能不是有效地址，因此 Google 地图会将用户选择的位置转换为纬度和经度，我的 Web 应用程序将调用function findSchoolsByLocation(latitude, longitude, filterParams...)并返回来自 PostgreSQL 的数据的 JSON 对象。

数据

我得到的原始数据包括关于那所学校的地址和元数据，如下所示：

| Primary Key | Address -------------------------------- | School Name ------- |
| ??????????? | 3210 Wimberly Rd, Amarillo TX 79109-3433 | University of Texas |
| ??????????? | 5198 Jex St, Arlington, TX, 78019-4532 | Texas Elementary School |

在验证地址和元数据之后，1) 对存储在 PostgreSQL 中的所有 700 万个地址进行地理编码并使用纬度和经度作为主键更好，还是 2) 使用地址作为主键更好，并且findSchoolsByLocation能够以某种方式仅通过字符串地址找到最近的 N 个地址，而无需经纬度？

如果是 1)，我正在考虑在本地服务器中使用 PostGIS（代码更改最少），在 AWS RDS Postgre 中使用 PostGIS 以更好地扩展（我不熟悉 AWS），或者 Google Geocode API（更准确，但是 Web 服务）。我需要对大量地址进行地理编码，但我只需要执行一次，随后的更改我只会更新更新地址的地理编码（显然不会那么多）。我已经了解了使用 Web 服务与直接写入数据库的优缺点。对于我的用例，哪个是更好的选择？

在这里寻找段落响应我想写一份报告来解释我的决策过程、替代选项以及处理实施此 Web 应用程序、地理编码和数据库设计的风险和错误：

  1. 我应该怎么办？
  2. 如果我在这个决定中犯了错误，我会怎么做？这将如何计算风险承担？
  3. 在决定哪个是更好的解决方案时，我将如何处理与队友的冲突？

标签： amazon-web-servicesweb-servicesdatabase-designgispostgis

解决方案

几点注意事项：

没有选择摆脱地理编码（字符串查找是不可能的）

你确定谷歌地理编码有用吗？它只是一个地理编码工具，正如有人提到的那样，他们不允许保留地理编码数据。您可能需要使用其他一些服务（mapquest 似乎有存储结果的计划）

我认为您的实际 2 个选项是：

  • 您可以将所有 7M 点上传到某个云服务中，他们为您进行地理编码，然后通过 API 运行空间查询（检查 cartodb、mapbox）。谷歌也有融合表，它实际上是免费的，但每个表的数据大小有限制，数据将是公开的（但工具本身很棒）
  • 或者您自己对数据进行地理编码并在您自己的数据库中运行空间查询。看起来地理编码将是这里的主要挑战。确保 google API 适合您。关于 AWS 或本地 - 如果您在一些小公司或预算允许，请使用 AWS（或任何其他云）。如果您已经拥有基础设施和资源 - 与本地合作可能更有意义。

回答您的 3 个问题 - 我认为您的主要风险和担忧是价格。只需对您可能使用的所有服务进行成本分析，我认为之后您就会清楚。首先，我会检查 cartodb（或类似的东西）是否有适合您的解决方案。如果不是，那么研究哪个地理编码提供商适合您（关键是能够存储您获得的数据）。然后从 AWS 获取估算值。我认为运行本地数据库可能会让人头疼，但可能会具有成本效益。

关于技术部分，我认为您应该使用空间类型/索引，无需使用公式计算距离。下面是一个关于如何创建、查询和检索空间数据的简单示例（以防您不熟悉或不清楚）

--- set up postgis environment with docker if needed
--- (from here: https://alexurquhart.com/post/set-up-postgis-with-docker):

-- docker volume create pg_data
-- docker run --name=postgis -d -e POSTGRES_USER=alex -e POSTGRES_PASS=password -e POSTGRES_DBNAME=gis -e ALLOW_IP_RANGE=0.0.0.0/0 -p 5432:5432 -v pg_data:/var/lib/postgresql --restart=always kartoza/postgis:9.6-2.4


-- drop table schools

create table schools (
 country varchar(20),
 state varchar(20),
 school varchar(60),
 lat float,
 long float, 
 loc GEOGRAPHY
);

---- NYC schools
insert into schools values ('USA', 'NY', 'New York City School District 1', 40.7212744,-73.986311, null);
insert into schools values ('USA', 'NY', 'KIPP NYC College Prep', 40.8162614,-73.9260793, null);
insert into schools values ('USA', 'NY', 'The Young Womens Leadership School of Astoria', 40.7712631,-73.9241695, null);
insert into schools values ('USA', 'NY', 'Brooklyn East Collegiate Charter School', 40.6784249,-73.9658189, null);
insert into schools values ('USA', 'NY', 'N Y City Board of Education', 40.6933457,-73.9215088, null);
insert into schools values ('USA', 'NY', 'New York City School District 28', 40.7027487,-73.8079333, null);
insert into schools values ('USA', 'NY', 'School of Math, Science, and Healthy', 40.6394884,-74.0202785, null);


UPDATE schools SET loc = ST_POINT(long,lat);
CREATE INDEX school_loc ON schools USING GIST (loc);



--- get schools within 10km around (-73.9091706, 40.71163)

select S.*
,ST_Distance(loc, ST_POINT(-73.9091706, 40.71163))  as dist
from schools S
where ST_Distance(loc, ST_POINT(-73.9091706, 40.71163)) < 10000


---- Converting result to JSON.
---- It's a good idea to get it as GeoJSON since it's supported almost by any spatial tool. You can use http://geojson.io to visualize it

with result as ( 
select S.*, ST_Distance(loc, ST_POINT(-73.9091706, 40.71163)) as dist from schools S
)

,features as ( 
select json_build_object( 
  'type', 'Feature',
  'geometry', st_AsGeoJSON(loc)::json,
  'properties', (school, dist)
  
) AS feature
from result
where dist < 10000 
order by dist
)
------ main 
-- select feature from features
select json_build_object( 
'type', 'FeatureCollection',
'features', json_agg(feature)
)
from features

推荐阅读