Problem getting zips in radius via MySQL

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.


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.


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

Tags:

Mysql

Geometry

Spatial

Recent Posts