Postgres columns of latitude and longitude from varchar to numeric












1















I have a table with latitude and longitude columns and they are in varchar. I have to convert them to numeric because that is how the fields are in the master table.



Is there a way to change all the values in the columns at one time from varchar to numeric in PostgreSQL?



I am struggling because since they are lat/long values they vary in length, have decimals and the longitude is negative.



Table Name



Latitude      | Longitude
+-----------------------------+

35.0528620000 | -119.375136000
+-------------|---------------+


and they go on like this. I have tried ALTER TABLE, but that just gives me an an error, CAN NOT BE CAST
and I tried http://www.postgresql.org/docs/9.3/static/functions-formatting.html
to_char



EDIT: I was able to get the fields almost to the same length. Latitude is not 13 char and Longitude is 14 including the negative sign.



EDIT: The alter table error in Navicat:



[Err] ERROR:  syntax error at or near "ALTER"
LINE 1: EXPLAIN ALTER TABLE "allwells_CA_copy"


In Postgres:



ERROR: syntax error at or near "ALTER" LINE 1: ..., VERBOSE off, COSTS on, BUFFERS off, TIMING off )ALTER TABL... ^ ********** Error ********** ERROR: syntax error at or near "ALTER" SQL state: 42601 Character: 71


I've been switching between PG Admin and Navicat in an attempt to make the change.










share|improve this question




















  • 1





    Always include the exact text of any error message and where possible the SQL that produced it.

    – Craig Ringer
    Oct 7 '14 at 13:45











  • Here is the original ALTER table error. I tried so many things yesterday with so many errors. I made an edit above

    – T.J.
    Oct 7 '14 at 14:07











  • and the SQL that produced that error?

    – Craig Ringer
    Oct 7 '14 at 14:22
















1















I have a table with latitude and longitude columns and they are in varchar. I have to convert them to numeric because that is how the fields are in the master table.



Is there a way to change all the values in the columns at one time from varchar to numeric in PostgreSQL?



I am struggling because since they are lat/long values they vary in length, have decimals and the longitude is negative.



Table Name



Latitude      | Longitude
+-----------------------------+

35.0528620000 | -119.375136000
+-------------|---------------+


and they go on like this. I have tried ALTER TABLE, but that just gives me an an error, CAN NOT BE CAST
and I tried http://www.postgresql.org/docs/9.3/static/functions-formatting.html
to_char



EDIT: I was able to get the fields almost to the same length. Latitude is not 13 char and Longitude is 14 including the negative sign.



EDIT: The alter table error in Navicat:



[Err] ERROR:  syntax error at or near "ALTER"
LINE 1: EXPLAIN ALTER TABLE "allwells_CA_copy"


In Postgres:



ERROR: syntax error at or near "ALTER" LINE 1: ..., VERBOSE off, COSTS on, BUFFERS off, TIMING off )ALTER TABL... ^ ********** Error ********** ERROR: syntax error at or near "ALTER" SQL state: 42601 Character: 71


I've been switching between PG Admin and Navicat in an attempt to make the change.










share|improve this question




















  • 1





    Always include the exact text of any error message and where possible the SQL that produced it.

    – Craig Ringer
    Oct 7 '14 at 13:45











  • Here is the original ALTER table error. I tried so many things yesterday with so many errors. I made an edit above

    – T.J.
    Oct 7 '14 at 14:07











  • and the SQL that produced that error?

    – Craig Ringer
    Oct 7 '14 at 14:22














1












1








1








I have a table with latitude and longitude columns and they are in varchar. I have to convert them to numeric because that is how the fields are in the master table.



Is there a way to change all the values in the columns at one time from varchar to numeric in PostgreSQL?



I am struggling because since they are lat/long values they vary in length, have decimals and the longitude is negative.



Table Name



Latitude      | Longitude
+-----------------------------+

35.0528620000 | -119.375136000
+-------------|---------------+


and they go on like this. I have tried ALTER TABLE, but that just gives me an an error, CAN NOT BE CAST
and I tried http://www.postgresql.org/docs/9.3/static/functions-formatting.html
to_char



EDIT: I was able to get the fields almost to the same length. Latitude is not 13 char and Longitude is 14 including the negative sign.



EDIT: The alter table error in Navicat:



[Err] ERROR:  syntax error at or near "ALTER"
LINE 1: EXPLAIN ALTER TABLE "allwells_CA_copy"


In Postgres:



ERROR: syntax error at or near "ALTER" LINE 1: ..., VERBOSE off, COSTS on, BUFFERS off, TIMING off )ALTER TABL... ^ ********** Error ********** ERROR: syntax error at or near "ALTER" SQL state: 42601 Character: 71


I've been switching between PG Admin and Navicat in an attempt to make the change.










share|improve this question
















I have a table with latitude and longitude columns and they are in varchar. I have to convert them to numeric because that is how the fields are in the master table.



Is there a way to change all the values in the columns at one time from varchar to numeric in PostgreSQL?



I am struggling because since they are lat/long values they vary in length, have decimals and the longitude is negative.



Table Name



Latitude      | Longitude
+-----------------------------+

35.0528620000 | -119.375136000
+-------------|---------------+


and they go on like this. I have tried ALTER TABLE, but that just gives me an an error, CAN NOT BE CAST
and I tried http://www.postgresql.org/docs/9.3/static/functions-formatting.html
to_char



EDIT: I was able to get the fields almost to the same length. Latitude is not 13 char and Longitude is 14 including the negative sign.



EDIT: The alter table error in Navicat:



[Err] ERROR:  syntax error at or near "ALTER"
LINE 1: EXPLAIN ALTER TABLE "allwells_CA_copy"


In Postgres:



ERROR: syntax error at or near "ALTER" LINE 1: ..., VERBOSE off, COSTS on, BUFFERS off, TIMING off )ALTER TABL... ^ ********** Error ********** ERROR: syntax error at or near "ALTER" SQL state: 42601 Character: 71


I've been switching between PG Admin and Navicat in an attempt to make the change.







postgresql-9.3 type-conversion






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Oct 7 '14 at 14:15







T.J.

















asked Oct 7 '14 at 13:39









T.J.T.J.

1085




1085








  • 1





    Always include the exact text of any error message and where possible the SQL that produced it.

    – Craig Ringer
    Oct 7 '14 at 13:45











  • Here is the original ALTER table error. I tried so many things yesterday with so many errors. I made an edit above

    – T.J.
    Oct 7 '14 at 14:07











  • and the SQL that produced that error?

    – Craig Ringer
    Oct 7 '14 at 14:22














  • 1





    Always include the exact text of any error message and where possible the SQL that produced it.

    – Craig Ringer
    Oct 7 '14 at 13:45











  • Here is the original ALTER table error. I tried so many things yesterday with so many errors. I made an edit above

    – T.J.
    Oct 7 '14 at 14:07











  • and the SQL that produced that error?

    – Craig Ringer
    Oct 7 '14 at 14:22








1




1





Always include the exact text of any error message and where possible the SQL that produced it.

– Craig Ringer
Oct 7 '14 at 13:45





Always include the exact text of any error message and where possible the SQL that produced it.

– Craig Ringer
Oct 7 '14 at 13:45













Here is the original ALTER table error. I tried so many things yesterday with so many errors. I made an edit above

– T.J.
Oct 7 '14 at 14:07





Here is the original ALTER table error. I tried so many things yesterday with so many errors. I made an edit above

– T.J.
Oct 7 '14 at 14:07













and the SQL that produced that error?

– Craig Ringer
Oct 7 '14 at 14:22





and the SQL that produced that error?

– Craig Ringer
Oct 7 '14 at 14:22










2 Answers
2






active

oldest

votes


















2














You need the USING clause to ALTER TABLE ... TYPE ..., e.g.:



ALTER TABLE mytable
ALTER COLUMN "Longitude" TYPE NUMERIC(14, 11)
USING ("Longditude"::NUMERIC(14,11)),
ALTER COLUMN "Latitude" TYPE NUMERIC(14, 11)
USING ("Latitude"::NUMERIC(14,11));


assuming you want a numeric with precision 14 and scale 11 and your columns really do have an upper case first letter.



For more details see the manual on ALTER TABLE.



after edit:



You seem to be using a broken client that's trying to prepend an EXPLAIN to the ALTER TABLE. You can't EXPLAIN an ALTER TABLE statement.






share|improve this answer


























  • I wrote something similar to this but I keep getting an error right at the 'ALTER' line 1. With your code the error is: ERROR: syntax error at or near "ALTER" LINE 1: ..., VERBOSE off, COSTS on, BUFFERS off, TIMING off )ALTER TABL... ^ ********** Error ********** ERROR: syntax error at or near "ALTER" SQL state: 42601 Character: 71

    – T.J.
    Oct 7 '14 at 14:11













  • You appear to be attempting to EXPLAIN ANALYZE an ALTER TABLE. You can't EXPLAIN an ALTER TABLE. Just run it without the EXPLAIN (...).

    – Craig Ringer
    Oct 7 '14 at 14:21





















0














GIS



You shouldn't be storing lat/long as numeric, nor varchar. Use PostGIS and store them as a GEOGRAPHY. This allows you to do intersection tests on an index, find all points within X-range, aggregate into lines, or polygons -- and a lot more.



CREATE EXTENSION postgis;
BEGIN;
ALTER TABLE foo ADD COLUMN geog geography;
UPDATE foo SET geog = ST_MakePoint(long, lat);
CREATE INDEX on foo USING gist ( geog );
ALTER TABLE foo DROP COLUMN long, DROP COLUMN lat;
COMMIT;





share|improve this answer























    Your Answer








    StackExchange.ready(function() {
    var channelOptions = {
    tags: "".split(" "),
    id: "182"
    };
    initTagRenderer("".split(" "), "".split(" "), channelOptions);

    StackExchange.using("externalEditor", function() {
    // Have to fire editor after snippets, if snippets enabled
    if (StackExchange.settings.snippets.snippetsEnabled) {
    StackExchange.using("snippets", function() {
    createEditor();
    });
    }
    else {
    createEditor();
    }
    });

    function createEditor() {
    StackExchange.prepareEditor({
    heartbeatType: 'answer',
    autoActivateHeartbeat: false,
    convertImagesToLinks: false,
    noModals: true,
    showLowRepImageUploadWarning: true,
    reputationToPostImages: null,
    bindNavPrevention: true,
    postfix: "",
    imageUploader: {
    brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
    contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
    allowUrls: true
    },
    onDemand: true,
    discardSelector: ".discard-answer"
    ,immediatelyShowMarkdownHelp:true
    });


    }
    });














    draft saved

    draft discarded


















    StackExchange.ready(
    function () {
    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f78580%2fpostgres-columns-of-latitude-and-longitude-from-varchar-to-numeric%23new-answer', 'question_page');
    }
    );

    Post as a guest















    Required, but never shown

























    2 Answers
    2






    active

    oldest

    votes








    2 Answers
    2






    active

    oldest

    votes









    active

    oldest

    votes






    active

    oldest

    votes









    2














    You need the USING clause to ALTER TABLE ... TYPE ..., e.g.:



    ALTER TABLE mytable
    ALTER COLUMN "Longitude" TYPE NUMERIC(14, 11)
    USING ("Longditude"::NUMERIC(14,11)),
    ALTER COLUMN "Latitude" TYPE NUMERIC(14, 11)
    USING ("Latitude"::NUMERIC(14,11));


    assuming you want a numeric with precision 14 and scale 11 and your columns really do have an upper case first letter.



    For more details see the manual on ALTER TABLE.



    after edit:



    You seem to be using a broken client that's trying to prepend an EXPLAIN to the ALTER TABLE. You can't EXPLAIN an ALTER TABLE statement.






    share|improve this answer


























    • I wrote something similar to this but I keep getting an error right at the 'ALTER' line 1. With your code the error is: ERROR: syntax error at or near "ALTER" LINE 1: ..., VERBOSE off, COSTS on, BUFFERS off, TIMING off )ALTER TABL... ^ ********** Error ********** ERROR: syntax error at or near "ALTER" SQL state: 42601 Character: 71

      – T.J.
      Oct 7 '14 at 14:11













    • You appear to be attempting to EXPLAIN ANALYZE an ALTER TABLE. You can't EXPLAIN an ALTER TABLE. Just run it without the EXPLAIN (...).

      – Craig Ringer
      Oct 7 '14 at 14:21


















    2














    You need the USING clause to ALTER TABLE ... TYPE ..., e.g.:



    ALTER TABLE mytable
    ALTER COLUMN "Longitude" TYPE NUMERIC(14, 11)
    USING ("Longditude"::NUMERIC(14,11)),
    ALTER COLUMN "Latitude" TYPE NUMERIC(14, 11)
    USING ("Latitude"::NUMERIC(14,11));


    assuming you want a numeric with precision 14 and scale 11 and your columns really do have an upper case first letter.



    For more details see the manual on ALTER TABLE.



    after edit:



    You seem to be using a broken client that's trying to prepend an EXPLAIN to the ALTER TABLE. You can't EXPLAIN an ALTER TABLE statement.






    share|improve this answer


























    • I wrote something similar to this but I keep getting an error right at the 'ALTER' line 1. With your code the error is: ERROR: syntax error at or near "ALTER" LINE 1: ..., VERBOSE off, COSTS on, BUFFERS off, TIMING off )ALTER TABL... ^ ********** Error ********** ERROR: syntax error at or near "ALTER" SQL state: 42601 Character: 71

      – T.J.
      Oct 7 '14 at 14:11













    • You appear to be attempting to EXPLAIN ANALYZE an ALTER TABLE. You can't EXPLAIN an ALTER TABLE. Just run it without the EXPLAIN (...).

      – Craig Ringer
      Oct 7 '14 at 14:21
















    2












    2








    2







    You need the USING clause to ALTER TABLE ... TYPE ..., e.g.:



    ALTER TABLE mytable
    ALTER COLUMN "Longitude" TYPE NUMERIC(14, 11)
    USING ("Longditude"::NUMERIC(14,11)),
    ALTER COLUMN "Latitude" TYPE NUMERIC(14, 11)
    USING ("Latitude"::NUMERIC(14,11));


    assuming you want a numeric with precision 14 and scale 11 and your columns really do have an upper case first letter.



    For more details see the manual on ALTER TABLE.



    after edit:



    You seem to be using a broken client that's trying to prepend an EXPLAIN to the ALTER TABLE. You can't EXPLAIN an ALTER TABLE statement.






    share|improve this answer















    You need the USING clause to ALTER TABLE ... TYPE ..., e.g.:



    ALTER TABLE mytable
    ALTER COLUMN "Longitude" TYPE NUMERIC(14, 11)
    USING ("Longditude"::NUMERIC(14,11)),
    ALTER COLUMN "Latitude" TYPE NUMERIC(14, 11)
    USING ("Latitude"::NUMERIC(14,11));


    assuming you want a numeric with precision 14 and scale 11 and your columns really do have an upper case first letter.



    For more details see the manual on ALTER TABLE.



    after edit:



    You seem to be using a broken client that's trying to prepend an EXPLAIN to the ALTER TABLE. You can't EXPLAIN an ALTER TABLE statement.







    share|improve this answer














    share|improve this answer



    share|improve this answer








    edited Oct 7 '14 at 14:23

























    answered Oct 7 '14 at 13:47









    Craig RingerCraig Ringer

    39.7k190132




    39.7k190132













    • I wrote something similar to this but I keep getting an error right at the 'ALTER' line 1. With your code the error is: ERROR: syntax error at or near "ALTER" LINE 1: ..., VERBOSE off, COSTS on, BUFFERS off, TIMING off )ALTER TABL... ^ ********** Error ********** ERROR: syntax error at or near "ALTER" SQL state: 42601 Character: 71

      – T.J.
      Oct 7 '14 at 14:11













    • You appear to be attempting to EXPLAIN ANALYZE an ALTER TABLE. You can't EXPLAIN an ALTER TABLE. Just run it without the EXPLAIN (...).

      – Craig Ringer
      Oct 7 '14 at 14:21





















    • I wrote something similar to this but I keep getting an error right at the 'ALTER' line 1. With your code the error is: ERROR: syntax error at or near "ALTER" LINE 1: ..., VERBOSE off, COSTS on, BUFFERS off, TIMING off )ALTER TABL... ^ ********** Error ********** ERROR: syntax error at or near "ALTER" SQL state: 42601 Character: 71

      – T.J.
      Oct 7 '14 at 14:11













    • You appear to be attempting to EXPLAIN ANALYZE an ALTER TABLE. You can't EXPLAIN an ALTER TABLE. Just run it without the EXPLAIN (...).

      – Craig Ringer
      Oct 7 '14 at 14:21



















    I wrote something similar to this but I keep getting an error right at the 'ALTER' line 1. With your code the error is: ERROR: syntax error at or near "ALTER" LINE 1: ..., VERBOSE off, COSTS on, BUFFERS off, TIMING off )ALTER TABL... ^ ********** Error ********** ERROR: syntax error at or near "ALTER" SQL state: 42601 Character: 71

    – T.J.
    Oct 7 '14 at 14:11







    I wrote something similar to this but I keep getting an error right at the 'ALTER' line 1. With your code the error is: ERROR: syntax error at or near "ALTER" LINE 1: ..., VERBOSE off, COSTS on, BUFFERS off, TIMING off )ALTER TABL... ^ ********** Error ********** ERROR: syntax error at or near "ALTER" SQL state: 42601 Character: 71

    – T.J.
    Oct 7 '14 at 14:11















    You appear to be attempting to EXPLAIN ANALYZE an ALTER TABLE. You can't EXPLAIN an ALTER TABLE. Just run it without the EXPLAIN (...).

    – Craig Ringer
    Oct 7 '14 at 14:21







    You appear to be attempting to EXPLAIN ANALYZE an ALTER TABLE. You can't EXPLAIN an ALTER TABLE. Just run it without the EXPLAIN (...).

    – Craig Ringer
    Oct 7 '14 at 14:21















    0














    GIS



    You shouldn't be storing lat/long as numeric, nor varchar. Use PostGIS and store them as a GEOGRAPHY. This allows you to do intersection tests on an index, find all points within X-range, aggregate into lines, or polygons -- and a lot more.



    CREATE EXTENSION postgis;
    BEGIN;
    ALTER TABLE foo ADD COLUMN geog geography;
    UPDATE foo SET geog = ST_MakePoint(long, lat);
    CREATE INDEX on foo USING gist ( geog );
    ALTER TABLE foo DROP COLUMN long, DROP COLUMN lat;
    COMMIT;





    share|improve this answer




























      0














      GIS



      You shouldn't be storing lat/long as numeric, nor varchar. Use PostGIS and store them as a GEOGRAPHY. This allows you to do intersection tests on an index, find all points within X-range, aggregate into lines, or polygons -- and a lot more.



      CREATE EXTENSION postgis;
      BEGIN;
      ALTER TABLE foo ADD COLUMN geog geography;
      UPDATE foo SET geog = ST_MakePoint(long, lat);
      CREATE INDEX on foo USING gist ( geog );
      ALTER TABLE foo DROP COLUMN long, DROP COLUMN lat;
      COMMIT;





      share|improve this answer


























        0












        0








        0







        GIS



        You shouldn't be storing lat/long as numeric, nor varchar. Use PostGIS and store them as a GEOGRAPHY. This allows you to do intersection tests on an index, find all points within X-range, aggregate into lines, or polygons -- and a lot more.



        CREATE EXTENSION postgis;
        BEGIN;
        ALTER TABLE foo ADD COLUMN geog geography;
        UPDATE foo SET geog = ST_MakePoint(long, lat);
        CREATE INDEX on foo USING gist ( geog );
        ALTER TABLE foo DROP COLUMN long, DROP COLUMN lat;
        COMMIT;





        share|improve this answer













        GIS



        You shouldn't be storing lat/long as numeric, nor varchar. Use PostGIS and store them as a GEOGRAPHY. This allows you to do intersection tests on an index, find all points within X-range, aggregate into lines, or polygons -- and a lot more.



        CREATE EXTENSION postgis;
        BEGIN;
        ALTER TABLE foo ADD COLUMN geog geography;
        UPDATE foo SET geog = ST_MakePoint(long, lat);
        CREATE INDEX on foo USING gist ( geog );
        ALTER TABLE foo DROP COLUMN long, DROP COLUMN lat;
        COMMIT;






        share|improve this answer












        share|improve this answer



        share|improve this answer










        answered 22 mins ago









        Evan CarrollEvan Carroll

        31.6k966214




        31.6k966214






























            draft saved

            draft discarded




















































            Thanks for contributing an answer to Database Administrators Stack Exchange!


            • Please be sure to answer the question. Provide details and share your research!

            But avoid



            • Asking for help, clarification, or responding to other answers.

            • Making statements based on opinion; back them up with references or personal experience.


            To learn more, see our tips on writing great answers.




            draft saved


            draft discarded














            StackExchange.ready(
            function () {
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f78580%2fpostgres-columns-of-latitude-and-longitude-from-varchar-to-numeric%23new-answer', 'question_page');
            }
            );

            Post as a guest















            Required, but never shown





















































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown

































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown







            Popular posts from this blog

            Liste der Baudenkmale in Friedland (Mecklenburg)

            Single-Malt-Whisky

            Czorneboh