Inserting point into PostGIS?

  • I have created one table in my PostGIS nut I cannot insert point.

    What is wrong with my query?

    CREATE TABLE app ( 
      p_id INTEGER PRIMARY KEY
    
    );
    
    SELECT AddGeometryColumn('app','the_geom','4326','POINT',2);
    
    INSERT INTO app(p_id, the_geom) VALUES(2, POINT(-71.060316, 48.432044));
    

    After the last query it shows some error..

    ERROR:  column "the_geom" is of type geometry but expression is of type point
    LINE 1: ...SERT INTO app(p_id, the_geom) VALUES(2, POINT(-71....
                                                                 ^
    HINT:  You will need to rewrite or cast the expression.
    
    
    ********** Error **********
    
    ERROR: column "the_geom" is of type geometry but expression is of type point
    SQL state: 42804
    Hint: You will need to rewrite or cast the expression.
    Character: 53
    

    I already check my PostGIS version.

    SELECT PostGIS_full_version();
    

    I got the following output..

    "POSTGIS="1.5.3" GEOS="3.3.1-CAPI-1.7.1" PROJ="Rel. 4.7.1, 23 September 2009" LIBXML="2.7.3" USE_STATS"
    
  • Mike T

    Mike T Correct answer

    9 years ago

    You are confusing SQL and WKT (well-known text). WKT is a like a geometry language to describe shapes, but it is not SQL, which is a language to query and manipulate databases. When working with WKT in an SQL query, it must be text, and not mixed-in with the SQL.

    Your query works if you properly format the WKT (remove the ",") and set an SRID. For this method, ST_GeomFromText(wkt, srid) works well:

    INSERT INTO app(p_id, the_geom)
    VALUES(2, ST_GeomFromText('POINT(-71.060316 48.432044)', 4326));
    

    If you have columns with numeric longitude/latitude, you can directly make a POINT geometry:

    ST_SetSRID(ST_MakePoint(long, lat), 4326);
    

    Check out the other geometry constructors in the manual.


    For @vik86's request, the_geom can be updated in the table app from numeric columns long and lat using:

    UPDATE app SET
      the_geom = ST_SetSRID(ST_MakePoint(long, lat), 4326);
    

    Could you elaborate on using this feature, "If you have columns with numeric longitude latitude, you can directly make a POINT geometry: ST_SetSRID(ST_MakePoint(long, lat), 4326); " Right now I have a table with lat and long, to use the PostGIS api's I need it in the point format . Hence wanted to know how do i update the point column using this feature. Thanks Vikram

    @Vik86 see updated answer

License under CC-BY-SA with attribution


Content dated before 6/26/2020 9:53 AM