CODEWITHSUNDEEP
mysqlsqlgeospatial
Rik
Rik

Reputation: 90

MySQL - Select all geometries inside a rectangle

I have a table AREAGEOMETRY with the following structure:

+-----------------+----------+------+-----+---------+-------+
| 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    |       |
+-----------------+----------+------+-----+---------+-------+

It contains data from parking zones. Now what I am trying to do is selecting all rows within a bounding box.

A bounding box may be the following: 

LatLngBounds{southwest=lat/lng: (52.35631327204287,4.881156384944916), northeast=lat/lng: (52.38006384519922,4.913054890930653)}

I came up with the following query for this: 

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)'))

However it seems to return all the rows in the table, I have no idea what is going wrong here. Maybe someone could point me in the right direction.

Edit:

For example it returns this row:

# 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))

Which it shouldn't.

Edit2: Some possible viewports and their results:

Viewport 1: 

LatLngBounds{southwest=lat/lng: (52.367693923958065,6.981273405253887), northeast=lat/lng: (52.3812037840295,6.99942022562027)}

Query:

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));

Expected Result: 0 rows returned

Actual Result: 0 rows returned

Viewport 2:

LatLngBounds{southwest=lat/lng: (52.20765248996001,6.881230026483536), northeast=lat/lng: (52.23028692988024,6.911527253687382)}

Query:

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));

Expected Result: About 25 rows returned

Actual Result: 0 rows returned

Another query same viewport: 

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));

Expected Result: About 25 rows returned

Actual Result: 1000 rows returned (limited by MySQL Workbench)

Edit 3: The following query returns exactly what I want:

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));

Seems like I mixed up Lat/Lng and had the parameters in the wrong order.

Upvotes: 1

Views: 2442

Answers (1)

John Powell
John Powell

Reputation: 12581

You should use Contains or Intersects, depending on whether you want objects on the border to be included, or whether you want full containment. However, your main issue, is that you have the geometries the wrong way round, if you look at the Contains documentation you will see it is Contains(g1, g2) returns 1 if g1 contains g2, so you will want to put your bounding box first.

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));

You might also want to consider storing AreaGeometryTxt as a geometry, rather than text, as this will give you two advantages:

  1. You can then put a spatial index on it, which will lead to much faster query times as table size grows.

  2. You will avoid the overhead of the GeomFromText conversion on each query, which in conjunction with point 1, will prevent doing a full table scan each time.

EDIT: I ran the following query, using the row you say should not be returned, and your original lat/lon bounding box:

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))'));

This query returned 0 (false) for overlaps, intersects and contains as it should.

Upvotes: 2

Related Questions