9

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.

7
+100

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.

  • Cool.. So I should create the circle geometry to represent the radius and then just see which polygons intersect.. interesting.. thx for the info – I wrestled a bear once. May 3 '17 at 10:24
  • Yeah..go on...Hope it works fine for you. – l.lijith May 3 '17 at 18:23
5

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.

  • I know I need spatial data (and I have it, but it's in an Oracle table and I'm not finding much info on how to convert it).. problem is figuring out how to query the data. – I wrestled a bear once. May 1 '17 at 14:08
  • If you are happy with the performance of the new code, that is probably the best. Note: The query lists the distance to the every zip, so there is probably no optimization potential. (I will be pleasantly surprised if you get better code.) – Rick James May 2 '17 at 14:11
  • that's sort of what I'm thinking too. I'll give you the bounty before it times out and you get half of it anyway.. just wanna see what other responses I might get first. – I wrestled a bear once. May 2 '17 at 14:17
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) );
    
  • 2
    1) i know i was doing it wrong. that is why i asked. 2) the company i work for has paid access to usps internal zip code boundaries. we worked directly with usps for this project, and 3) generally, suggesting that OP uses an entirely different toolset is not a proper answer. – I wrestled a bear once. Jan 21 at 13:54
  • @iwrestledabearonce You can do all of this stuff with MySQL 8 too just substitute the ST_DWithin with MBRIntersects – Evan Carroll Jan 22 at 5:57
  • "paid access to usps internal zip code boundaries" do you happen to know the name of that product? AFAIK there is no such thing. (though USPS does offer 2 data products and some APIs for decoding address) – Evan Carroll Jan 22 at 6:02
  • thank you for adding the info about mysql. +1. the api is not public and it isn't listed on anywebsite, in fact the endpoint url doesn't even have a domain name, we request it directly from the ip address. however, just to prove the api exists it is listed in this document (the 3 that refer to EDDM are the ones i'm referring to) usps.com/business/web-tools-apis/archive/… – I wrestled a bear once. Jan 22 at 19:11
  • That actually does seem legitimate if you're pulling off EDDM/SelectZIP endpoint. That's not advertised for that purpose, but kudos to finding that end point. – Evan Carroll Jan 22 at 19:39
1

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.

Hope this helps.

  • Is this data set available for MySQL or is it for SQL Server only? – ypercubeᵀᴹ Jan 22 at 8:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.