空间数据库试验参考指导书PostGIS的空间数据库操作.doc

PostGIS空间数据库操作
1、SHP导入POSTGIS数据库
导出sql再导入数据库
方法1：以SQL文件为中间媒介实现shp导入空间数据库
Step1：shp2pgsql -s 4326  F:\spatial\data\cities.shp  cities >D:\cities.sql
说明：-s 设置坐标系统； F:\spatial\data\cities.shp指定数据源； cities 目标表名称。
Step2：运行postgres数据库；输入登录密码：admin 进入数据库；选中postgis数据库

Step3：点击工具栏上，弹出sql窗口

Step4：点击，找到刚才生成SQL文件，打开，运行即可。
方法2：经过命令行直接导入空间库
Step1：shp2pgsql -s 4326  F:\spatial\data\cities.shp public.cities psql -U postgres  -p admin -d postgis
说明：-s 设置坐标系统； F:\spatial\data\cities.shp指定数据源 ；public.cities 目标表名称； –U 用户名； –p 密码； –d 空间数据库名称。

方法3：经过界面导入空间库
Step1：在开始菜单中，运行 postgis 2.0下面

Step2：点击，设置数据库连接；确定，假如成功，在前一界面上会出现“Connection succeeded.”信息。

Step3：点击，在对话框找到需要转入空间数据库shp文件，open

Step4：点击即可导入数据
Step5：假如数据中字段存在汉字话，能够点击进行编码设置。
2、SHP导入POSTGIS数据库
方法1：经过命令行直接导出shp
pgsql2shp -f d:\shop_point.shp -h localhost -u postgres -P admin postgis  public.cities
说明：-f d:\shop_point.shp导出文件名称和路径； -h 数据库ip地址；–U 用户名； –p 密码； postgis  空间数据库名称；public.cities空间数据库表名称
方法2：经过命令行直接导出shp
Step1：在开始菜单中，运行 postgis 2.0下面
Step2：点击，设置数据库连接；确定，假如成功，在前一界面上会出现“Connection succeeded.”信息。
Step3：选择export界面

Step3：点击；再点击即可

3、PostGIS函数分类
字段处理函数
AddGeometryColumn为已经有数据表增加一个地理几何数据字段；
DropGeometryColumn删除一个地理数据字段；
SetSRID设置SRID值
几何关系函数
这类函数现在共有10个，分别是：
Distance，Equals，Disjoint，Intersects， Touches Crosses， Within， Overlaps， Contains， Relate
几何分析函数
这类函数现在共有12个，分别是：
Centroid， Area， Lenth， PointOnSurface， Boundary， Buffer， ConvexHull， Intersection， SymDifference， Difference， GeomUnion， MemGeomUnion

读写函数
这类函数很多，关键是用于在多种数据类型之间转换，尤其是在于Geometry数据类型和其它如字符型等数据类型之间转换，函数名如AsText、GeomFromText等。
4、Geo-SQL查询
Select c1.city_name From Cities C1, Rivers R Where ST_Overlaps(C1.geom,ST_Buffer(R.geom,3000))
Select c1.city_name From Cities C1, (Select geom from rivers where name='Alabama') as m Where ST_Overlaps(C1.geom,ST_Buffer(m,3000))

Select c1.city_name From Cities C1, (Select ST_Buffer(geom,3000) as dd from rivers where name='Platte') as m Where ST_Overlaps(C1.geom,m.dd)=true
Select c1.city_name, ST_Overlaps(C1.geom,m.dd) From Cities C1, (Select ST_Buffer(geom,3000) as dd from rivers where name='Platte') as m

做一个点查询（查询州数据）ST_Within
点查询城市 ST_Buffer、ST_Within
查询某一州包含城市 ST_Contains
查询面价大于XX州有哪些 ST_Area
查询州名字（根据面积从大到小排序）ST_Area
条件查询空间数据，查询结果空间数据，用文本显示 ST_AsText
和某点距离小于XX要素有哪些（点线面分别查询一次）ST_Distance、ST_MakePoint
查询名字为XX河流，流经哪些州 ST_Crosses
将某个城市平移一定距离 ST_GeomFromText、ST_AsText

附件：常见Geo-SQL查询
1. SELECT shengjie_region.name,shijie_region.name FROM shengjie_region ,shijie_region where shijie_region.provincena = shengjie_region.name and ST_Within(ST_MakePoint(121.54, 38.91), shengjie_region.geom) 点查询某省份，并找到该省份城市
2. SELECT name FROM shijie_region where ST_Within(ST_MakePoint(121.54, 38.91), geom)
3. SELECT shengjie_region.name,shijie_region.name FROM shengjie_region ,shijie_region where ST_Within(ST_MakePoint(121.54, 38.91), shijie_region.geom) and ST_Within(ST_MakePoint(121.54, 38.91), shengjie_region.geom)
4. SELECT shengjie_region.name,shijie_region.name,xianjie_region.name FROM shengjie_region ,shijie_region,xian_point,xianjie_region where ST_Within(ST_MakePoint(121.54, 38.91), shengjie_region.geom) and ST_Within(ST_MakePoint(121.54, 38.91), shijie_region.geom) and ST_Within(ST_MakePoint(121.54, 38.91), xianjie_region.geom)
5. SELECT name FROM guodao_polyline where ST_Within(ST_MakePoint(121.54, 38.91), geom) 点查询

6. select name,st_distance(ST_MAKEPOINT(116.561, 40.276),geom) as distance from shengjie_region where name='天津市' or name ='辽宁省' or name='北京市'
7. select st_point(63.573566, 44.646244) from dual;
8. SELECT ST_AsText(geom) as wkt FROM shengjie_region where name = '新疆维吾尔自治区'

9. SELECT ST_AsEWKT(geom) as buffer FROM shengjie_region
10. SELECT ST_AsText(st_transform(st_setsrid(ST_Buffer(st_transform(st_setsrid(st_geomfromtext('" + wkt + "'), 4326), 2333), 10800), 2333), 4326)) FROM dual
11. SELECT ST_MakePoint(121.55223, 38.86758) from dual;
12. SELECT st_distance(ST_MakePoint(121.55223, 38.86758),geom) from gongyuan_point;
13. SELECT name FROM gongyuan_point where ST_Within(ST_MakePoint(121.54, 38.91), geom)
14. SELECT ST_SetSRID(ST_MakePoint(121.55223, 38.86758),4326);
15. SELECT name,st_distance(ST_MakePoint(121.55223, 38.86758),geom) from gongyuan_point where st_distance(ST_MakePoint(121.55223, 38.86758),geom)< 0.02 ;
16. SELECT gid,name,st_setsrid(ST_MakePoint(121.55223, 38.86758),4326) from gongyuan_point where st_distance(ST_MakePoint(121.55223, 38.86758),geom)< 0.02 ;
17. SELECT ST_Distance(ST_Transform(ST_GeomFromText('POINT(121.55223 38.86758)',4326),26986),ST_Transform(ST_GeomFromText('POINT(121.56224 38.87757)', 4326),26986));
18. select gid,POINT(geom) from gongyuan_point where st_distance(ST_MakePoint(121.55223, 38.86758),geom)< 0.02 ;
19. SELECT ST_Distance(ST_Transform(ST_GeomFromText('POINT(121.55223 38.86758)',4326),26986),ST_Transform(ST_GeomFromText('POINT(121.56171408 38.87784198)', 4326),26986));
20. SELECT ST_DWithin(ST_Transform(ST_GeomFromText('POINT(121.55223 38.86758)',4326),26986),ST_Transform(ST_GeomFromText('POINT(121.56171408 38.87784198)', 4326),26986),1500) from dual;
21. SELECT ST_DWithin(ST_Transform(ST_GeomFromText('POINT(121.55223 38.86758)',4326),26986),ST_Transform(ST_GeomFromText('POINT(121.56171408 38.87784198)', 4326),26986),1400) from dual;
22. SELECT name,st_distance(ST_MakePoint(121.55223, 38.86758),geom) from gongyuan_point where st_distance(ST_MakePoint(121.55223, 38.86758),geom)< 0.02 order by st_distance(ST_MakePoint(121.55223, 38.86758),geom);
23. SELECT ST_Transform(ST_GeomFromText('POINT(121.55223 38.86758)',4326),26986) from dual
24. SELECT ST_Transform(point(t.geom,26986),5144) from gongyuan_point t
25. SELECT point(gongyuan_point.geom) from gongyuan_point
26. SELECT name, gid FROM gongyuan_point ORDER BY geom <-> st_setsrid(st_makepoint(121.55223,38.86758),4326) LIMIT 10;
27. select st_extent(geom) as wgs84 from gongyuan_point;
28. select gid,st_x(geom),st_y(geom),st_z(geom),POINT(geom) from gongyuan_point where st_distance(ST_MakePoint(121.55223, 38.86758),geom)< 0.02 ;
29. select gid,st_x(geom),st_y(geom),st_z(geom),POINT(geom),st_srid(geom) from gongyuan_point where st_distance(ST_MakePoint(121.55223, 38.86758),geom)< 0.02 ;
30. select ST_Area(geom),gid,st_AsText(geom),st_x(geom),st_y(geom),st_z(geom),POINT(geom),st_srid(geom) from gongyuan_point where st_distance(ST_MakePoint(121.55223, 38.86758),geom)< 0.02 ;
31. select ST_distance_sphere(ST_MakePoint(121.55223, 38.86758),geom) from gongyuan_point where st_distance(ST_MakePoint(121.55223, 38.86758),geom)< 0.02 ;
32. select ST_distance_sphere(ST_MakePoint(121.55223, 38.86758),geom) from gongyuan_point where ST_distance_sphere(ST_MakePoint(121.55223, 38.86758),geom) < 1000 ;
33. select ST_distance_sphere(ST_MakePoint(121.55223, 38.86758),geom) from gongyuan_point where ST_distance_sphere(ST_MakePoint(121.55223, 38.86758),geom) < 1500 ;
34. select ST_distance_sphere(ST_MakePoint(121.55223, 38.86758),geom), ST_AsBinary(geom), ST_AsEWKT(geom), ST_AsEWKB(geom), ST_AsHEXEWKB(geom) from gongyuan_point where ST_distance_sphere(ST_MakePoint(121.55223, 38.86758),geom) < 1500 ;
35. select name,ST_distance_sphere(ST_MakePoint(121.55223, 38.86758),geom) from gongjiaozhan_point where ST_distance_sphere(ST_MakePoint(121.55223, 38.86758),geom) < 500 ;
36. select name,ST_distance_sphere(ST_MakePoint(121.55223, 38.86758),geom) from dasha_point where ST_distance_sphere(ST_MakePoint(121.55223, 38.86758),geom) < 1500 ;
37. select name,ST_distance_sphere(ST_MakePoint(121.55223, 38.86758),geom) from dasha_point where ST_distance_sphere(ST_MakePoint(121.55223, 38.86758),geom) < 1500 order by ST_distance_sphere(ST_MakePoint(121.55223, 38.86758),geom) limit 1 ;
38. select name,ST_distance_sphere(ST_MakePoint(121.55223, 38.86758),geom) from chaoshishangcheng_point where ST_distance_sphere(ST_MakePoint(121.55223, 38.86758),geom) < 500 order by ST_distance_sphere(ST_MakePoint(121.55223, 38.86758),geom) limit 1;
39. SELECT name FROM shengjie_region where ST_Within(ST_MakePoint(121.55223, 38.86758), geom)
40. SELECT name FROM shijie_region where ST_Within(ST_MakePoint(121.55223, 38.86758), geom)
41. SELECT name FROM xianjie_region where ST_Within(ST_MakePoint(121.55223, 38.86758), geom)
42. select name,ST_distance_sphere(ST_MakePoint(121.55223, 38.86758),geom),st_x(geom),st_y(geom) from dasha_point where ST_distance_sphere(ST_MakePoint(121.55223, 38.86758),geom) < 3000 order by ST_distance_sphere(ST_MakePoint(121.55223, 38.86758),geom) limit 1 ;
43. select name,ST_distance_sphere(ST_MakePoint(121.55223, 38.86758),geom),st_x(geom),st_y(geom) from binguanjiudian_point where ST_distance_sphere(ST_MakePoint(121.55223, 38.86758),geom) < 3000 order by ST_distance_sphere(ST_MakePoint(121.55223, 38.86758),geom) limit 1 ;
44. select ST_AsEWKT(ST_Difference(st_geomfromText('LINESTRING(1 1,2 3,3 4,3 1)'),st_geomfromText('LINESTRING(2 0,2 2,5 2,3 1)'))) from dual;
45. select ST_AsEWKT(ST_Difference(st_geomfromText('POLYGON((1 1,2 3,3 4,3 1,1 1))'),st_geomfromText('POLYGON((2 0,2 2,5 2,1 3,2 0))'))) from dual;
46. select GeometryType(st_geomfromText('MULTILINESTRING((1 1,2 3,3 4,3 1,2 1,1 1),(1 2,2 3,4 5))')) from dual;
47. SELECT name,st_area(geom) as area from shengjie_region ORDER BY area DESC LIMIT 1;
48. SELECT st_Length2d(st_GeomFromText('MultiLineString((1 1,2 2,3 3),(4 4,5 5))')) from dual;
49. SELECT gid,name ,st_astext(geom) AS Linestring,st_length(geom) FROM guodao_polyline where name='东北路';
50. SELECT name, st_astext(geom) FROM guodao_polyline where gid='152415';
51. SELECT gid, st_astext (geom) AS MULTIPOINT FROM guodao_polyline WHERE gid = 152415;
52. SELECT st_astext(geom) AS MLINESTRING FROM guodao_polyline;
53. SELECT name FROM guodao_polyline where gid='152415';
54. SELECT ST_AsEWKT(ST_Line_Interpolate_Point(the_line, 0.5))
55. FROM (SELECT ST_GeomFromEWKT('LINESTRING(1 2 3, 4 5 6, 6 7 8)') as the_line) As foo;
56. SELECT ST_AsText(ST_Line_Interpolate_Point(foo.the_line, ST_Line_Locate_Point(foo.the_line, ST_GeomFromText('POINT(4 3)')))) FROM (SELECT ST_GeomFromText('LINESTRING(1 2, 4 5, 6 7)') As the_line) As foo;
57. SELECT ST_AsText(ST_Line_SubString(ST_GeomFromText('LINESTRING(25 50, 100 125, 150 190)'), 0.333, 0.666));
58. SELECT ST_AsText(house_loc) As as_text_house_loc,startstreet_num + CAST( (endstreet_num - startstreet_num)*ST_Line_Locate_Point(street_line, house_loc) As integer) As street_num FROM (SELECT ST_GeomFromText('LINESTRING(1 2, 3 4)') As street_line,ST_MakePoint(x*1.01,y*1.03) As house_loc, 10 As startstreet_num, 20 As endstreet_num FROM generate_series(1,3) x CROSS JOIN generate_series(2,4) As y) As foo WHERE ST_DWithin(street_line, house_loc, 0.2);
59. SELECT ST_AsText(the_geom) FROM (SELECT ST_LocateAlong( ST_GeomFromText('MULTILINESTRINGM((1 2 3, 3 4 2, 9 4 3), (1 2 3, 5 4 5))'),3) As the_geom) As foo;
60. SELECT ST_AsText(the_geom) FROM (SELECT ST_LocateBetween( ST_GeomFromText('MULTILINESTRING M ((1 2 3, 3 4 2, 9 4 3), (1 2 3, 5 4 5))'),1.5, 3) As the_geom) As foo;
61. SELECT ST_AsEWKT((ST_Dump(the_geom)).geom) FROM (SELECT ST_LocateBetweenElevations( ST_GeomFromEWKT('LINESTRING(1 2 6, 4 5 -1, 7 8 9)'),6,9)As the_geom) As foo
62. SELECT ST_InterpolatePoint('LINESTRING M (0 0 0, 10 0 20)', 'POINT(5 5)');
63. SELECT ST_AsText(ST_AddMeasure( ST_GeomFromEWKT('MULTILINESTRINGM((1 0 4, 2 0 4, 4 0 4),(1 0 4, 2 0 4, 4 0 4))'),10,70)) As ewelev;