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

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).

   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 );

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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