程序员经验分享-hwhale

搜索距离邮政编码最近的 5 个地点 - 我应该走哪条路？

2024-01-12

我想要的是：

  1. 用户传入邮政编码或城市名称
  2. 我在数据库中搜索 5 个最近的地点
  3. 显示距离用户最近的 5 个位置

到目前为止我所拥有的：

假设有一个包含以下内容的地点表：

(about 16000 rows)

CREATE TABLE `locations` (
 `locationID` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(150) NOT NULL,
 `firstname` varchar(100) DEFAULT NULL,
 `lastname` varchar(100) DEFAULT NULL,
 `street` varchar(100) NOT NULL,
 `city` varchar(100) NOT NULL,
 `state` varchar(100) NOT NULL,
 `zipcode` varchar(10) NOT NULL,
 `phone` varchar(20) NOT NULL,
 `web` varchar(255) DEFAULT NULL,
 `machine` enum('Unbekannt','Foo','Bar') DEFAULT 'Unbekannt',
 `surface` enum('Unbekannt','Foo','Bar','') DEFAULT 'Unbekannt',
 PRIMARY KEY (`locationID`)
) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=utf8
  1. ID
  2. name
  3. zip code
  4. city

现在我有了第二张表，其中包含世界上所有的城镇：

(about 340万 rows)

CREATE TABLE `geoData` (
 `geoID` int(11) NOT NULL AUTO_INCREMENT,
 `countryCode` char(2) NOT NULL,
 `zipCode` varchar(20) NOT NULL,
 `name` varchar(180) NOT NULL,
 `state` varchar(100) NOT NULL,
 `stateCode` varchar(20) NOT NULL,
 `county` varchar(100) NOT NULL,
 `countyCode` varchar(20) NOT NULL,
 `community` varchar(100) NOT NULL,
 `communityCode` varchar(20) NOT NULL,
 `lat` mediumint(6) NOT NULL,
 `lon` mediumint(6) NOT NULL,
 PRIMARY KEY (`lon`,`lat`,`geoID`) USING BTREE,
 KEY `geoID` (`geoID`)
) ENGINE=InnoDB AUTO_INCREMENT=16482 DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (lat)
(PARTITION p0 VALUES LESS THAN (-880000) ENGINE = InnoDB,
PARTITION p1 VALUES LESS THAN (-860000) ENGINE = InnoDB,
PARTITION p2 VALUES LESS THAN (-840000) ENGINE = InnoDB,
PARTITION p3 VALUES LESS THAN (-820000) ENGINE = InnoDB,
PARTITION p4 VALUES LESS THAN (-800000) ENGINE = InnoDB,
PARTITION p5 VALUES LESS THAN (-780000) ENGINE = InnoDB,
PARTITION p6 VALUES LESS THAN (-760000) ENGINE = InnoDB,
PARTITION p7 VALUES LESS THAN (-740000) ENGINE = InnoDB,
PARTITION p8 VALUES LESS THAN (-720000) ENGINE = InnoDB,
PARTITION p9 VALUES LESS THAN (-700000) ENGINE = InnoDB,
PARTITION p10 VALUES LESS THAN (-680000) ENGINE = InnoDB,
PARTITION p11 VALUES LESS THAN (-660000) ENGINE = InnoDB,
PARTITION p12 VALUES LESS THAN (-640000) ENGINE = InnoDB,
PARTITION p13 VALUES LESS THAN (-620000) ENGINE = InnoDB,
PARTITION p14 VALUES LESS THAN (-600000) ENGINE = InnoDB,
PARTITION p15 VALUES LESS THAN (-580000) ENGINE = InnoDB,
PARTITION p16 VALUES LESS THAN (-560000) ENGINE = InnoDB,
PARTITION p17 VALUES LESS THAN (-540000) ENGINE = InnoDB,
PARTITION p18 VALUES LESS THAN (-520000) ENGINE = InnoDB,
PARTITION p19 VALUES LESS THAN (-500000) ENGINE = InnoDB,
PARTITION p20 VALUES LESS THAN (-480000) ENGINE = InnoDB,
PARTITION p21 VALUES LESS THAN (-460000) ENGINE = InnoDB,
PARTITION p22 VALUES LESS THAN (-440000) ENGINE = InnoDB,
PARTITION p23 VALUES LESS THAN (-420000) ENGINE = InnoDB,
PARTITION p24 VALUES LESS THAN (-400000) ENGINE = InnoDB,
PARTITION p25 VALUES LESS THAN (-380000) ENGINE = InnoDB,
PARTITION p26 VALUES LESS THAN (-360000) ENGINE = InnoDB,
PARTITION p27 VALUES LESS THAN (-340000) ENGINE = InnoDB,
PARTITION p28 VALUES LESS THAN (-320000) ENGINE = InnoDB,
PARTITION p29 VALUES LESS THAN (-300000) ENGINE = InnoDB,
PARTITION p30 VALUES LESS THAN (-280000) ENGINE = InnoDB,
PARTITION p31 VALUES LESS THAN (-260000) ENGINE = InnoDB,
PARTITION p32 VALUES LESS THAN (-240000) ENGINE = InnoDB,
PARTITION p33 VALUES LESS THAN (-220000) ENGINE = InnoDB,
PARTITION p34 VALUES LESS THAN (-200000) ENGINE = InnoDB,
PARTITION p35 VALUES LESS THAN (-180000) ENGINE = InnoDB,
PARTITION p36 VALUES LESS THAN (-160000) ENGINE = InnoDB,
PARTITION p37 VALUES LESS THAN (-140000) ENGINE = InnoDB,
PARTITION p38 VALUES LESS THAN (-120000) ENGINE = InnoDB,
PARTITION p39 VALUES LESS THAN (-100000) ENGINE = InnoDB,
PARTITION p40 VALUES LESS THAN (-80000) ENGINE = InnoDB,
PARTITION p41 VALUES LESS THAN (-60000) ENGINE = InnoDB,
PARTITION p42 VALUES LESS THAN (-40000) ENGINE = InnoDB,
PARTITION p43 VALUES LESS THAN (-20000) ENGINE = InnoDB,
PARTITION p44 VALUES LESS THAN (0) ENGINE = InnoDB,
PARTITION p45 VALUES LESS THAN (20000) ENGINE = InnoDB,
PARTITION p46 VALUES LESS THAN (40000) ENGINE = InnoDB,
PARTITION p47 VALUES LESS THAN (60000) ENGINE = InnoDB,
PARTITION p48 VALUES LESS THAN (80000) ENGINE = InnoDB,
PARTITION p49 VALUES LESS THAN (100000) ENGINE = InnoDB,
PARTITION p50 VALUES LESS THAN (120000) ENGINE = InnoDB,
PARTITION p51 VALUES LESS THAN (140000) ENGINE = InnoDB,
PARTITION p52 VALUES LESS THAN (160000) ENGINE = InnoDB,
PARTITION p53 VALUES LESS THAN (180000) ENGINE = InnoDB,
PARTITION p54 VALUES LESS THAN (200000) ENGINE = InnoDB,
PARTITION p55 VALUES LESS THAN (220000) ENGINE = InnoDB,
PARTITION p56 VALUES LESS THAN (240000) ENGINE = InnoDB,
PARTITION p57 VALUES LESS THAN (260000) ENGINE = InnoDB,
PARTITION p58 VALUES LESS THAN (280000) ENGINE = InnoDB,
PARTITION p59 VALUES LESS THAN (300000) ENGINE = InnoDB,
PARTITION p60 VALUES LESS THAN (320000) ENGINE = InnoDB,
PARTITION p61 VALUES LESS THAN (340000) ENGINE = InnoDB,
PARTITION p62 VALUES LESS THAN (360000) ENGINE = InnoDB,
PARTITION p63 VALUES LESS THAN (380000) ENGINE = InnoDB,
PARTITION p64 VALUES LESS THAN (400000) ENGINE = InnoDB,
PARTITION p65 VALUES LESS THAN (420000) ENGINE = InnoDB,
PARTITION p66 VALUES LESS THAN (440000) ENGINE = InnoDB,
PARTITION p67 VALUES LESS THAN (460000) ENGINE = InnoDB,
PARTITION p68 VALUES LESS THAN (480000) ENGINE = InnoDB,
PARTITION p69 VALUES LESS THAN (500000) ENGINE = InnoDB,
PARTITION p70 VALUES LESS THAN (520000) ENGINE = InnoDB,
PARTITION p71 VALUES LESS THAN (540000) ENGINE = InnoDB,
PARTITION p72 VALUES LESS THAN (560000) ENGINE = InnoDB,
PARTITION p73 VALUES LESS THAN (580000) ENGINE = InnoDB,
PARTITION p74 VALUES LESS THAN (600000) ENGINE = InnoDB,
PARTITION p75 VALUES LESS THAN (620000) ENGINE = InnoDB,
PARTITION p76 VALUES LESS THAN (640000) ENGINE = InnoDB,
PARTITION p77 VALUES LESS THAN (660000) ENGINE = InnoDB,
PARTITION p78 VALUES LESS THAN (680000) ENGINE = InnoDB,
PARTITION p79 VALUES LESS THAN (700000) ENGINE = InnoDB,
PARTITION p80 VALUES LESS THAN (720000) ENGINE = InnoDB,
PARTITION p81 VALUES LESS THAN (740000) ENGINE = InnoDB,
PARTITION p82 VALUES LESS THAN (760000) ENGINE = InnoDB,
PARTITION p83 VALUES LESS THAN (780000) ENGINE = InnoDB,
PARTITION p84 VALUES LESS THAN (800000) ENGINE = InnoDB,
PARTITION p85 VALUES LESS THAN (820000) ENGINE = InnoDB,
PARTITION p86 VALUES LESS THAN (840000) ENGINE = InnoDB,
PARTITION p87 VALUES LESS THAN (860000) ENGINE = InnoDB,
PARTITION p88 VALUES LESS THAN (880000) ENGINE = InnoDB,
PARTITION p89 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
  1. ID
  2. city
  3. zip code
  4. latitude
  5. 经度

基于此article http://mysql.rjweb.org/doc.php/latlng#representation_choices和其他一些关于此事的阅读我有一个存储过程，它给了我n靠近某个点（纬度/经度）的最近城镇的位置/邮政编码。

我的存储过程：

    BEGIN
    DECLARE _deg2rad DOUBLE DEFAULT PI()/1800000;

    SET @my_lat := _my_lat,
        @my_lon := _my_lon,
        @deg2dist := 0.0111325,  
        @start_deg := _start_dist / @deg2dist,  
        @max_deg := _max_dist / @deg2dist,
        @cutoff := @max_deg / SQRT(2),  
        @dlat := @start_deg,  
        @lon2lat := COS(_deg2rad * @my_lat),
        @iterations := 0;        

    SET @sql = CONCAT(
        "SELECT COUNT(*) INTO @near_ct
            FROM geoData
            WHERE lat    BETWEEN @my_lat - @dlat
                             AND @my_lat + @dlat   
              AND lon    BETWEEN @my_lon - @dlon
                             AND @my_lon + @dlon");
    PREPARE _sql FROM @sql;
    MainLoop: LOOP
        SET @iterations := @iterations + 1;
        SET @dlon := ABS(@dlat / @lon2lat);  
        SET @dlon := IF(ABS(@my_lat) + @dlat >= 900000, 3600001, @dlon);  
        EXECUTE _sql;
        IF ( @near_ct >= _limit OR         
             @dlat >= @cutoff ) THEN       
            LEAVE MainLoop;
        END IF;
        SET @dlat := LEAST(2 * @dlat, @cutoff);   
    END LOOP MainLoop;
    DEALLOCATE PREPARE _sql;

    SET @dlat := IF( @dlat >= @max_deg OR @dlon >= 1800000,
                @max_deg,
                GCDist(ABS(@my_lat), @my_lon,
                       ABS(@my_lat) - @dlat, @my_lon - @dlon) );
    SET @dlon := IFNULL(ASIN(SIN(_deg2rad * @dlat) /
                             COS(_deg2rad * @my_lat))
                            / _deg2rad 
                        , 3600001);    


    IF (ABS(@my_lon) + @dlon < 1800000 OR    
        ABS(@my_lat) + @dlat <  900000) THEN 
        SET @sql = CONCAT(
            "SELECT *,
                    @deg2dist * GCDist(@my_lat, @my_lon, lat, lon) AS dist
                FROM geoData
                WHERE lat BETWEEN @my_lat - @dlat
                              AND @my_lat + @dlat   
                  AND lon BETWEEN @my_lon - @dlon
                              AND @my_lon + @dlon   
                HAVING dist <= ", _max_dist, "
                ORDER BY dist
                LIMIT ", _limit
                        );
    ELSE
        SET @west_lon := IF(@my_lon < 0, @my_lon, @my_lon - 3600000);
        SET @east_lon := @west_lon + 3600000;
        SET @sql = CONCAT(
            "( SELECT *,
                    @deg2dist * GCDist(@my_lat, @west_lon, lat, lon) AS dist
                FROM geoData
                WHERE lat BETWEEN @my_lat - @dlat
                              AND @my_lat + @dlat 
                  AND lon BETWEEN @west_lon - @dlon
                              AND @west_lon + @dlon   
                HAVING dist <= ", _max_dist, " )
            UNION ALL
            ( SELECT *,
                    @deg2dist * GCDist(@my_lat, @east_lon, lat, lon) AS dist
                FROM geoData
                WHERE lat BETWEEN @my_lat - @dlat
                              AND @my_lat + @dlat   
                  AND lon BETWEEN @east_lon - @dlon
                              AND @east_lon + @dlon   
                HAVING dist <= ", _max_dist, " )
            ORDER BY dist
            LIMIT ", _limit
                        );
    END IF;

    PREPARE _sql FROM @sql;
    EXECUTE _sql;
    DEALLOCATE PREPARE _sql;
END

我的问题：

我想传递邮政编码或城镇名称，然后从那里开始搜索。所以我的想法是我请求这些信息并查找世界上所有城镇/邮政编码的表格。之后，如果只找到一个结果，我就会获得纬度/经度信息，或者在有多个结果的情况下我会要求用户选择正确的选择。

之后，我开始搜索离我当前位置最近的城镇。假设我想要 50 个城镇/城市的列表。然后，我会去查找包含位置的表是否与其中的 5 个结果匹配。

转念一想，这听起来是个坏主意......

方法一：

我阅读了存储过程、sql 和怪物查询，并尝试得到以下内容：

传递一个邮政编码/城市名称，我会查找它，从巨大的表中取出我的纬度/经度（可能是mysql中的函数），有了这个，我会寻找最近的城镇，然后立即加入位置表并获取我最近的 5 个位置。

问题：

  • 如何避免城市/邮政编码的同名出现多个匹配项？
  • 听起来是否可以通过简单的连接来获得 5 个最近的位置？

方法2：

获取我所在位置的所有纬度/经度值，然后在此表上运行该过程。然后只使用巨大的表来检索我当前的位置？

这样，我就需要收集我所在位置的所有纬度/经度。但这可能是最好的方法。

但是，仅仅为了获取位置而拥有所有城市/邮政编码的庞大数据库似乎有点大材小用。我希望有一个替代方案，然后也许......不知何故......

方法3

说实话，我想要的这个函数好像已经写了一百万遍了。那么我为什么要费心重新发明轮子呢？但我不知道如何找到合适的文章或书籍来实现我的目标。

你们中还有其他人有关于类似事情的最佳实践的想法吗？


首先一些评论...

我在这里和其他论坛上看到了数十（不是数百万）的实施；你的比大多数人都好。

根据一个数据源（我碰巧下载了），世界上大约有 320 万个城市。

为了提高性能，您需要避免检查所有 3M 行。您已经通过不断增长的边界框有了一个良好的开端。请注意，您应该有

INDEX(lat, lon),
INDEX(lon, lat)

优化器将在这些查询和第一个查询之间进行选择（使用COUNT(*)）会将其视为“覆盖”。它将是环绕地球的条纹或楔形；与 300 万行相比有了明显的改进。最差的纬度（+34度）有96K个城市。 （1 度 = 69 英里/111 公里。）对于十分之一度来说，34.4 度是最差的，有 10,000 个城市。

（是的，我喜欢这种数据难题。）

而且，我看到你处理日期变更线和两极。我不认为你可以将它们作为特殊情况进行改进。

（我只看了一眼公式和常数。）

Geohash 和 Z 顺序索引帮助。但他们有一个小问题，你需要检查目标周围最多 4 个区域——这就像没有意识到整数 199999 和 200000 彼此非常接近，尽管每个数字的第一位数字不同。

“用户输入邮政编码或城市名称”——这是对两个简单表之一的点查询。 （只不过可能存在重复——“san jose”和“san antonio”各超过 320 个。列表中靠后的是第一个非西班牙名称：“victoria”，只有 144 个城市。）

其次，我的实现...（它与你的有一些相似之处。）

http://mysql.rjweb.org/doc.php/latlng http://mysql.rjweb.org/doc.php/latlng

这可以通过使用来提高性能PARTITIONing将边界框保持为大致正方形，而不是条纹或楔形。如果您正在寻找最近的 5 个，我的算法很少会涉及超过几十行，并且这些行将“聚集”在少量块中，从而使磁盘命中次数保持在非常低的水平。

我的设计中的一件关键事情是在一个表中包含所有必要的列。一旦找到最近的 5 个，您就可以前往其他桌子获取辅助信息（电话号码等）。

至于邮政编码，在开始搜索 5 个最近的邮政编码之前，将其转换为纬度/经度。

算法内部的连接很可能会破坏性能。

本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)

mysql

sql

storedprocedures

InnoDB

搜索距离邮政编码最近的 5 个地点 - 我应该走哪条路？ 的相关文章

  • Python Peeweeexecute_sql() 示例

    我使用 Peewee 模块作为我的项目的 ORM 我看了整个文档 没有明确的 有关如何处理 db execute sql 结果的示例 我跟踪代码 只能发现db execute sql 返回游标 有谁知道如何处理光标 例如迭代它并获取 返回复

  • 正则表达式中 (*) 和 .* 有什么区别？ [复制]

    这个问题在这里已经有答案了 是任意字符零次或多次 我试图找到以元音开头的单词 我用了 aeiou 它给了我所有以元音开头的单词 当我这样做时给出相同的结果 aeiou 现在我正在寻找以元音结尾的单词 我做到了 aeiou 它没有给出任何结果

  • 根据另一个表中的值查找总计数

    在Mysql中 我的表中有具有重复值的城市 表城市 Name New York USA New York USA Chicago USA Chicago USA Chicago USA Paris France Nice France Mi

  • Postgres 检查文本数组中的约束以确保值的有效性

    我想创建类似的东西 CHECK ALL scopes IN read write delete update scopes这是表中的一个字段text 我想确保该数组中的所有值都是上面的值之一 对此有何意见 是否有可能通过以下方式获取这些值S

  • MySQL 索引创建速度很慢（在 EC2 上）

    我有一张相当简单的桌子 requestparams requestid varchar 64 NOT NULL requestString text ENGINE MyISAM 使用 LOAD DATA 填充表后 我正在更改架构并将 req

  • 将 CSV 文件导入 MySQL 数据库时出现无效的 UTF-8 字符串

    我正在尝试使用以下代码将 CSV 导入我的 MySQL 数据库 我从帖子中获取了 CSV 文件

  • PostgreSQL 列“foo”不存在

    我有一个表 其中有 20 个整数列和 1 个名为 foo 的文本列 如果我运行查询 SELECT from table name where foo is NULL 我收到错误 ERROR column foo does not exist

  • MySQL 的 read_sql() 非常慢

    我将 MySQL 与 pandas 和 sqlalchemy 一起使用 然而 它的速度非常慢 对于一个包含 1100 万行的表 一个简单的查询需要 11 分钟以上才能完成 哪些行动可以改善这种表现 提到的表没有主键 并且仅由一列索引 fro

  • PhpStorm Docker PHPUnit 数据库

    I setup https blog jetbrains com phpstorm 2016 11 docker remote interpreters PhpStorm PHP PHPUnit 与 Docker 我在 PhpStorm 数

  • MySQL 查询 - 使用 ORDER BY rand( ) 强制区分大小写

    是否可以强制查询区分大小写 我的听起来是这样的 SELECT g path FROM glyphs WHERE g glyph g glyph ORDER BY rand 如果 g glyph r 结果可以是 R 或 r 这不是我所期望的

  • SQL Server 中的循环行

    我有一个包含 2 列的 SQL Server 表 Code 和 CodeDesc 我想使用 T SQL 循环遍历行并打印 CodeDesc 的每个字符 怎么做 如果您确实想循环遍历行 则需要光标 CURSOR http msdn micro

  • PHP md5() 给出与 MySQL md5 不同的输出

    我正在尝试设置登录系统 但无法解决一个问题 PHP 通过 md5 给了我另一个输出 比MySQL 例如 在 PHP 中 password md5 brickmasterj return password 返回 3aa7b18f304e2e2

  • ORA-01438: 值大于此列允许的指定精度

    有时我们会从合作伙伴的数据库中收到以下错误 i ORA 01438 value larger than specified precision allows for this column i 完整响应如下所示

  • 用户登录时如何更新updated_at列？

    我正在尝试更新updated at每次用户登录时 列到当前时间 但我收到以下错误 InvalidArgumentException 找不到四位数年份 数据丢失 PHP input Input all remember Input has r

  • 转储中的 MySQL 标志

    在查看 mySQL 转储时 我发现了一些东西并且想知道它们是什么 I see 50001 DROP TABLE IF EXISTS xxx 标志 50001 是什么 有它们含义的列表吗 它在 MySQL 的论坛 邮件列表上进行了讨论here

  • @Where 子句在 hibernate join 查询中不起作用

    我有 2 个带有 Where 注释的实体 第一个是类别 Where clause DELETED 0 public class Category extends AbstractEntity 且有如下关系 OneToMany fetch F

  • 发送 QUERY 数据包时出错。 PID=9565

    我有两个不同的环境开发和生活几乎都是相同的 但上述 标题中 警告仅在开发模式下发生 在此警告之前 我还收到错误消息 允许的内存大小 268435456 字节已耗尽 这仅发生在开发模式下 使用 PHP 版本 5 6 和 mysql 不是 my

  • oracle 计算两个字符串中连续匹配的单词

    我想要一个返回两个字符串中单词的顺序匹配数的查询 例子 Table Id column1 column2 result 1 foo bar live foo bar 2 2 foo live tele foo tele 1 3 bar fo

  • 如何在JdbcTemplate中执行多批量删除？

    我想一次删除多个数据库条目 仅当 3 个字段匹配 此处 姓名 电子邮件 年龄 时 才应删除每个条目 如果我只想删除单个属性 我会选择 String sql DELETE FROM persons WHERE email IN JdbcTem

  • 将文件保存为 MYSQL 数据库中的 blob 或文件路径

    我知道这些问题是常见问题之一 但我需要您针对具体案例提供帮助 我正在开发一个应用程序 其中一些用户可以添加订单 一些用户可以执行这些订单 这些订单非常具体 因此只有有限数量的用户可以添加它们 然后 为每个订单生成三个文档 每个文档的大小不超

随机推荐

热门标签