使用 MySQL 5.6 在多边形内查找 Long/Lat-数据库问题

Find Long/Lat inside of polygon with MySQL 5.6(使用 MySQL 5.6 在多边形内查找 Long/Lat)

本文介绍了使用 MySQL 5.6 在多边形内查找 Long/Lat的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我有一个 locations 表，其中包含许多行位置和数据.里面有两列——经度和纬度.我想从该表中查找在定义的地图多边形区域中找到的记录.

我有第二个表，它是根据一些名为 polyThing 的教程创建的，它有我定义的边界...

CREATE TABLE polyThing(`ID` int auto_increment 主键，`boundary` 多边形不为空，`testarea` varchar(200) NOT NULL);INSERT INTO polyThing(边界，测试区域)价值观(PolygonFromText('多边形((-114.018522 46.855932 ,-113.997591 46.856030，-113.997447 46.848626，-114.018161 46.848824，-114.018522 46.855932))'), '测试区 1');

我想从 locations 中找到该定义的多边形内的记录，并且我一直在尝试与此类似的查询.无论我尝试什么，我都会得到 0 条记录.

选择 *从位置ST_CONTAINS((从 polyThing 中选择边界WHERE polyThing.testarea = '测试区 1'), 点(经度, 纬度))

这是locations表和数据:

CREATE TABLE `locations` (`LocationID` int(11) 非空，`Location` varchar(200) NOT NULL,`Longitude` varchar(200) NOT NULL,`纬度` varchar(200) NOT NULL) ENGINE=InnoDB 默认字符集=latin1 ROW_FORMAT=COMPACT;插入`locations`(`LocationID`、`Location`、`Longitude`、`Latitude`)值(1, '在多边形中', '-114.007191', '46.853019'),(2, '在多边形中', '-114.003798', '46.851045'),(3, '不在', '-114.016934', '46.866098');

喜欢在正确的方向上轻推.

解决方案

我猜 (Longitude, l.Latitude) 在位置?

I have a locations table that has many rows of locations and data. There are 2 columns inside of that - Longitude and Latitude. I want to find records from that table that are found in a defined map polygon area.

I have a second table that I just created based on some tutorials called polyThing which has my defined boundary...
CREATE TABLE polyThing ( `ID` int auto_increment primary key, `boundary` polygon not null, `testarea` varchar(200) NOT NULL ); INSERT INTO polyThing (boundary, testarea) VALUES( PolygonFromText( 'POLYGON(( -114.018522 46.855932 , -113.997591 46.856030 , -113.997447 46.848626 , -114.018161 46.848824 , -114.018522 46.855932 ))' ), 'Test Area 1' );
I want to find records from locations that are inside this defined polygon, and I've been trying queries similar to this. I get 0 records no matter what I try.
SELECT * FROM locations WHERE ST_CONTAINS( (SELECT boundary FROM polyThing WHERE polyThing.testarea = 'Test Area 1') , Point(Longitude, Latitude))
Here is locations table and data:
CREATE TABLE `locations` ( `LocationID` int(11) NOT NULL, `Location` varchar(200) NOT NULL, `Longitude` varchar(200) NOT NULL, `Latitude` varchar(200) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT; INSERT INTO `locations` (`LocationID`, `Location`, `Longitude`, `Latitude`) VALUES (1, 'In the polygon', '-114.007191', '46.853019'), (2, 'In the polygon', '-114.003798', '46.851045'), (3, 'Not in', '-114.016934', '46.866098');
Love to get a nudge in the right direction.
解决方案
I guess (Longitude, l.Latitude) are in locations?

Sql DEMO:
SELECT l.*, ST_CONTAINS(p.boundary, POINT(l.Longitude, l.Latitude)) FROM locations l CROSS JOIN polyThing p; SELECT l.* FROM locations l JOIN polyThing p ON ST_CONTAINS(p.boundary, POINT(l.Longitude, l.Latitude)) AND p.testarea = 'Test Area 1';
OUTPUT

这篇关于使用 MySQL 5.6 在多边形内查找 Long/Lat的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！