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

PostGIS 的空间数据库操作1、SHP 导入 POSTGIS 数据库导出 sql 再导入数据库方法 1：以 SQL 文件为中间媒介实现shp 导入空间数据库Step1：shp2pgsql -s 4326 F:spatialdatacities.shp cities D:cities.sql 说明： -s 设置坐标系统；F:spatialdatacities.shp 指定数据源；cities 目标表名称。Step2：运行 postgres数据库；输入登录密码：admin 进入数据库；选中postgis 数据库Step3：点击工具栏上的，弹出 sql 窗口精选学习资料 - - - - - - - - - 名师归纳总结 - - - - - - -第 1 页，共 9 页Step4：点击，找到刚刚生成的SQL 文件，打开，运行即可。方法 2：通过命令行直接导入空间库Step1：shp2pgsql -s 4326 F:spatialdatacities.shp public.cities psql -U postgres -p admin -d postgis 说明： -s 设置坐标系统；F:spatialdatacities.shp 指定数据源；public.cities 目标表名称； U 用户名； p 密码； d 空间数据库名称。方法 3：通过界面导入空间库Step1：在开始菜单中，运行postgis 2.0 下面的Step2：点击，设置数据库连接；确定，如果成功，在前一界面上会出现“ Connection succeeded. ”的信息。精选学习资料 - - - - - - - - - 名师归纳总结 - - - - - - -第 2 页，共 9 页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 空间数据库表的名称精选学习资料 - - - - - - - - - 名师归纳总结 - - - - - - -第 3 页，共 9 页方法 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 精选学习资料 - - - - - - - - - 名师归纳总结 - - - - - - -第 4 页，共 9 页几何分析函数这类函数目前共有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), 精选学习资料 - - - - - - - - - 名师归纳总结 - - - - - - -第 5 页，共 9 页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 精选学习资料 - - - - - - - - - 名师归纳总结 - - - - - - -第 6 页，共 9 页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; 精选学习资料 - - - - - - - - - 名师归纳总结 - - - - - - -第 7 页，共 9 页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) 精选学习资料 - - - - - - - - - 名师归纳总结 - - - - - - -第 8 页，共 9 页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; 精选学习资料 - - - - - - - - - 名师归纳总结 - - - - - - -第 9 页，共 9 页