查询指定经纬度范围内用户时遭遇PostGIS几何解析错误求助

Got it, let's break down why you're seeing that PG::InternalError: ERROR: parse error - invalid geometry in your Rails query, and fix it step by step.

What's Causing the Error? #

The problem lies in how you're constructing the first ST_GeomFromText call. You wrote:

ST_GeomFromText('POINT(locations.longitude locations.latitude)', 4326)

PostgreSQL treats everything inside those single quotes as a literal string. So it's trying to parse the text locations.longitude as a longitude value—which isn't a valid number—hence the "invalid geometry" error. It doesn't recognize that you want to use the actual values from the locations.longitude and locations.latitude columns here.

Recommended Fix: Use ST_MakePoint (Cleaner & Safer) #

PostGIS has a dedicated function ST_MakePoint that lets you pass column values directly, no messy string parsing needed. Here's your revised Rails code:

organisation.users.joins(:location)
  .where(
    "ST_DWithin(ST_SetSRID(ST_MakePoint(locations.longitude, locations.latitude), 4326), ST_SetSRID(ST_MakePoint(?, ?), 4326), ?)",
    longitude, latitude, distance
  )
  .pluck(:id)

• ST_MakePoint(longitude, latitude) creates a geometry directly from the column values (no string parsing required).
• ST_SetSRID(..., 4326) sets the spatial reference system to WGS84 (the standard for GPS coordinates), matching your original code's intent.

Alternative Fix: Fix String Interpolation for ST_GeomFromText #

If you prefer to stick with ST_GeomFromText, use PostgreSQL's string concatenation operator (||) to insert the column values into the POINT string:

organisation.users.joins(:location)
  .where(
    "ST_DWithin(ST_GeomFromText('POINT(' || locations.longitude || ' ' || locations.latitude || ')', 4326), ST_GeomFromText('POINT(? ?)', 4326), ?)",
    longitude, latitude, distance
  )
  .pluck(:id)

This builds a valid POINT string by combining the column values with the literal POINT( and ) parts. That said, this approach is more error-prone than using ST_MakePoint, so the first fix is better practice.

Quick Extra Check #

Make sure your locations table doesn't have any rows with NULL values for longitude or latitude—those would also create invalid geometry objects and trigger the same error. Add a filter to exclude those:

organisation.users.joins(:location)
  .where.not(locations: { longitude: nil, latitude: nil }) # Add this line
  .where(
    "ST_DWithin(ST_SetSRID(ST_MakePoint(locations.longitude, locations.latitude), 4326), ST_SetSRID(ST_MakePoint(?, ?), 4326), ?)",
    longitude, latitude, distance
  )
  .pluck(:id)

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