在MySQL Geometry数据类型列中查询数据 [英] Querying data in MySQL Geometry data type column

问题描述

我有一个表，该表在我使用ogr2​​ogr通过shapefile加载的GEOMETRY数据类型列中存储多边形.这是create语句:

I have a table that stores polygons in a GEOMETRY data type column that I loaded via a shapefile using ogr2ogr. Here's the create statement:

CREATE TABLE IF NOT EXISTS `sunzones` (
  `OGR_FID` int(11) NOT NULL AUTO_INCREMENT,
  `polygon` geometry NOT NULL,
  UNIQUE KEY `OGR_FID` (`OGR_FID`),
  SPATIAL KEY `polygon` (`polygon`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=105 ;

以下是示例记录:

POLYGON((449455.354522821 2447255.57758461,449700.419971925 2447132.08575524,449970.797988416 2447012.08302579,450331.302189915 2446845.08750142,450720.557402377 2446631.12366577,451001.686314893 2446471.65633497,451351.253709065 2446236.2071619,451621.320850174 2445889.34918971,451822.94912776 2445643.44939179,451970.329536186 2445268.16443891,451900.90187755 2444641.13680345,451762.850153972 2443964.1640728,451483.986510987 2443208.22160261,451201.806260121 2442650.10616671,451020.687327243 2442095.95148262,450839.505763005 2441544.78980207,450683.636511575 2441015.10597334,450538.454805135 2440413.89081544,450433.333155727 2440035.55620047,450360.648999258 2439678.70563045,450288.151292623 2439419.31682052,450118.029551282 2439008.99974342,449901.03211162 2438634.67295293,449702.407549484 2438469.26337817,449341.345080443 2438239.89817619,449070.656839706 2438139.47157292,448886.71805136 2438143.99566917,448634.339635752 2438227.50240038,448385.707435195 2438433.96784289,448234.638650137 2438747.88628722,448145.380437968 2439372.19635416,448095.562049752 2439852.04892374,447984.553987549 2440439.87931978,447808.669818343 2441024.70524153,447592.724965448 2441364.09711298,447275.840595988 2441754.49255107,447067.208249397 2442148.36776757,446617.319564483 2442841.67896542,446426.750106166 2443289.53943803,446257.992375297 2443841.82345023,446157.983746886 2444530.46142231,446014.475379242 2445071.69774818,445881.655839275 2445515.48887139,445748.650167696 2445843.84709753,445669.894930651 2446244.64998972,445684.828488262 2446608.40816774,445779.199887883 2446997.59260231,445887.760935538 2447234.8818733,446155.073525621 2447526.07468538,446451.07445614 2447654.87106738,446714.451262744 2447697.24267349,446959.766023142 2447696.15327556,447233.893916706 2447670.07230515,447450.271461094 2447633.52082824,448020.090403634 2447559.36967915,448377.094925486 2447485.7971982,448943.225936908 2447365.18349843,449455.354522821 2447255.57758461))

使用以下命令查询了哪个

Which was queried using:

SELECT ASTEXT( POLYGON ) FROM  `sunzones`;

我有一个特定的纬度和经度，并且我想知道哪些记录具有包含该经度/纬度的多边形，所以我尝试了以下查询:

I've got a specific latitude and longitude, and I want to know which records have polygons that encompass that lat/lng, so I've tried this query:

SELECT * FROM sunzones WHERE MBRWithin(GeomFromText('Point(21.3069 -157.8583)'), polygon);

，但不返回任何记录.我什至尝试将lat/lng转换为UTM坐标，但此查询也未返回任何记录:

but that returns no records. I even tried to convert the lat/lng to UTM coordinates, but this query also returns no records:

SELECT * FROM sunzones WHERE MBRWithin(GeomFromText('Point(2447696.15327556 447233.893916706)'), polygon);

我的查询出问题了吗?

顺便说一句，我正在使用MySQL 5.6.另外，我也尝试过ST_CONTAINS()和CONTAINS().

I'm using MySQL 5.6, by the way. Also, I've tried ST_CONTAINS() and CONTAINS(), as well.

请注意，查询中的坐标不一定在我上面发布的单个示例多边形内.形状全在夏威夷，所以我的经/纬度只是檀香山的座标.

推荐答案

花了很长时间弄乱了这个意思之后，我想出了查询的正确语法:

After spending way too long messing with this, I came up with the right syntax for the query:

SELECT * FROM sunzones WHERE MBRWITHIN( POINT( 863575.082797506, 2137306.79465704 ) , polygon ) 

这里要注意的重要事项是坐标是相反的(经度/纬度，而不是纬度/经度)，并且与大多数发布的示例不同，我不必使用GeomFromText()来声明Point.

The important things to note here are that the coordinates are reversed (longitude/latitude, not latitude/longitude), and unlike most examples posted, I didn't have to use GeomFromText() to declare the Point.

这篇关于在MySQL Geometry数据类型列中查询数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！