Inserting point into PostGIS?

Question

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"

Answer 1

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);

Answer 2

If you are working with a Java client then my advice is to use the binary types to transfer the data. From memory, I recorded a performance improvement of 10% when I did this change over the ST_AsEWKT method.