我有一个表AREAGEOMETRY,其结构如下:
+-----------------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+----------+------+-----+---------+-------+
| AREAGEOMETRY_ID | int(11) | NO | PRI | NULL | |
| AreaManagerId | int(11) | YES | | NULL | |
| AreaId | text | YES | | NULL | |
| EndDateArea | datetime | YES | | NULL | |
| StartDateArea | datetime | YES | | NULL | |
| AreaGeometryTxt | text | YES | | NULL | |
+-----------------+----------+------+-----+---------+-------+
包含来自停车场的数据。现在我要做的是选择一个边界框内的所有行。
边界框可以是:
LatLngBounds{southwest=lat/lng: (52.35631327204287,4.881156384944916), northeast=lat/lng: (52.38006384519922,4.913054890930653)}
我提出了以下查询:
SELECT * FROM AREAGEOMETRY WHERE ST_OVERLAPS(GeomFromText(AreaGeometryTxt), GeomFromText('LINESTRING(52.35631327204287 4.881156384944916, 52.38006384519922 4.881156384944916, 52.38006384519922 4.913054890930653, 52.35631327204287 4.913054890930653, 52.35631327204287 4.881156384944916)'))
然而,它似乎返回表中的所有行,我不知道这里出了什么问题。也许有人能给我指个方向。
编辑:例如,它返回这一行:
# AREAGEOMETRY_ID, AreaManagerId, AreaId, EndDateArea, StartDateArea, AreaGeometryTxt
493, 299, 8721, 0000-00-00 00:00:00, 0000-00-00 00:00:00, POLYGON ((6.071624141 51.927465383, 6.071167939 51.927755315, 6.073816653 51.928513734, 6.07434586 51.928376592, 6.072239751 51.927748706, 6.072269225 51.927414931, 6.071624141 51.927465383))
它不应该。
Edit2:一些可能的视口及其结果:
Viewport 1:
LatLngBounds{southwest=lat/lng: (52.367693923958065,6.981273405253887), northeast=lat/lng: (52.3812037840295,6.99942022562027)}
查询:SELECT * FROM AREAGEOMETRY WHERE ST_CONTAINS(GeomFromText('LINESTRING(52.367693923958065 6.981273405253887, 52.3812037840295 6.981273405253887, 52.3812037840295 6.99942022562027, 52.367693923958065 6.99942022562027, 52.367693923958065 6.981273405253887)'), GeomFromText(AreaGeometryTxt));
预期结果:返回0行
实际结果:返回0行
窗口2:
LatLngBounds{southwest=lat/lng: (52.20765248996001,6.881230026483536), northeast=lat/lng: (52.23028692988024,6.911527253687382)}
查询:SELECT * FROM AREAGEOMETRY WHERE ST_CONTAINS(GeomFromText('LINESTRING(52.20765248996001 6.881230026483536, 52.23028692988024 6.881230026483536, 52.23028692988024 6.911527253687382, 52.20765248996001 6.911527253687382, 52.20765248996001 6.881230026483536)'), GeomFromText(AreaGeometryTxt));
预期结果:返回约25行
实际结果:返回0行
另一个查询相同的视图:
SELECT * FROM AREAGEOMETRY WHERE ST_Overlaps(GeomFromText('LINESTRING(52.20765248996001 6.881230026483536, 52.23028692988024 6.881230026483536, 52.23028692988024 6.911527253687382, 52.20765248996001 6.911527253687382, 52.20765248996001 6.881230026483536)'), GeomFromText(AreaGeometryTxt));
预期结果:返回约25行
实际结果:返回1000行(受MySQL Workbench限制)
编辑3:下面的查询返回我想要的结果:
SELECT * FROM AREAGEOMETRY WHERE ST_Intersects(GeomFromText('Polygon((6.881230026483536 52.20765248996001, 6.881230026483536 52.23028692988024, 6.911527253687382 52.20765248996001, 6.881230026483536 52.20765248996001))'), GeomFromText(AreaGeometryTxt));
似乎我混淆了Lat/Lng,并且参数的顺序错误。
您应该使用Contains或Intersects,这取决于您是想要包含边框上的对象,还是想要完全包含。然而,你的主要问题是,你有几何形状的错误方式,如果你看一下包含文档,你会看到它是包含(g1, g2)返回1,如果g1包含g2,所以你会想把你的边界框。
SELECT * FROM AREAGEOMETRY WHERE ST_CONTAINS(ST_GeomFromText('LINESTRING(52.35631327204287 4.881156384944916, 52.38006384519922 4.881156384944916, 52.38006384519922 4.913054890930653, 52.35631327204287 4.913054890930653, 52.35631327204287 4.881156384944916)'), ST_GeomFromText(AreaGeometryTxt));
您可能还想考虑将AreaGeometryTxt存储为几何而不是文本,因为这将为您提供两个优势:
你可以在它上面放一个空间索引,这将导致更快的查询时间随着表大小的增长。
您将避免每次查询时GeomFromText转换的开销,这与第一点相结合,将防止每次进行全表扫描。
EDIT:我运行了以下查询,使用您说不应该返回的行,以及原始的纬度/长度边界框:
select ST_Overlaps(ST_GeomFromText('POLYGON ((6.071624141 51.927465383, 6.071167939 51.927755315, 6.073816653 51.928513734, 6.07434586 51.928376592, 6.072239751 51.927748706, 6.072269225 51.927414931, 6.071624141 51.927465383))'),
ST_GeomFromText('POLYGON ((4.881156384944916 52.35631327204287, 4.881156384944916 52.38006384519922, 4.913054890930653 52.38006384519922, 4.913054890930653 52.35631327204287, 4.881156384944916 52.35631327204287))'));
对于overlaps
, intersects
和contains
,该查询返回0 (false)。