Invalid input syntax for type double precision: “null” when finding nearest city with longitude/latitude

Question

I have a table in a PostgreSQL database.

Table Structure:

CREATE TABLE "public"."citydescription" (
    "srno" int4 DEFAULT nextval('newcitydescription_srno_seq'::regclass) NOT NULL,
    "cityname" varchar(40),
    "country" varchar(40),
    "airportcode" varchar(4),
    "description" text,
    "code" varchar,
    "parentcity" int4,
    "type" varchar DEFAULT 'city'::character varying,
    "title" varchar,
    "priority" varchar,
    "isactive" bool DEFAULT true,
    "sequence" int4,
    "latitude" varchar,
    "longitude" varchar,
    "score" float8,
    "state_province" varchar(2),
    "population" text,
    "isnew" bool,
    "has_banner" bool DEFAULT false,
    "hreflang" bool,
    "au_href" varchar,
    CONSTRAINT "newcitydescription_pkey" PRIMARY KEY ("srno")
)
WITH (OIDS=FALSE);

When I run this query:

SELECT * FROM (
    SELECT
        *,
        (
            3959 * acos(
                cos( radians(6.414478) ) *
                cos( radians( CAST(latitude AS FLOAT) ) ) *
                cos( radians( CAST(longitude AS FLOAT) ) - radians(12.466646) ) +
                sin( radians(6.414478) ) *
                sin( radians( CAST(latitude AS FLOAT) ) )
            )
        ) AS distance 
        FROM citydescription
    ) al
WHERE distance < 5
ORDER BY distance
LIMIT 20;

It gives this error:

[Err] ERROR: invalid input syntax for type double precision: "null"

Note: There is no option to alter column type as this will lead to lots of code changes

Sasha Kondrashov Sasha Kondrashov · Accepted Answer · 2018-07-10T16:37:16

One of your rows has the string 'null' as a value for either longitude or latitude. You can fix this either by correcting the data:

UPDATE citydescription SET latitude=NULL WHERE latitude='null';
UPDATE citydescription SET longitude=NULL WHERE longitude='null';

or by adding a WHERE clause to your query:

SELECT * FROM (
    SELECT
        *,
        (
            3959 * acos(
                cos( radians(6.414478) ) *
                cos( radians( CAST(latitude AS FLOAT) ) ) *
                cos( radians( CAST(longitude AS FLOAT) ) - radians(12.466646) ) +
                sin( radians(6.414478) ) *
                sin( radians( CAST(latitude AS FLOAT) ) )
            )
        ) AS distance
        FROM citydescription
        WHERE
            latitude != 'null' AND
            longitude != 'null'
    ) al
WHERE distance < 5
ORDER BY distance
LIMIT 20;

PS It is strongly recommended to use PostGIS for operations like this. I know you said you can't change the column type, but using a varchar column to store floats is begging for trouble, and if you expect to be maintaining this project for any amount of time, you should change the column type, and when you do so, switching to PostGIS can be a part of that transition. Your queries will also be much cleaner as a result.

Invalid input syntax for type double precision: “null” when finding nearest city with longitude/latitude

1 Answers