Postgresql PostGIS使用总结

创建带有postgis功能的数据库的方法

1.安装时勾选默认创建的gis库 

2.新建一个数据库 执行create extension postgis

 

创建一个带有地理类型列的表

1.先创建一个不包含地理列的表

CREATE TABLE public."STATION_ADDRESS"
(
"ID" character(36) NOT NULL,
"CREATE_STATION_CODE" character varying(50),
"COORDINATE_VALUE" character varying(200),
"POINT" text,
"REMARK" character varying(200),
CONSTRAINT "STATION_ADDRESS_pkey" PRIMARY KEY ("ID")
)

用copy命令向数据表"STATION_ADDRESS" 导入数据（http://blog.csdn.net/namelb/article/details/7909910）

copy "STATION_ADDRESS" from 'd:/test/testdata.csv' delimiter as',' csv quote as '"'

--添加一个地理类型列

SELECT ADDGEOMETRYCOLUMN('STATION_ADDRESS','POINT_COORDINATE',4326, 'POINT',2)

--查询两点的距离小于100的
SELECT * FROM public."STATION_ADDRESS" WHERE ST_Distance("POINT_COORDINATE",ST_GeomFromText('POINT(112.308407 22.192877)',4326))=0;

--把字符串类型的字段转化为多边形列 

UPDATE "EXPRESS_RANGE" SET "SHAPE"=ST_MakePolygon(ST_GeomFromText('LINESTRING(select  "REMARK" from "EXPRESS_RANGE" t where t."ID"= "EXPRESS_RANGE"."ID")',4326))

--查看某一个点是否在某个多边形内 返回f表示不在范围内 返回T表示在多边形内
SELECT ST_Contains( ST_MakePolygon(ST_GeomFromText('LINESTRING ( 121.312350 30.971457 , 121.156783 31.092221 , 121.353250 31.278195 , 121.509125 31.157431 , 121.312350 30.971457 ) ')) ,st_point(121.632378,31.07106) )

--把geom列以文本形式显示

select ST_AsText("SHAPE") FROM "EXPRESS_RANGE"