Amazon Redshift Python UDF调用Shapely GEOS函数遇权限拒绝错误

Let's break down how to resolve this permission issue when importing shapely.wkt in your Redshift UDF. The core problem is that Shapely's WKT module depends on the GEOS shared library, which Redshift can't access properly due to either missing compatible binaries or permission/path misconfiguration.

Option 1: Use Redshift's Native Geospatial Functions (Simplest Solution) #

Before diving into fixing the Shapely setup, consider using Redshift's built-in geospatial functions—they're optimized for Redshift and avoid external library headaches entirely. Here's how to replicate your desired functionality:

CREATE OR REPLACE FUNCTION is_point_in_multipolygon(
    latitude float, 
    longitude float, 
    wkt_multipolygon varchar
)
RETURNS boolean
STABLE
AS $$
-- Redshift uses (longitude, latitude) order for ST_Point
SELECT ST_Contains(
    ST_GeomFromText(wkt_multipolygon), 
    ST_Point(longitude, latitude)
);
$$ LANGUAGE sql;

This function will directly return true if the (lat, lon) point lies within the provided WKT multipolygon, no external libraries required.

Option 2: Fix the Shapely + GEOS Setup for PL/Python UDF #

If you need to use Shapely specifically, follow these steps to ensure GEOS is properly accessible:

1. Prepare Compatible Shapely and GEOS Binaries #

Redshift runs on an Amazon Linux 2-based environment (x86_64 architecture), so you can't use locally compiled GEOS files from other OSes. Instead:

• Launch an Amazon Linux 2 EC2 instance (same architecture as your Redshift cluster).
• Install dependencies and compile GEOS:

  sudo yum install gcc python3-devel
  wget https://download.osgeo.org/geos/geos-3.11.2.tar.bz2
  tar xjf geos-3.11.2.tar.bz2
  cd geos-3.11.2
  ./configure --prefix=/tmp/geos
  make && make install
  

• Install Shapely via pip (it will link against the compiled GEOS):

  pip3 install shapely --target=/tmp/shapely
  

• Copy the GEOS shared libraries into the Shapely directory:

  cp /tmp/geos/lib/libgeos*.so* /tmp/shapely/shapely/
  

2. Package and Upload to S3 #

• Create a zip archive of the Shapely module (including the GEOS libraries):

  cd /tmp/shapely
  zip -r shapely_geos.zip .
  

• Upload this zip file to your S3 bucket, ensuring your Redshift cluster's IAM role has s3:GetObject permissions for the bucket.

3. Create the Redshift Library and UDF #

• First, create the library from S3:

  CREATE LIBRARY shapely_geos
  FROM 's3://your-bucket/path/to/shapely_geos.zip'
  IAM_ROLE 'arn:aws:iam::your-account-id:role/your-redshift-role';
  

• Then, write the UDF with explicit library path configuration to ensure GEOS is found:

  CREATE OR REPLACE FUNCTION is_point_in_multipolygon_shapely(
      id int,
      latitude float,
      longitude float,
      wkt_multipolygon varchar
  )
  RETURNS boolean
  STABLE
  AS $$
  import os
  import sys
  
  # Add the Shapely directory to Python path (Redshift extracts libraries to /tmp)
  sys.path.insert(0, '/tmp')
  # Set LD_LIBRARY_PATH to include the GEOS libraries inside Shapely's folder
  os.environ['LD_LIBRARY_PATH'] = os.path.join(os.environ.get('LD_LIBRARY_PATH', ''), '/tmp/shapely')
  
  from shapely.wkt import loads
  from shapely.geometry import Point
  
  polygon = loads(wkt_multipolygon)
  point = Point(longitude, latitude)
  return polygon.contains(point)
  $$ LANGUAGE plpythonu;
  

4. Verify Permissions and Logs #

• If you still encounter errors, check the svl_udf_log table for detailed messages:

  SELECT * FROM svl_udf_log WHERE udf_name = 'is_point_in_multipolygon_shapely' ORDER BY logtime DESC;
  

• Ensure the zip archive's files have world-readable permissions (644). You can verify this with unzip -l shapely_geos.zip before uploading—avoid files with restrictive permissions like 700.

内容的提问来源于stack exchange，提问作者user8834864