PostGIS ST_Intersects Precision Issues with Bounding Box Queries

PostGIS ST_Intersects sometimes misses points when querying by bounding box due to floating-point precision limitations and boundary condition handling. This inconsistent behavior occurs because PostGIS stores all coordinate values as double-precision floating point integers, which can represent up to 15 significant digits but may introduce tiny precision errors near boundaries that cause points to sometimes fall just outside the mathematical boundary of your bounding box.

Contents

• Understanding the Precision Problem
• Bounding Box Creation with ST_MakeEnvelope
• Spatial Indexing and Query Optimization
• Antimeridian and Coordinate System Considerations
• Solutions for Consistent Results
• Best Practices for Spatial Queries

Understanding the Precision Problem

The root cause of your inconsistent PostGIS ST_Intersects behavior lies in how floating-point numbers are handled in spatial databases. According to the PostGIS precision documentation, “PostGIS stores all coordinate values as double-precision floating point integers, which can reliably represent 15 significant digits.”

When you create a bounding box using ST_MakeEnvelope, you’re defining mathematical boundaries that have exact coordinates. However, when your point geometries were originally created, they might have undergone transformations or operations that introduced tiny precision errors. These errors can cause points to sometimes fall slightly outside the mathematical boundary of your envelope.

The problem becomes apparent when you move coordinates slightly - these tiny precision errors can push a point across the boundary in one direction but bring it back within bounds when moved further. This creates the inconsistent behavior you’re experiencing where points appear and disappear from your results as coordinates change.

Floating Point Precision Issues

Double-precision floating point numbers can represent numbers with up to 15-17 significant decimal digits. However, they cannot represent all decimal numbers exactly. This means that:

0.1 + 0.2 != 0.3  // In floating-point arithmetic!

In spatial contexts, this can cause issues when comparing whether a point with coordinates like (5.372134, 85.0868074) is exactly on the boundary of a polygon created with ST_MakeEnvelope. Tiny representation errors can make a point that should be exactly on the boundary appear slightly outside it.

Boundary Condition Handling

The ST_Intersects documentation states that this function “Performed by the GEOS module (for geometry), geography is native.” GEOS has specific rules about how it handles boundary conditions, particularly when dealing with points exactly on polygon boundaries.

This explains why you’re seeing inconsistent results - small movements in coordinates can push a point across a threshold where the precision error causes it to be classified differently by ST_Intersects.

Bounding Box Creation with ST_MakeEnvelope

When you use ST_MakeEnvelope to create your bounding box, you’re essentially creating a rectangular polygon from minimum and maximum values for X and Y coordinates. The official documentation explains that “Input values must be in the spatial reference system specified by the SRID.”

Several factors related to ST_MakeEnvelope usage could contribute to your inconsistent results:

Coordinate System Mismatches

If your point geometries are in a different SRID than what you’re specifying in ST_MakeEnvelope, you might encounter precision issues. The transformation between coordinate systems can introduce small errors that affect boundary conditions.

Envelope Creation Precision

Even when specifying coordinates for your envelope, tiny representation errors can occur. For example:

-- This might not create an exact boundary due to floating-point precision
SELECT ST_MakeEnvelope(5.372134, 36.556537, 5.9306269, 85.0868074, 4326);

The resulting polygon coordinates might not be exactly what you specified due to how floating-point numbers are represented internally.

Antimeridian Considerations

As discussed in this StackExchange post, there are special considerations when creating envelopes that cross the international dateline (antimeridian). If your bounding box spans this line, you might need special handling to ensure proper geometry creation.

Spatial Indexing and Query Optimization

The way your spatial queries are executed can significantly impact the consistency of your results. According to the PostGIS indexing documentation, “To do a bounding-box search using the index (and no filtering), make use of the && operator.”

Index Usage and Operator Choice

By default, ST_Intersects might not always use spatial indexes optimally, especially when dealing with complex boundary conditions. The && operator (bounding box intersection) is more efficient for initial filtering:

-- More efficient initial filtering
SELECT * FROM your_table 
WHERE geom && ST_MakeEnvelope(xmin, ymin, xmax, ymax, srid)
AND ST_Intersects(geom, ST_MakeEnvelope(xmin, ymin, xmax, ymax, srid));

Bounding Box vs. Precise Intersection

The && operator performs a simple bounding box check, which is faster but less precise than ST_Intersects. When you’re experiencing inconsistent results, it’s worth checking if your spatial index is being used properly. The PostGIS spatial queries documentation explains that “This is enabled by ST_DWithin() using the && operator internally on an expanded bounding box of the query geometry.”

Query Planner Considerations

Sometimes the query planner might choose not to use the spatial index if it estimates a full table scan would be more efficient. This can lead to inconsistent behavior, especially as your dataset grows or changes.

Antimeridian and Coordinate System Considerations

When working with global datasets, especially those crossing the international dateline, standard bounding box approaches can fail. As mentioned in the antimeridian discussion, standard envelopes might wrap around the globe in unexpected ways.

If your bounding box crosses longitude 180°/-180°, you might need special handling:

-- Special handling for antimeridian crossing
SELECT * FROM your_table 
WHERE ST_Intersects(
  geom, 
  ST_MakeEnvelope(
    CASE WHEN xmin > xmax THEN xmax - 360 ELSE xmin END,
    ymin,
    CASE WHEN xmin > xmax THEN xmax ELSE xmax + 360 END,
    ymax, 
    4326
  )
);

Geographic vs. Geometry Types

The choice between geometry and geography data types can affect how intersections are calculated. The ST_Intersects documentation notes that “geography is native” while geometry uses the GEOS module. For global datasets, the geography type might provide more consistent results.

Solutions for Consistent Results

Several approaches can help you achieve consistent results with PostGIS ST_Intersects:

Precision Reduction

The most direct solution is to reduce the precision of your geometries before comparison. The ST_ReducePrecision function allows you to specify the number of significant digits to maintain:

-- Reduce precision to avoid floating-point boundary issues
SELECT * FROM your_table 
WHERE ST_Intersects(
  ST_ReducePrecision(geom, 6), 
  ST_ReducePrecision(ST_MakeEnvelope(xmin, ymin, xmax, ymax, srid), 6)
);

This approach forces all coordinates to have the same precision level, eliminating tiny representation differences that cause boundary inconsistencies.

Snap to Grid

For point geometries, ST_SnapToGrid provides an alternative precision control method:

-- Snap points to a consistent grid
SELECT * FROM your_table 
WHERE ST_Intersects(
  ST_SnapToGrid(geom, 0.000001), 
  ST_MakeEnvelope(xmin, ymin, xmax, ymax, srid)
);

This method snaps points to a regular grid, eliminating tiny precision variations.

Buffer and Contains Approach

Another strategy is to use ST_Contains with a slightly buffered bounding box:

-- Use Contains with a small buffer
SELECT * FROM your_table 
WHERE ST_Contains(
  ST_Buffer(ST_MakeEnvelope(xmin, ymin, xmax, ymax, srid), 0.000001),
  geom
);

The small buffer ensures that points very near the boundary are consistently included.

Use Geography Type for Global Data

If you’re working with global datasets, consider using the geography type:

-- Convert to geography for consistent global results
SELECT * FROM your_table 
WHERE ST_Intersects(
  geom::geography, 
  ST_MakeEnvelope(xmin, ymin, xmax, ymax, 4326)::geography
);

The geography type handles the Earth’s curvature and antimeridian crossings more naturally.

Best Practices for Spatial Queries

To ensure consistent results with PostGIS ST_Intersects:

1. Be explicit about SRIDs: Always specify the SRID in your envelope creation and ensure it matches your data.

2. Use appropriate precision: Choose a precision level appropriate for your use case. As noted in the precision discussion, “testing for equality between original and reprojected data must be done by limiting precision.”

3. Combine operators for efficiency: Use && for initial bounding box filtering followed by ST_Intersects for precise checking.

4. Consider your coordinate system: For global datasets, geography type often provides more consistent results than geometry.

5. Handle boundary conditions: If you need exact boundary matching, consider using ST_Touches or ST_Contains instead of ST_Intersects.

6. Normalize data transformations: If your data undergoes multiple transformations, consider applying a consistent precision reduction at each step.

7. Test with known edge cases: Create test cases with points exactly on boundaries to verify your query behavior.

8. Document your approach: Document the precision level and methods you’re using to ensure consistent behavior across your application.

Sources

• PostGIS ST_MakeEnvelope Documentation
• PostGIS ST_Intersects Documentation
• PostGIS ST_ReducePrecision Documentation
• PostGIS Spatial Queries Documentation
• PostGIS Spatial Indexing Documentation
• Performing Bounding Box Query in PostGIS
• ST_Intersects in WHERE clause misses results
• Using ST_MakeEnvelope over the antimeridian
• Avoiding precision problems with ST_Transform

Conclusion

The inconsistent behavior you’re experiencing with PostGIS ST_Intersects is primarily due to floating-point precision limitations and how boundary conditions are handled in spatial queries. By understanding these precision issues and implementing appropriate solutions like precision reduction using ST_ReducePrecision or ST_SnapToGrid, you can ensure consistent results when querying points within your bounding boxes. For best results, combine these precision controls with proper spatial indexing techniques and appropriate choice between geometry and geography data types based on your specific use case.