空间数据库实验指导书PostGIS的空间数据库操作.pdf

《空间数据库实验指导书PostGIS的空间数据库操作.pdf》由会员分享，可在线阅读，更多相关《空间数据库实验指导书PostGIS的空间数据库操作.pdf（9页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、PostGISPostGIS 的空间数据库操作的空间数据库操作1 1、SHPSHP 导入导入 POSTGISPOSTGIS 数据库数据库导出导出 sqlsql 再导入数据库再导入数据库方法方法 1 1：以：以 SQLSQL 文件为中间媒介实现文件为中间媒介实现 shpshp 导入空间数据库导入空间数据库Step1Step1：shp2pgsql -s 4326 F:spatialdatacities.shp cities D:cities.sql说明：-s设置坐标系统； F:spatialdatacities.shp指定数据源； cities 目标表名称。Step2Step2：运行 postgr

2、es 数据库；输入登录密码：admin 进入数据库；选中 postgis 数据库Step3Step3：点击工具栏上的，弹出 sql 窗口Step4Step4：点击，找到刚刚生成的 SQL 文件，打开，运行即可。方法方法 2 2：通过命令行直接导入空间库：通过命令行直接导入空间库Step1Step1：shp2pgsql -s 4326 F:spatialdatacities.shp public.cities psql -U postgres-padmin -d postgis说明：-s设置坐标系统； F:spatialdatacities.shp指定数据源 ；public.cities 目标表

3、名称； U 用户名； p 密码；d 空间数据库名称。方法方法 3 3：通过界面导入空间库：通过界面导入空间库Step1Step1：在开始菜单中，运行 postgis 2.0 下面的Step2Step2：点击，设置数据库连接；确定，如果成功，在前一界面上会出现“Connection succeeded.”的信息。Step3Step3：点击，在对话框找到需要转入空间数据库的 shp 文件，openStep4Step4：点击即可导入数据进行编码设置。Step5Step5：如果数据中的字段存在汉字的话，可以点击2 2、SHPSHP 导入导入 POSTGISPOSTGIS 数据库数据库方法方法 1 1：

4、通过命令行直接导出：通过命令行直接导出 shpshppgsql2shp -f d:shop_point.shp -h localhost -u postgres -P admin postgis public.cities说明：-fd:shop_point.shp 导出文件的名称和路径； -h 数据库的 ip 地址；U 用户名；p 密码；postgis 空间数据库名称；public.cities 空间数据库表的名称方法方法 2 2：通过命令行直接导出：通过命令行直接导出 shpshpStep1Step1：在开始菜单中，运行 postgis 2.0 下面的Step2Step2：点击，设置数据库连

5、接；确定，如果成功，在前一界面上会出现“Connection succeeded.”的信息。Step3Step3：选择 export 界面Step3Step3：点击；再点击即可3 3、PostGISPostGIS 函数分类函数分类字段处理函数字段处理函数AddGeometryColumn为已有的数据表增加一个地理几何数据字段；DropGeometryColumn删除一个地理数据字段的；SetSRID 设置 SRID 值几何关系函数几何关系函数这类函数目前共有 10 个，分别是：Distance， Equals， Disjoint， Intersects， Touches Crosses， Wi

6、thin， Overlaps，RelateContains，几何分析函数几何分析函数这类函数目前共有 12 个，分别是：Centroid，Area，Lenth，PointOnSurface，Boundary，Buffer，ConvexHull，Intersection，SymDifference， Difference， GeomUnion， MemGeomUnion读写函数读写函数这类函数很多，主要是用于在各种数据类型之间的转换，尤其是在于 Geometry 数据类型与其他如字符型等数据类型之间的转换，函数名如AsText、GeomFromText等。4 4、Geo-SQLGeo-SQL 查

7、询查询Selectc1.city_nameFromCitiesC1,RiversRWhereST_Overlaps(C1.geom,ST_Buffer(R.geom,3000)Select c1.city_name From Cities C1, (Select geom from rivers where name=Alabama) as mWhere ST_Overlaps(C1.geom,ST_Buffer(m,3000)Select c1.city_name From Cities C1, (Select ST_Buffer(geom,3000)as dd from riverswhe

8、re name=Platte) as m Where ST_Overlaps(C1.geom,m.dd)=trueSelectc1.city_name,ST_Overlaps(C1.geom,m.dd)FromCitiesC1,(SelectST_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条件查询空间

9、数据，查询结果的空间数据，用文本显示 ST_AsText与某点距离小于 XX 的要素有哪些点线面分别查询一次 ST_Distance、ST_MakePoint查询名字为 XX 的河流，流经哪些州ST_Crosses将某个城市平移一定的距离 ST_GeomFromText、ST_AsText附件：常用的附件：常用的 Geo-SQLGeo-SQL 查询查询1.SELECTshengjie_region.name,shijie_region.nameFROMshengjie_region ,shijie_region where shijie_region.provincena = shengjie

10、_region.nameand 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),3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.geom)SELECTshengjie_region.name,shijie_region.nameFROMshengjie_region,shijie_reg

11、ionwhereST_Within(ST_MakePoint(121.54,38.91),shijie_region.geom)andST_Within(ST_MakePoint(121.54,38.91),shengjie_region.geom)SELECTshengjie_region.name,shijie_region.name,xianjie_region.nameFROMshengjie_region,shijie_region,xian_point,xianjie_regionwhereST_Within(ST_MakePoint(121.54,38.91),shengjie_

12、region.geom)andST_Within(ST_MakePoint(121.54,38.91),shijie_region.geom)andST_Within(ST_MakePoint(121.54, 38.91), xianjie_region.geom)SELECT name FROM guodao_polyline where ST_Within(ST_MakePoint(121.54,38.91), geom)select name,st_distance(ST_MAKEPOINT(116.561, 40.276),geom) as distance fromshengjie_

13、region where name=天津市 or name =辽宁省 or name=北京市select st_point(63.573566, 44.646244) from dual;SELECT ST_AsText(geom) as wkt FROM shengjie_region where name = 新疆维吾尔自治区SELECT ST_AsEWKT(geom) as buffer FROM shengjie_regionSELECTST_AsText(st_transform(st_setsrid(ST_Buffer(st_transform(st_setsrid(st_geom

14、fromtext( + wkt + ), 4326), 2333), 10800), 2333), 4326) FROM dualSELECT ST_MakePoint(121.55223, 38.86758) from dual;SELECTst_distance(ST_MakePoint(121.55223,38.86758),geom)fromgongyuan_point;SELECT name FROM gongyuan_point where ST_Within(ST_MakePoint(121.54, 38.91),geom)SELECT ST_SetSRID(ST_MakePoi

15、nt(121.55223, 38.86758),4326);SELECTname,st_distance(ST_MakePoint(121.55223,38.86758),geom)fromgongyuan_point where st_distance(ST_MakePoint(121.55223, 38.86758),geom) 0.02 ;SELECTgid,name,st_setsrid(ST_MakePoint(121.55223,38.86758),4326)fromgongyuan_point where st_distance(ST_MakePoint(121.55223, 3

16、8.86758),geom) 0.02 ;SELECTST_Distance(ST_Transform(ST_GeomFromText(POINT(121.5522338.86758),4326),26986),ST_Transform(ST_GeomFromText(POINT(121.5622438.87757), 4326),26986);selectgid,POINT(geom)fromgongyuan_pointwherest_distance(ST_MakePoint(121.55223, 38.86758),geom) 0.02 ;SELECTST_Distance(ST_Tra

17、nsform(ST_GeomFromText(POINT(121.5522338.86758),4326),26986),ST_Transform(ST_GeomFromText(POINT(121.5617140838.87784198), 4326),26986);SELECTST_DWithin(ST_Transform(ST_GeomFromText(POINT(121.5522338.86758),4326),26986),ST_Transform(ST_GeomFromText(POINT(121.5617140838.87784198), 4326),26986),1500) f

18、rom dual;SELECTST_DWithin(ST_Transform(ST_GeomFromText(POINT(121.5522338.86758),4326),26986),ST_Transform(ST_GeomFromText(POINT(121.5617140822.23.24.25.26.27.28.29.30.31.32.33.34.35.36.37.38.38.87784198), 4326),26986),1400) from dual;SELECTname,st_distance(ST_MakePoint(121.55223,38.86758),geom)fromg

19、ongyuan_point where st_distance(ST_MakePoint(121.55223, 38.86758),geom) 0.02order by st_distance(ST_MakePoint(121.55223, 38.86758),geom);SELECTST_Transform(ST_GeomFromText(POINT(121.5522338.86758),4326),26986) from dualSELECT ST_Transform(point(t.geom,26986),5144) from gongyuan_point tSELECT point(g

20、ongyuan_point.geom) from gongyuan_pointSELECTname,gidFROMgongyuan_pointORDERBYgeomst_setsrid(st_makepoint(121.55223,38.86758),4326) LIMIT 10;select st_extent(geom) as wgs84 from gongyuan_point;selectgid,st_x(geom),st_y(geom),st_z(geom),POINT(geom)fromgongyuan_pointwhere st_distance(ST_MakePoint(121.

21、55223, 38.86758),geom) 0.02 ;selectgid,st_x(geom),st_y(geom),st_z(geom),POINT(geom),st_srid(geom)fromgongyuan_point where st_distance(ST_MakePoint(121.55223, 38.86758),geom) 0.02 ;selectST_Area(geom),gid,st_AsText(geom),st_x(geom),st_y(geom),st_z(geom),POINT(geom),st_srid(geom)from gongyuan_point wh

22、ere st_distance(ST_MakePoint(121.55223,38.86758),geom) 0.02 ;selectST_distance_sphere(ST_MakePoint(121.55223,38.86758),geom)fromgongyuan_point where st_distance(ST_MakePoint(121.55223, 38.86758),geom) 0.02 ;selectST_distance_sphere(ST_MakePoint(121.55223,38.86758),geom)fromgongyuan_point where ST_di

23、stance_sphere(ST_MakePoint(121.55223, 38.86758),geom) 1000 ;selectST_distance_sphere(ST_MakePoint(121.55223,38.86758),geom)fromgongyuan_point where ST_distance_sphere(ST_MakePoint(121.55223, 38.86758),geom) 1500 ;selectST_distance_sphere(ST_MakePoint(121.55223,38.86758),geom),ST_AsBinary(geom),ST_As

24、EWKT(geom),ST_AsEWKB(geom),ST_AsHEXEWKB(geom)fromgongyuan_pointwhereST_distance_sphere(ST_MakePoint(121.55223, 38.86758),geom) 1500 ;selectname,ST_distance_sphere(ST_MakePoint(121.55223,38.86758),geom)fromgongjiaozhan_pointwhereST_distance_sphere(ST_MakePoint(121.55223,38.86758),geom) 500 ;selectnam

25、e,ST_distance_sphere(ST_MakePoint(121.55223,38.86758),geom)fromdasha_point where ST_distance_sphere(ST_MakePoint(121.55223, 38.86758),geom) 1500 ;selectname,ST_distance_sphere(ST_MakePoint(121.55223,38.86758),geom)fromdasha_point where ST_distance_sphere(ST_MakePoint(121.55223, 38.86758),geom) 1500

26、order by ST_distance_sphere(ST_MakePoint(121.55223, 38.86758),geom) limit 1 ;selectname,ST_distance_sphere(ST_MakePoint(121.55223,38.86758),geom)fromchaoshishangcheng_pointwhereST_distance_sphere(ST_MakePoint(121.55223,38.86758),geom)500orderbyST_distance_sphere(ST_MakePoint(121.55223,38.86758),geom

27、) limit 1;39. SELECT name FROM shengjie_region whereST_Within(ST_MakePoint(121.55223,38.86758), geom)40. SELECT name FROM shijie_region whereST_Within(ST_MakePoint(121.55223,38.86758), geom)41. SELECT name FROM xianjie_region whereST_Within(ST_MakePoint(121.55223,38.86758), geom)42. selectname,ST_di

28、stance_sphere(ST_MakePoint(121.55223,38.86758),geom),st_x(geom),st_y(geom)fromdasha_pointwhereST_distance_sphere(ST_MakePoint(121.55223, 38.86758),geom) 3000 order byST_distance_sphere(ST_MakePoint(121.55223, 38.86758),geom) limit 1 ;43. selectname,ST_distance_sphere(ST_MakePoint(121.55223,38.86758)

29、,geom),st_x(geom),st_y(geom)frombinguanjiudian_pointwhereST_distance_sphere(ST_MakePoint(121.55223, 38.86758),geom) 3000 order byST_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,31),st_geomfromText(LINESTRIN

30、G(2 0,2 2,5 2,3 1) from dual;45. select ST_AsEWKT(ST_Difference(st_geomfromText(POL YGON(1 1,2 3,3 4,3 1,11),st_geomfromText(POLYGON(2 0,2 2,5 2,1 3,2 0) from dual;46. select GeometryType(st_geomfromText(MULTILINESTRING(11,2 3,3 4,3 1,2 1,11),(1 2,2 3,4 5) from dual;47. SELECT name,st_area(geom) as

31、area from shengjie_region ORDER BY area DESCLIMIT 1;48. SELECT st_Length2d(st_GeomFromText(MultiLineString(1 1,2 2,3 3),(4 4,5 5)from dual;49. SELECTgid,name,st_astext(geom)ASLinestring,st_length(geom)FROMguodao_polyline where name=东北路;50. SELECT name, st_astext(geom) FROM guodao_polyline where gid=

32、152415;51. SELECT gid,st_astext(geom)ASMULTIPOINTFROM guodao_polylineWHERE gid = 152415;52. SELECT st_astext(geom)AS MLINESTRINGFROM 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(LIN

33、ESTRING(1 2 3, 4 5 6, 6 7 8) as the_line)As foo;56. SELECTST_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. SELECTST_AsText(ST_Line_SubString(ST_GeomFromText(LINES

34、TRING(25 50,100 125, 150 190), 0.333, 0.666);58. SELECTST_AsText(house_loc)Asas_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, 34) As street_line,ST_MakePoint(x*

35、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)59.60.61.62.63.As y) As foo WHERE ST_DWithin(street_line, house_loc, 0.2);SELECTST_AsText(the_geom)FROM(SELECTST_LocateAlong( ST_GeomFromText(MULTILINESTRINGM(1 2 3, 3 4 2,

36、 9 4 3), (12 3, 5 4 5),3) As the_geom) As foo;SELECTST_AsText(the_geom)FROM(SELECTST_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;SELECTST_AsEWKT(ST_Dump(the_geom).geom)FROM(SELECTST_LocateBetweenElevations( ST_GeomFromEWKT(LINESTRING(1 2 6, 4 5 -1, 7 89),6,9)As the_geom) As fooSELECT ST_InterpolatePoint(LINESTRING M (0 0 0, 10 0 20), POINT(5 5);SELECTST_AsText(ST_AddMeasure( ST_GeomFromEWKT(MULTILINESTRINGM(1 0 4, 20 4, 4 0 4),(1 0 4, 2 0 4, 4 0 4),10,70) As ewelev;