MySQL – Problem Getting ZIP Codes in Radius

geometryMySQLspatial

I have a table of zip codes which includes the center lat, lng for each zip code. I use it to get a list of zip codes within a given mile radius from any arbitrary point.

It just occurred to me that, just because a zip's center point is not within a given radius, does not mean that the zip itself is not within the radius.

I used my super advanced art skills to illustrate the point here:

enter image description here

  • The green stripy blobs represent zip codes A, B and C.

  • The red smudges are the geographic centers for each zip code

  • The fuchsia dot is the target location, and..

  • The lumpy blue circle is a 1 mile radius from the target location

If I run a query for all the zip codes within a 1 mile radius from the pink smudge, only zip codes B and C will be returned as the center point for zip A is not within the one mile radius, even though the pink smudge itself is clearly in zip code A.

SELECT *,
        p.distance_unit
                 * DEGREES(ACOS(COS(RADIANS(p.latpoint))
                 * COS(RADIANS(z.y))
                 * COS(RADIANS(p.longpoint) - RADIANS(z.x))
                 + SIN(RADIANS(p.latpoint))
                 * SIN(RADIANS(z.y)))) AS dist
  FROM standard_zip AS z
  JOIN (   /* these are the query parameters */
        SELECT  $lat  AS latpoint,  $lng AS longpoint,
                $miles AS radius,      69 AS distance_unit
    ) AS p ON 1=1
  WHERE z.y
     BETWEEN p.latpoint  - (p.radius / p.distance_unit)
         AND p.latpoint  + (p.radius / p.distance_unit)
    AND z.x
     BETWEEN p.longpoint - (p.radius / (p.distance_unit * COS(RADIANS(p.latpoint))))
         AND p.longpoint + (p.radius / (p.distance_unit * COS(RADIANS(p.latpoint))))
  ORDER BY dist

How the heck do I write a query that will include zip A in the results?

I have access to spatial/geometry for each zip code that I can add to the table if needed, but I have no idea how I would use it for this purpose in MySQL.

Edit: I spent a day reading the Oracle and MySQL docs for spatial data and managed to successfully convert my spatial data to MySQL. How do I go about writing a similar query that uses the geometry column instead of the lat and long? I am using 2D data.. the geometry are polygons and multipolygons only..

I think I sort of figured it out..

select
  *
from
  (
    select
      MIN(st_distance(geom, POINT(-82.765136, 28.0914015))) * 69 as miles,
      zip
    from
      zip_spatial
    group by
      zip
    order by
      miles asc
  ) d
where
  d.miles < 5

I'll leave the bounty open for now in case someone has a better, more efficient solution.

Best Answer

From Indexing and Querying Spatial Data in Oracle in the Oracle® Spatial Developer's Guide 11g Release 2 (11.2):

Querying Spatial Data

Spatial uses a two-tier query model with primary and secondary filter operations to resolve spatial queries and spatial joins. The term two-tier indicates that two distinct operations are performed to resolve queries. If both operations are performed, the exact result set is returned.

You cannot append a database link (dblink) name to the name of a spatial table in a query if a spatial index is defined on that table.

Spatial Query

In a spatial R-tree index, each geometry is represented by its minimum bounding rectangle (MBR). Consider the following layer containing several objects in Figure1. Each object is labeled with its geometry name (geom_1 for the line string, geom_2 for the four-sided polygon, geom_3 for the triangular polygon, and geom_4 for the ellipse), and the MBR around each object is represented by a dashed line.

Figure1 Geometries with MBRs

Description of "Figure1 Geometries with MBRs"

A typical spatial query is to request all objects that lie within a query window, that is, a defined fence or window. A dynamic query window refers to a rectangular area that is not defined in the database, but that must be defined before it is used. Figure2 shows the same geometries as in Figure1, but adds a query window represented by the heavy dotted-line box.

Figure2 Layer with a Query Window

Description of "Figure2 Layer with a Query Window"

In Figure 2, the query window covers parts of geometries geom_1 and geom_2, as well as part of the MBR for geom_3 but none of the actual geom_3 geometry. The query window does not cover any part of the geom_4 geometry or its MBR.

Primary Filter Operator

The SDO_FILTER operator, implements the primary filter portion of the two-step process involved in the Oracle Spatial query processing model. The primary filter uses the index data to determine only if a set of candidate object pairs may interact. Specifically, the primary filter checks to see if the MBRs of the candidate objects interact, not whether the objects themselves interact. The SDO_FILTER operator syntax is as follows:

SDO_FILTER(geometry1 SDO_GEOMETRY, geometry2 SDO_GEOMETRY, param VARCHAR2)

In the preceding syntax:

  • geometry1 is a column of type SDO_GEOMETRY in a table. This column must be spatially indexed.

  • geometry2 is an object of type SDO_GEOMETRY. This object may or may not come from a table. If it comes from a table, it may or may not be spatially indexed.

  • param is an optional string of type VARCHAR2. It can specify either or both of the min_resolution and max_resolution keywords.

The following examples perform a primary filter operation only (with no secondary filter operation). They will return all the geometries shown in Figure2 that have an MBR that interacts with the query window. The result of the following examples are geometries geom_1, geom_2, and geom_3.

Example1 performs a primary filter operation without inserting the query window into a table. The window will be indexed in memory and performance will be very good.

Example1 Primary Filter with a Temporary Query Window

SELECT A.Feature_ID FROM TARGET A  WHERE sdo_filter(A.shape, SDO_geometry(2003,NULL,NULL,
                                       SDO_elem_info_array(1,1003,3),
                                       SDO_ordinate_array(x1,y1, x2,y2))
                           ) = 'TRUE';

In Example1, (x1,y1) and (x2,y2) are the lower-left and upper-right corners of the query window.

Related Question