postgresql+postgis基础语法
1.创建空间表
CREATE TABLE public.my_polygon
(
id bigserial,
name character(100) COLLATE pg_catalog.”default” NOT NULL,
mypolygon geometry NOT NULL,
salary integer,
CONSTRAINT my_polygon_pkey PRIMARY KEY (id)
)
空间字段：mypolygon； 数据类型为geometry；
bigserial： 主键自增类型；
2.psql表操作
//添加点
1) insert into my_polygon values (1,’点’,’POINT(0 0)’);
//添加线,多边形也是线点存储
2) insert into my_polygon values (2,’线条’,’LINESTRING(0 0,1 1,1 2)’);
//查看所有表记录
3)select * from 表名;
select * from my_polygon; //数据不进行处理，空间字段展示增强的16进制编码
select id,name,ST_AsText(mypolygon) from my_polygon; //空间几何字段处理展示文本类型，POINT(1 1)等
//更新数据
4)UPDATE table_name SET column1 = value1, column2 = value2…., columnN = valueN WHERE [condition];
update my_polygon SET name = ‘点’,mypolygon = ‘POINT(1 1)’ where id = 1
update my_polygon SET name = ‘点’,mypolygon = ST_AsText(‘POINT(1 1)’) where id = 1；
update my_polygon SET name = ‘点’,mypolygon = ST_GeomFromText(‘POINT(1 1)’) where id = 1
注释：效果一样，最终表数据16进制展示；
//删除记录
5)delete from my_polygon where id = 3
//排序查询
6)SELECT column-list FROM table_name [WHERE condition] [ORDER BY column1, column2, .. columnN] [ASC | DESC];
select id,name,ST_AsText(mypolygon) from my_polygon order by name;
//分组查询
7)SELECT column-list FROM table_name WHERE [conditions ] GROUP BY column1, column2….columnN
select name,sum(salary) from my_polygon group by name; //正确
select id,name,sum(salary) from my_polygon group by id,name //正确，salary是添加的integer字段
select id,name from my_polygon group by name; //错误，当查询字段含有id，group by后也必须添加id;
select id,name from my_polygon group by id,name; //正确
select name from my_polygon group by name; //正确，查询字段不含id，group by后id无要求，可要可不要;
select name from my_polygon group by id,name; //正确
//条件查询
8)SELECT column1, column2, ….. columnN FROM table_name WHERE [search_condition] AND [search_condition];
//and
select * from my_polygon where id >= 2 and salary <5000;
//or
select * from my_polygon where id >= 2 or salary <5000;
//and & or
select * from my_polygon where id >=2 and salary >5000 or id = 1;
//not
select * from my_polygon where name is not null;
//like
select * from my_polygon where name like ‘_青%’;
//in
select * from my_polygon where salary in (1000,1500);
//not in
select * from my_polygon where salary not in (1000,1500);
//between … and …
select * from my_polygon where salary between 0 and 2000;
//别名
9)SELECT column_name AS alias_name FROM table_name conditions… ;
select ST_AsText(mypolygon) as polygon from my_polygon; //字段别名
//主键自增
10)创建表时候使用
CREATE TABLE tablename (
id SERIAL PRIMARY KEY
);
3.空间字段函数
a) ST_AsText -返回没有SRID元数据的几何/地理的知名文本（WKT）表示。
text ST_AsText(几何 g1) ; //g1 必须为geometry类型
WKT格式不保持精度，以防止浮动截断，使用ST_AsBinary或ST_AsEWKB格式进行传输。
b)ST_GeomFromText -从知名文本表示（WKT）返回指定的ST_Geometry值。
geometry ST_GeomFromText(text WKT); //不知道srid时候默认srid=0
geometry ST_GeomFromText(text WKT, integer srid); //SRID=4326，WGS84坐标系; SRID=900913,900913坐标系;
当空间字段类型不适用geometry类型，使用text类型时候使用；
SQL: select ST_GeomFromText(text类型的字段) from 表名 ;
c)ST_AsBinary – 返回没有SRID元数据的几何/地理的已知二进制（WKB）表示。支持3维数据；
bytea ST_AsBinary(几何 g1) ;
bytea ST_AsBinary(几何 g1，文本NDR_or_XDR) ;
bytea ST_AsBinary(地理 g1) ;
bytea ST_AsBinary(地理 g1，文本NDR_or_XDR) ;
地理类型为三维，不考虑；
d)ST_Within – 如果几何A完全位于几何B内，则返回true
boolean ST_Within(几何 A，几何 B) ; //矩形，多边形电子围栏可用
e)ST_Contains – 当且仅当B的点没有位于A的外部并且B的内部至少有一个点位于A的内部时，才返回true。
boolean ST_Contains(几何几何 geomA，几何 geomB) ;
f)ST_Equals – 如果给定的几何图形代表相同的几何图形，则返回true。方向性被忽略
boolean ST_Equals(几何 A，几何 B) ;
g)ST_OrderingEquals – 如果给定的几何体表示相同的几何体并且点的方向顺序相同，则返回true。
boolean ST_OrderingEquals(geometry A, geometry B);
h)ST_Transform – 返回一个新的几何体，其坐标转换为不同的空间参考。
geometry ST_Transform(几何 g1，整数 srid) ;
geometry ST_Transform(几何 GEOM，文字 to_proj) ;
geometry ST_Transform(几何 GEOM，文字 from_proj，文字 to_proj) ;
geometry ST_Transform(几何 的geom，文本 from_proj，整数 to_srid) ;
j)ST_DWithin -以g1为中心,半径为distance_of_srid,这个范围内包不包含g2,如果包含反回true,否则即为假
boolean ST_DWithin(geometry g1, geometry g2, double precision distance_of_srid);//圆电子围栏
h)ST_MakePolygon – 创建由给定外壳形成的多边形。输入几何必须是封闭的LINESTRINGS。
geometry ST_MakePolygon(几何线串) ;
geometry ST_MakePolygon(几何外部字符串，几何[] 内部线) ;
4.高级空间数据检索
//获取区域的最大范围，是个矩形
select st_xmin(geom),st_ymin(geom),st_xmax(geom),st_ymax(geom) from 表名 ；
//返回含有指定点的geometry对应记录列表
select * from my_polygon where ST_Within(st_geomfromtext(‘POINT(1 1)’),mypolygon) ；
//计算距离，单位是度
SELECT ST_Distance(
ST_GeomFromText(‘POINT(114.1235 34.3521)’,4326),
ST_GeomFromText(‘LINESTRING(114.1260 34.45, 114.123 34.1546)’, 4326)
);
//计算距离，单位是米
SELECT ST_Distance(
ST_Transform(ST_GeomFromText(‘POINT(-72.1235 42.3521)’,4326),900913),
ST_Transform(ST_GeomFromText(‘LINESTRING(-72.1260 42.45, -72.123 42.1546)’, 4326),900913)
);
//判断指定A点是否在以B点为圆心，指定半径长度C的圆上; 圆电子围栏
select ST_DWithin(B,A,C)
示例：select ST_DWithin(‘POINT(2 2)’,’POINT(3 3)’,2) ；
//判断A点(0,0.5)是否在多边形上
SELECT ST_Contains((SELECT mypolygon FROM my_polygon WHERE id = 4), st_geometryfromtext(‘POINT(0 0.5)’));
//查找以(-87.71 43.741)为中心半径1500米范围内的兴趣点,之后按与这个中心点由近到远的顺序排列结果
select * from my_polygon where ST_DWithin(
ST_Transform(ST_GeomFromText(‘POINT(-87.71 43.741)’,4326),900913),
ST_Transform(ST_GeomFromText(st_astext(mypolygon),4326),900913),
1500
)
order by ST_Distance(
ST_Transform(ST_GeomFromText(‘POINT(-87.71 43.741)’,4326),900913),
ST_Transform(ST_GeomFromText(st_astext(mypolygon),4326),900913)
)；
注： 自己学习整理，数据库表名，字段，很多不太规范，希望大家指正，共同进步。
转载自：https://blog.csdn.net/helloworld_xwb/article/details/82257320
