Going from long/lat cords to Geometry or Geography type

Question

When I make a query:

select *, 
       concat(pickup_longitude,' ',pickup_latitude) as pickup_pt, 
       concat(dropoff_longitude,' ',dropoff_latitude) as dropoff_pt

it usually defines the pickup_pt and the dropoff_pt as datatype text from default apparently.

Is there a way to define the column in another datatype?

Perhaps something like:

concat(pickup_longitude,' ',pickup_latitude) as pickup_pt of type geometry

Answer 1

Can you cast? No.

geometry and geography are PostGIS types, and your types are likely float, double, or something like that.

Can you create PostGIS types? Yes.

What you want to do is use ST_MakePoint(long,lat).

SELECT *, 
   ST_MakePoint(pickup_longitude, pickup_latitude) as pickup_pt, 
   ST_MakePoint(dropoff_longitude, dropoff_latitude) as dropoff_pt

If you don't have PostGIS.

1. Get it. Install it. Use it: CREATE EXTENSION postgis;

2. Or, if you can't get or have a good reason for not wanting it, then and only then should you even consider using the native type, point.

   SELECT *, 
      point(pickup_longitude, pickup_latitude) as pickup_pt, 
      point(dropoff_longitude, dropoff_latitude) as dropoff_pt
   

Geometery vs Geography

Geography to geometry conversions are a function to PostGIS. But you can do it with

SELECT ST_MakePoint(long,lat)::geography;

Or, you can use another geometery srid with

SELECT ST_SetSRID( ST_MakePoint(long,lat), srid );