Inserting point into PostGIS?

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

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.

Tags:

Postgis