PostGIS的一些笔记

数据库里面geom保存的数据格式

 

转成wkt格式

查询语句

SELECT st_astext(geom) FROM table_name

结果：POINT(112.545539 28.4678)

ST_GeomFromText：是将wkt类型的数据转成geom格式 SELECT ST_GeomFromText('POINT(-122.34900 47.65100)',4490)

ST_Transform(geom,4326)：坐标系转换

 

查询中心点 1000米内的数据：

SELECT id,s_name FROM mis.table_a WHERE ST_DWithin(ST_GeomFromText('POINT(114.237906 22.8714)',4326),geom, 1000)

 

插入点数据：

insert into mis.steel_company(id,geom)VALUES(1959, ST_GeomFromText('POINT(120.11 30.32)',4326))

 

jpa保存空间数据

@ColumnTransformer(read = "st_astext(shape)", write = "st_geometry(?,4490)")
String shape;
shape是wkt格式的数据

查询坐标系信息
SELECT st_srid(geom) FROM polyline WHERE geom is not null ORDER BY id asc limit 1

 

创建空间表

CREATE TABLE group_multi_polygon (id integer PRIMARY KEY,geom geometry(MultiPolygon,4326),area_name character varying)

CREATE TABLE group_polygon (id integer PRIMARY KEY,geom geometry(Polygon,4326),area_name character varying)

CREATE TABLE group_multi_point (id integer PRIMARY KEY,geom geometry(MultiPoint,4326),area_name character varying)

CREATE TABLE group_point (id integer PRIMARY KEY,geom geometry(Point,4326),area_name character varying)

CREATE TABLE group_line (id integer PRIMARY KEY,geom geometry(LineString,4326),area_name character varying)

CREATE TABLE group_multi_line (id integer PRIMARY KEY,geom geometry(MultiLineString,4326),area_name character varying)

 

如果有Z坐标 则 MultiLineString 改成 MultiLineStringZ