1.将.shp格式数据代入数据库postgis 中:
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

 

shp2pgsql -W"GBK" f:\XianCh_point.shp ccvv >f:\ccvv.sql

 

psql -d postgis -f f:\ccvv.sql postgres

 

表名称：ccvv
数据库名字：postgis
角色名：postgres
2.创建表空间数据库用户在pgAdmin中可设置。
1）create tablespace  dhsdpgis_tp owner postgres location ‘d:\’
2）crate database dhsdpgis_db owner postgres   TABLESPACE  dhsdpgis_tp;
3）createuser -U postgres  - P Allen

 

 

3进入数据库  cd D:\PostgreSQL\9.2\bin

 

D:\PostgreSQL\9.2\bin psql –d postgis postgres

 

创建空间表

CREATE TABLE ROADS ( ID int4, ROAD_NAME varchar(25), geom. geometry(LINESTRING,4326) );

1.插入一列（线）

SELECT AddGeometryColumn( 'roads', 'geom', 423, 'LINESTRING', 2);

 

2.('MULTIPOLYGON') 
 
  

   3. (GEOMETRY)
  

  

  

  

  

  
 
  

   
   
 

  
 
  

   
   
 

  
 
  

   
   
 

  
 
  

   
   
 

  
 
  

   4
  

  

  

  

  
 
  

   添加点
   

   
  
 
  

   String sql = "INSERT INTO business(名称, geom)  VALUES('cccXXXXcc集团', 'POINT("+x+" "+y+")')";        
  
 
  

   修改点
   

   
  
 
  

   String sql = 
   "update "
   +table+
   " set 
   名称
   ='
   东和盛达
   2' , geom =ST_GeomFromText('POINT("
   +x+
   " "
   +y+
   ")')  where gid = 4"
   ;   
   
   

  
 
  

   
   
 

  
 
  

   geometry ST_GeomFromText(text WKT);
  
 
  

   geometry ST_GeomFromText(text WKT, integer srid);
  
 
  

   
   
 

  
 
  

   
   
 

  
 
  

   
   
 

  
 
  

   
   
 

  

  
 
  

   
   
 

  
 
  

   5．
  
 
  

   ST_GeomFromText('POINT(22 22)')将其转化为 geometry  类似010100000054B7AB482BB65D40D40D4D6BCEB64240
  
 
  

   ST_AsText 转化为  类似POINT(22 22)
   

   
  
 
  

   
   
 

   
  
 
  

   
   
 

   
  
 
  

   
   
 

   
  
 
  

   
   
 

   
  
 
  

   
   
 

   
  
 
  

   
   
 

   
  
 
  

   
   
 

   
  
 
  

   
   
 

   
  
 
  

   
   
 

   
  
 
  

   
   
 

   
  
 
  

   
   
 

   
  
 
  

   
   
 

   
  
 
  

   
   
 

   
  
 
  

   
   
 

   
  
 
  

   
   
 

   
  
 
  

   
   
 

   
  
 
  

   获取两个面  之间最短距离的点
   

   
  
 
  

   <?xml:namespace prefix = v ns = "urn:schemas-microsoft-com:vml" />
  
 
  

   
   
 

  
 
  

   ST_ClosestPoint
   （geometry g1, geometry g2
   ）
   g2
   到
   g1
   最近的点
      
   该点在
   g1
   上
   

   
  
 
  

   SELECT ST_AsText(
   

  
 
  

   ST_ClosestPoint(ST_GeomFromText(’ POLYGON((175 150, 20 40, 50 60, 125 100, 175 150))’),
   

  
 
  

   ST_Buffer(ST_GeomFromText(’POINT(110 170)’), 20)))
   

  
 
  

   
   
 

  
 
  

   
  
 
  

   SELECT  ST_AsText(ST_ClosestPoint(pt,line)  
  
 
  

   ) AS cp_pt_line, ST_AsText(ST_ClosestPoint(line,pt )) As cp_line_pt FROM (SELECT 'POINT(100 100)'::geometry  As pt, 'LINESTRING (20 80, 98 190, 110 180, 50 75 )'::geometry As line) As foo ;
  
 
  

   结果：
   POINT(100 100) | POINT(73.0769230769231  115.384615384615)
   

  
 
  

   或者
  
 
  

   SELECT ST_AsText(ST_ClosestPoint(ST_GeomFromText('LINESTRING (20 80, 98 190, 110 180, 50 75 )'),ST_GeomFromText('POINT(100 100)') ) ) AS cp_pt_line FROM  business;
  
 
  

   
   
 

  
 
  

   结果："POINT(73.0769230769231 115.384615384615)"
  
 
  

   
   
 

  
 
  

   ST_Contains(A,B)    B
   在
   A
   内
   
   

  
 
  

   ST_Within(A,B)   A 
   在
   B
   内
   
   

  
 
  

   
   
 

  
 
  

   
   
 

  
 
  

   
   
 

  
 
  

   判断
   A
   是否被
   B
   包含
       ST_Within(geometry A, geometry B)
   

   
  
 
  

   判断
   A
   是否包含
   B      ST_Contains(geometry A, geometry B)

   判断
   A
   是否覆盖
    B      ST_Covers(geometry A, geometry B)
   


   
   

  
 
  

   
   
 

  
 
  

   
  
 
  

   SELECT ST_AsText(
   

  
 
  

   ST_ShortestLine(’POINT(100 100) ’::geometry,’LINESTRING (20 80, 98  -190, 110 180, 50 75 )’::geometry)) As sline;sline
   

  
 
  

   -----------------
   

  
 
  

   LINESTRING(100 100,73.0769230769231  -
   

  
 
  

   115.384615384615)
   

  
 
  

   
   
 

  
 
  

   
  
 
  

   SELECT ST_AsText(ST_ShortestLine(ST_GeomFromText(’ POLYGON((175 150, 20 40, 50 60, 125 100, 175150))’),ST_Buffer(  -ST_GeomFromText(’POINT(110 170)’), 20))) As slinewkt;
   

  
 
  

   LINESTRING(140.752120669087  125.695053378061,121.111404660392 153.370607753949)
   

  
 
  

   
   
 

  
 
  

   
   
 

  
 
  

   
   
 

  
 
  

   
   
 

  
 
  

   
   
 

  
 
  

   
   
 

  
 
  

   
   
 

  
 
  

   ST_DWithin(
   

  
 
  

           ST_Transform(ST_GeomFromText('POINT(118.84839183778 37.4231344123688)',4326),2383),
   

  
 
  

           ST_Transform(ST_GeomFromText('MULTIPOLYGON (((118.853945599439 37.4229670850174, 118.8658 37.4225250432923, 118.853278523793 37.4326615750288, 118.853476307826 37.4231018594778, 118.853945599439 37.4229670850174)))',4326),2383),
   

  
 
  

          100
   

  
 
  

   
   
 

  
 
  

   
   
 

  
 
  

   
   
 

  
 
  

   多边形查询   多边形内的所有点
   

   
  
 
  

   SELECT gid, "名称", geom
   

  
 
  

     FROM business where ST_Within(business.geom,
   

  
 
  

           ST_GeomFromText('MULTIPOLYGON (((118.853945599439 37.4229670850174, 118.8658 37.4225250432923, 118.853278523793 37.4326615750288, 118.853476307826 37.4231018594778, 118.853945599439 37.4229670850174)))')
   

  
 
  

   ) ;
   

  
 
  

   
   
 

  
 
  

   缓冲区查询： 
   

  
 
  

   
   
 

  
 
  

   String sql = "select  *,ST_AsText(ST_Buffer(ST_Transform(ST_GeomFromText('POINT(118.8665,37.420134)',4236),4236),2500)) as BufferFeatures from business  where ST_DWithin(ST_Transform(ST_SetSRID(geom, 4236),2383),ST_Transform(ST_GeomFromText('POINT(118.8665,37.420134)',4236),4236),2500) ";
   

  
 
  

   空间查询：
   

   
  
 
  

   
   
 

   
  
 
  

   1.圆查：
   

  
 
  

   String sql = "select *,gid,ST_GeometryType(geom),GeometryType(geom) ,ST_Area(ST_Transform(ST_GeomFromText('MULTIPOLYGON((("+geometry+")))',4236),2383)) from "+layerName+"   limit 1 ";
   

  
 
  

   2.拉框查询：
   

  
 
  

   String sql = 
   "select *,ST_AsText(geom) as geometry,GeometryType(geom) as geometryType from "
   +
   layerName
   +
   " where ST_Within(geom,ST_GeomFromText('MULTIPOLYGON((("
   +
   geometry
   +
   ")))'))"
   ;