2

My geometry columns have SRID of 0. Can I simply convert them to geography as such:

routes.geom::geography <-> pc.geom::geography as distance

in order to get "distance" in metres?

P.S - "routes.geom" is a linestring column, and "pc.geom" is a point column.

here is a snippet of how it is used, (extracted from my main query):

    FROM point_center as pc
    
    CROSS JOIN LATERAL (
      SELECT routes.id_num, routes.geom::geography <-> pc.geom::geography AS dist 

1 Answer 1

3

A GEOGRAPHY type instantiation assumes EPSG:4326 by default, so you can simply cast your GEOMETRY types, and even get correct results if they actually are referenced in EPSG:4326.


However, it is highly advisable to add the correct SRID to the GEOMETRY column and values:

ALTER TABLE <table>
  ALTER COLUMN geom TYPE GEOMETRY(<TYPE>, <SRID>)
    USING ST_SetSRID(geom, <SRID>)
;

-- VACUUM ANALYZE <table>;

Note:

A CAST to GEOGRAPHY will render the respective statement unable to utilize a spatial index on the GEOMETRY column; in order to benefit from an index lookup where possible, add a functional index on the actual CAST:

CREATE INDEX ON <table> USING GIST ( (geom::GEOGRAPHY) );

-- VACUUM ANALYZE <table>;
;
1
  • Comments are not for extended discussion; this conversation has been moved to chat.
    – Ian Turton
    Sep 20, 2022 at 14:27

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.