5

I am trying to convert a normal table containing coordinates value to a geospatial data table .Which will contain geometry column too .

For additional information I created table as

 create table latLng(
 lat varchar,
 long varchar);

alter table latLng add column geom geometry(Point,4326); UPDATE latLng SET geom = ST_SetSRID(ST_MakePoint(long, lat), 4326);

My Latitude Longitude table

ERROR:  function st_makepoint(character varying, character varying) does not 
exist
LINE 1: UPDATE latLng SET geom = ST_SetSRID(ST_MakePoint(long, lat):...
                                        ^
HINT:  No function matches the given name and argument types. You might need 
to add explicit type casts.
3
  • 1
    What the types of lat and long fields in your latLng table? Looks like Postgresql is saying they are text, not numbers. – BradHards Sep 27 '17 at 10:30
  • Type of lat and long is float – Abhishek Priyankar Sep 27 '17 at 10:40
  • Sorry its string only. How should I convert them to float or numeric ? – Abhishek Priyankar Sep 27 '17 at 10:49
7

ERROR: function st_makepoint(character varying, character varying) does not exist

says it all, your lat and lon are of type varchar.

but the documentation tells:

geometry ST_MakePoint(double precision x, double precision y);

Solution:

Hint: [...] You might need to add explicit type casts.

The Hint tells you what to do: Try to cast as double:

UPDATE latLng SET geom = ST_SetSRID(ST_MakePoint(long::double precision, lat::double precision), 4326) where lat <> '' and lon <> '';

Or update your original table within your query:

alter table latLng alter column lat type double precision using lat::double precision;
alter table latLng alter column long type double precision using long::double precision;

When you receive the following error:

ERROR: invalid input syntax for type double precision: ""

You have empty strings in your table. Try to update empty strings to NULL in your table before altering the column.

update latLng set lat = null WHERE lat = '';
update latLng set long = null WHERE long = '';
9
  • 1
    It giving error ERROR: invalid input syntax for type numeric: "" I tried both of your solutions – Abhishek Priyankar Sep 27 '17 at 10:53
  • 2
    I changed the answer to use double precision instead – pLumo Sep 27 '17 at 10:54
  • That is probably easier than faffing with numerics, when you are going to end up with a geom anyway. – John Powell Sep 27 '17 at 10:56
  • @RoVo Again for double precision ERROR: invalid input syntax for type double precision: "" this error – Abhishek Priyankar Sep 27 '17 at 10:57
  • @JohnPowellakaBarça I am getting error for trying that only – Abhishek Priyankar Sep 27 '17 at 10:59
2

PostGIS extension missing?

I went through the same error, even after converting longitude and latitude from character varying to double precision. The problem was simple, the database had not been set up with PostGIS extensions installed, and so ST_MakePoint didn't exist. For info, if the geom wants to be exported to GIS, ST_SetSRID and ST_transform need to be used to get the right SRID:

ST_transform(ST_SetSRID(ST_MakePoint(a.longitude, a.latitude),4326),3857) as geom From name_of_table as a

1
  • in my case i already had the extension setup, but it for some reason didn't work, after two hours searching i deleted the extension and created it again, then it worked !. – Mohab Khaled Feb 8 at 21:02

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.