1

The underlying geometries are SRID 4326. I want to confirm whether "pma.dist" in the select clause would be a distance in meters. Here is my code:

SELECT 
       clusters.areaid,
       clusters.uc_name,
       clusters.missed_area_cluster_id,      
       pma."ID" AS pma_ID,
       pma.dist
FROM inter clusters
CROSS JOIN LATERAL (
  SELECT pma."centerPoint", pma."ID", pma."centerPoint"::geography <-> clusters.cluster_center::geography AS dist
  FROM potential_missed_areas AS pma
  where pma.status = 'approved'
  ORDER BY dist
  LIMIT 1
) pma;

1 Answer 1

3

Yes, whenever the GEOGRAPHY data type is used for geometric measurements, the base unit of their results, as well as their respective function arguments (e.g. for ST_DWithin), is meter!

This is true even for geodetic reference systems that use e.g. feet as their primary unit.

When called with GEOGRAPHY types as arguments, the <-> operator calculates distances on a spherical model - for spheroidal calculations you would need to add a call to ST_Distance:

SELECT 
  clusters.areaid,
  clusters.uc_name,
  clusters.missed_area_cluster_id,      
  pma."ID" AS pma_ID,
  pma.dist
FROM
  inter AS clusters
  CROSS JOIN LATERAL (
    SELECT
      pma."
      ST_Distance(
        pma."centerPoint"::GEOGRAPHY,
        clusters.cluster_center::GEOGRAPHY,
        TRUE -- optional, defaults to TRUE
      ) AS dist
    FROM
      potential_missed_areas AS pma
    WHERE
      pma.status = 'approved'
    ORDER BY
      pma."centerPoint"::GEOGRAPHY <-> clusters.cluster_center::GEOGRAPHY
    LIMIT
      1
  ) AS pma
;

Note that the KNN search is a very costly Cross Product operation if not covered by an index on the CAST to GEOGRAPHY, i.e.:

CREATE INDEX
  potential_missed_areas__geog__func_idx
  ON
    potential_missed_areas
  USING
    GIST( CAST("centerPoint" AS GEOGRAPHY) ) -- or GIST( ("centerPoint"::GEOGRAPHY) )
;
5
  • thanks! do both arguments across the <-> need to be indexed?
    – analyst92
    Commented Aug 31, 2023 at 10:10
  • 3
    The index driven <-> KNN ordering can never use two indexes.
    – geozelot
    Commented Aug 31, 2023 at 10:38
  • 3
    If having indexes on two columns in a join operation may be beneficial depends on the query. Generally it would be useless and ineffective to use an index on the left join item as the driver of a query (i.e. looping over all rows in the left join item and ask for an index lookup on the right join item each). However, if you were to add a (spatial) filter on the left join item in the scenario above, the planner may first fetch candidates from a (spatial) index on the left join item to only use this subset to drive the query and subsequently ask for index lookups on the right join item.
    – geozelot
    Commented Aug 31, 2023 at 10:39
  • 3
    So: the LATERAL sub-query above cannot benefit from a spatial index on inter.cluster_center; the index driven <-> ordering never can. If you were to add a spatial filter on inter.cluster_center in the outer WHERE clause, e.g. an ST_Intersects, then an index on inter.cluster_center may be used.
    – geozelot
    Commented Aug 31, 2023 at 10:40
  • 3
    Addendum: if you were to add a spatial filter on potential_missed_areas."centerPoint" inside the sub-query, the planner will decide based on table statistics if to use the index for the filter or the <-> ordering - never for both; an index on the same table and column can only ever be used for multiple lookups in logical set operations (i.e. AND|OR'ed value filters of the same column).
    – geozelot
    Commented Aug 31, 2023 at 11:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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