4

I have a geometry(Point,4326) column on my table:

CREATE TABLE foo ( geom geometry(Point,4326) );

I want to insert into it. Currently' I am running

INSERT INTO foo (geom) VALUES (
  ST_GeomFromText('POINT(latitude longitude)', 4326))
);

To save a geometry value. I think ST_GeomFromText('POINT(latitude longitude)', 4326) is wrong method, I tried from here

2

2 Answers 2

3

This is the right method - ST_GeomFromText('POINT(' || longitude || ' ' ||latitude || ')',4326)

2
  • No, that's not. Jun 3, 2018 at 0:29
  • 2
    Evan Carroll, there are different ways of creating point objects, this is the first, second - SN Tiwari confused places with latitude and longitude values, it was necessary to pay attention to this, with respect Jun 3, 2018 at 10:19
3

You don't have to do anything for that. Just use ST_MakePoint and cast.

ST_SetSRID(ST_MakePoint(x,y),4326);

In an INSERT that looks like this,

-- looks like this.
INSERT INTO foo (geom) VALUES
  (ST_SetSRID(ST_MakePoint(1,2),4326));

Geography

The default SRID for geography is 4326. If you're new, I would suggest using geography instead of geometry.

ALTER TABLE foo drop column geom,
  ADD COLUMN geog geography(point,4326);

For that, just use a cast to geography or allow the implicit cast.

  • Implicit cast and srid

    ST_MakePoint(x,y)
    
  • Explicit cast, implicit srid

    ST_MakePoint(x,y)::geography
    
  • Explicit cast and srid

    ST_SetSRID( ST_MakePoint(3,4), 4326 )::geography
    

Which looks like this,

INSERT INTO foo (geog) VALUES
  -- implicit cast and SRID
  ( ST_MakePoint(1,2) ),

  -- explicit cast, implicit SRID
  ( ST_MakePoint(1,2)::geography ),

   -- explicit cast and SRID
  ( ST_SetSRID( ST_MakePoint(3,4), 4326 )::geography );

Converting to text and then forcing PostgreSQL to parse the text with ST_GeomFromText or ST_GeogFromText is silly and slow.

0

Not the answer you're looking for? Browse other questions tagged or ask your own question.