Problem getting zips in radius via MySQL

Question

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:

• 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.

Answer 1

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.

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.

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.

Answer 2

Any attempt to includ A will probably include D, E, F, G. The problem cannot be solved without having an exact path defining each zip code area.

Find such a database, then build a SPATIAL index using such arbitrary polygons.

Answer 3

You're doing it wrong. First, if possible, use PostGIS -- which is the leading RDMBS with Spatial solution.

Then you want to follow these steps.

1. Pull down the ZCTA (Zip Code Tabulation Areas) from the Census's TIGER dataset. Zip codes are not actually know for certain. Officially, zip codes are for internal use by the USPS only. Because everyone uses them, including the government, the second most authoritative source has become the ZCTA shapefiles.
2. Import these shapefiles into your database, with PostgreSQL you can easily use shp2pgsql

3. Index the geometry you imported.

   CREATE INDEX ON census_zcta USING gist (geog);
   ANALYZE census_zcta;
   

4. Run a Point-of-Interest (POI) query against the shapefiles. The point-of-interest in your case is the Input Cords, this will look like this,

   SELECT *
   FROM census_zcta AS zcta
     WHERE ST_Intersects( zcta, ST_MakePoint(long,lat)::geog );
   

ℹ 1609.344 Meters = 1 Mile

MySQL

With MySQL you'll have

1. Use ogr2ogr to output MySQL insert statements for the Census Shapefile.

2. Use MBRIntersects to utilize the spatial index. End query should look something like

   SELECT *
   FROM zcta
   WHERE MBRIntersects( geom, Point(long,lat) )
     AND ST_Intersects ( geom, Point(long,lat) );
   

Answer 4

Check out this data set from GreatData.com (note that this is not open source but a paid service).

They use population density instead of center of zip.

And how to use sql server spatial data type to get fast correct results.

• http://mitchelsellers.com/blogs/2012/01/23/simple-zip-code-to-zip-code-distance-calculations-with-sql-server.aspx
• http://blogs.lessthandot.com/index.php/datamgmt/datadesign/sql-server-2008-proximity-search-with-th/

Hope this helps.