database - 如何将 POINTS(LANG, LAT) 存储到 PostGIS 中的几何类型列中？

Question

我在 PostGIS 中创建了一个表 itapp_cities 来存储城市的数据。我添加了一个数据类型为 geometry 的 location 列来存储城市的 longitude 和 latitude 。当我运行以下 INSERT 查询时，出现如下所示的错误。

INSERT 查询:

INSERT INTO itapp_cities(city_id, city_name, city_code, state_id, location) 
  VALUES (DEFAULT,'Ada', 'ada-ok',37,POINT(34.774531000000003, -96.678344899999999)); 

表定义:

CREATE TABLE itapp_cities
(
  city_id bigserial NOT NULL,
  city_name character varying(100) NOT NULL,
  city_code character varying(5) NOT NULL DEFAULT ''::character varying,
  state_id bigint NOT NULL,
  location geometry,
  CONSTRAINT itapp_cities_pkey PRIMARY KEY (city_id),
  CONSTRAINT fk_states FOREIGN KEY (city_id)
      REFERENCES itapp_states (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE
)

错误:

ERROR:  column "location" is of type geometry but expression is of type point
LINE 2:   VALUES (DEFAULT,'Ada', 'ada-ok',37,POINT(34.77453100000000...
                                             ^
HINT:  You will need to rewrite or cast the expression.
********** Error **********

ERROR: column "location" is of type geometry but expression is of type point
SQL state: 42804

如何在此列中存储点值？我是 PostGIS 的新手所以请原谅我这个愚蠢的问题

Answer 1

您可以使用函数 ST_MakePoint()并设置 SRID (Spatial Reference System Identifier)与 ST_SetSRID() :

SELECT ST_SetSRID(ST_MakePoint(longitude, latitude),4326)

或者当您输入文字值时，将字符串表示形式提供给 ST_GeomFromText() :

SELECT ST_GeomFromText('SRID=4326;POINT(34.774531 -96.6783449)')

dba.SE 上的相关答案以及更多详细信息和链接:

• Order by distance