Populating geom using lat/long or x/y

Question

I'm having trouble populating a geometry (geom) column by using already existing columns (the data has an x, y, as well as lat, long columns) the 4 columns are in varchar. I have created the geometry column (set to 4326 same as the table I'll be comparing it to) however all 112 rows are null. I had already tried:

UPDATE gas_stations 
SET geom = ST_SetSRID(ST_MakePoint(longitude, latitude), 4326);

Code above found on StackExchange

Answer 1

This SQL snippet should work on PG 15 + PostGIS 3.3:

--DROP TABLE public.temp;

CREATE TABLE IF NOT EXISTS public.temp
(
    id bigint PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    longitude numeric,
    latitude numeric,
    geom geometry(Point,4326)
);

INSERT INTO public.temp(longitude, latitude) VALUES
  (0,36),(4, 37),(8, 38),(12, 39),(16, 40),(20, 41),(24, 42),(28, 43);


UPDATE public.temp SET geom = ST_SetSRID(ST_MakePoint(longitude, latitude), 4326);

SELECT * FROM public.temp;

=>

So you probably need to convert your varchar column to real numbers, because ST_MakePoint() is only accepting numbers, not text, per the doc:

Synopsis

geometry ST_MakePoint(float x, float y);

geometry ST_MakePoint(float x, float y, float z);

geometry ST_MakePoint(float x, float y, float z, float m);

Hence:

DROP TABLE public.temp;

CREATE TABLE IF NOT EXISTS public.temp
(
    "id" bigint PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    longitude varchar (20),
    latitude varchar (20),
    geom geometry(Point,4326)
);

INSERT INTO public.temp(longitude, latitude) VALUES
  (0,36), (4, 37),(8, 38),(12, 39),(16, 40),(20, 41),(24, 42),(28, 43);


UPDATE public.temp SET geom = ST_SetSRID(ST_MakePoint(longitude, latitude), 4326);

will raise:

ERROR:  function st_makepoint(character varying, character varying) does not exist
LINE 15: UPDATE public.temp SET geom = ST_SetSRID(ST_MakePoint(longit...
                                                  ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
SQL state: 42883
Character: 378

Thus, you need to "type cast" you varchar fields into, e.g. numeric:

DROP TABLE public.temp;

CREATE TABLE IF NOT EXISTS public.temp
(
    "id" bigint PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    longitude varchar (20),
    latitude varchar (20),
    geom geometry(Point,4326)
);

INSERT INTO public.temp(longitude, latitude) VALUES
  (0,36), (4, 37),(8, 38),(12, 39),(16, 40),(20, 41),(24, 42),(28, 43);


UPDATE public.temp SET geom = ST_SetSRID(
  ST_MakePoint(
    longitude::numeric,
    latitude::numeric
  ),
  4326
);

SELECT * FROM public.temp;

=>

Finally, this may be obvious, but you need to have PostGIS installed in your database:

CREATE EXTENSION postgis;
CREATE EXTENSION postgis_topolgoy;

You can check your PostGIS version with:

SELECT postgis_full_version();