5空间数据库实验指导书-PostGIS的空间数据库操作

3空间数据库实验指导书-PostGIS的空间数据库操作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目标 表 关于同志近三年现实表现材料材料类招标技术评分表图表与交易pdf视力表打印pdf用图表说话 pdf 名称。Step2：运行postgres数据库；输入登录密码：admin进入数据库；选中postgis数据库Step3：点击工具栏上的，弹出sql窗口Step4：点击，找到刚才生成的SQL文件，打开，运行即可。方法2：通过命令行直接导入空间库Step1：shp2pgsql -s 4326  F:\spatial\data\cities.shp public.cities psql -U postgres -padmin -d postgis说明：-s设置坐标系统；F:\spatial\data\cities.shp指定数据源；public.cities目标表名称；–U用户名；–p密码；–d空间数据库名称。方法3：通过界面导入空间库Step1：在开始菜单中，运行postgis2.0下面的Step2：点击，设置数据库连接；确定，如果成功，在前一界面上会出现“Connectionsucceeded.”的信息。Step3：点击，在对话框找到需要转入空间数据库的shp文件，openStep4：点击即可导入数据Step5：如果数据中的字段存在汉字的话，可以点击进行编码设置。2、SHP导入POSTGIS数据库方法1：通过命令行直接导出shppgsql2shp -f d:\shop_point.shp -h localhost -u postgres -P adminpostgis public.cities说明：-fd:\shop_point.shp导出文件的名称和路径；-h数据库的ip地址；–U用户名；–p密码；postgis 空间数据库名称；public.cities空间数据库表的名称方法2：通过命令行直接导出shpStep1：在开始菜单中，运行postgis2.0下面的Step2：点击，设置数据库连接；确定，如果成功，在前一界面上会出现“Connectionsucceeded.”的信息。Step3：选择export界面Step3：点击；再点击即可3、PostGIS函数分类字段处理函数AddGeometryColumn为已有的数据表增加一个地理几何数据字段；DropGeometryColumn删除一个地理数据字段的；SetSRID设置SRID值几何关系函数这类函数目前共有10个，分别是：Distance，Equals，Disjoint，Intersects，TouchesCrosses，Within，Overlaps，Contains，Relate几何 分析 定性数据统计分析pdf销售业绩分析模板建筑结构震害分析销售进度分析表京东商城竞争战略分析 函数这类函数目前共有12个，分别是：Centroid，Area，Lenth，PointOnSurface，Boundary，Buffer，ConvexHull，Intersection，SymDifference，Difference，GeomUnion，MemGeomUnion读写函数这类函数很多，主要是用于在各种数据类型之间的转换，尤其是在于Geometry数据类型与其他如字符型等数据类型之间的转换，函数名如AsText、GeomFromText等。4、Geo-SQL查询Selectc1.city_nameFromCitiesC1,RiversRWhereST_Overlaps(C1.geom,ST_Buffer(R.geom,3000))Selectc1.city_nameFromCitiesC1,(Selectgeomfromriverswherename='Alabama')asmWhereST_Overlaps(C1.geom,ST_Buffer(m,3000))Selectc1.city_nameFromCitiesC1,(SelectST_Buffer(geom,3000)asddfromriverswherename='Platte')asmWhereST_Overlaps(C1.geom,m.dd)=trueSelectc1.city_name,ST_Overlaps(C1.geom,m.dd)FromCitiesC1,(SelectST_Buffer(geom,3000)asddfromriverswherename='Platte')asm做一个点查询（查询州数据）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查询SELECTshengjie_region.name,shijie_region.nameFROMshengjie_region,shijie_regionwhereshijie_region.provincena=shengjie_region.nameandST_Within(ST_MakePoint(121.54,38.91),shengjie_region.geom)点查询某省份，并找到该省份的城市SELECTnameFROMshijie_regionwhereST_Within(ST_MakePoint(121.54,38.91),geom)SELECTshengjie_region.name,shijie_region.nameFROMshengjie_region,shijie_regionwhereST_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_region.geom)andST_Within(ST_MakePoint(121.54,38.91),shijie_region.geom)andST_Within(ST_MakePoint(121.54,38.91),xianjie_region.geom)SELECTnameFROMguodao_polylinewhereST_Within(ST_MakePoint(121.54,38.91),geom)点查询selectname,st_distance(ST_MAKEPOINT(116.561,40.276),geom)asdistancefromshengjie_regionwherename='天津市'orname='辽宁省'orname='北京市'selectst_point(63.573566,44.646244)fromdual;SELECTST_AsText(geom)aswktFROMshengjie_regionwherename='新疆维吾尔自治区'SELECTST_AsEWKT(geom)asbufferFROMshengjie_regionSELECTST_AsText(st_transform(st_setsrid(ST_Buffer(st_transform(st_setsrid(st_geomfromtext('"+wkt+"'),4326),2333),10800),2333),4326))FROMdualSELECTST_MakePoint(121.55223,38.86758)fromdual;SELECTst_distance(ST_MakePoint(121.55223,38.86758),geom)fromgongyuan_point;SELECTnameFROMgongyuan_pointwhereST_Within(ST_MakePoint(121.54,38.91),geom)SELECTST_SetSRID(ST_MakePoint(121.55223,38.86758),4326);SELECTname,st_distance(ST_MakePoint(121.55223,38.86758),geom)fromgongyuan_pointwherest_distance(ST_MakePoint(121.55223,38.86758),geom)<0.02;SELECTgid,name,st_setsrid(ST_MakePoint(121.55223,38.86758),4326)fromgongyuan_pointwherest_distance(ST_MakePoint(121.55223,38.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_Transform(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)fromdual;SELECTST_DWithin(ST_Transform(ST_GeomFromText('POINT(121.5522338.86758)',4326),26986),ST_Transform(ST_GeomFromText('POINT(121.5617140838.87784198)',4326),26986),1400)fromdual;SELECTname,st_distance(ST_MakePoint(121.55223,38.86758),geom)fromgongyuan_pointwherest_distance(ST_MakePoint(121.55223,38.86758),geom)<0.02orderbyst_distance(ST_MakePoint(121.55223,38.86758),geom);SELECTST_Transform(ST_GeomFromText('POINT(121.5522338.86758)',4326),26986)fromdualSELECTST_Transform(point(t.geom,26986),5144)fromgongyuan_pointtSELECTpoint(gongyuan_point.geom)fromgongyuan_pointSELECTname,gidFROMgongyuan_pointORDERBYgeom<->st_setsrid(st_makepoint(121.55223,38.86758),4326)LIMIT10;selectst_extent(geom)aswgs84fromgongyuan_point;selectgid,st_x(geom),st_y(geom),st_z(geom),POINT(geom)fromgongyuan_pointwherest_distance(ST_MakePoint(121.55223,38.86758),geom)<0.02;selectgid,st_x(geom),st_y(geom),st_z(geom),POINT(geom),st_srid(geom)fromgongyuan_pointwherest_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)fromgongyuan_pointwherest_distance(ST_MakePoint(121.55223,38.86758),geom)<0.02;selectST_distance_sphere(ST_MakePoint(121.55223,38.86758),geom)fromgongyuan_pointwherest_distance(ST_MakePoint(121.55223,38.86758),geom)<0.02;selectST_distance_sphere(ST_MakePoint(121.55223,38.86758),geom)fromgongyuan_pointwhereST_distance_sphere(ST_MakePoint(121.55223,38.86758),geom)<1000;selectST_distance_sphere(ST_MakePoint(121.55223,38.86758),geom)fromgongyuan_pointwhereST_distance_sphere(ST_MakePoint(121.55223,38.86758),geom)<1500;selectST_distance_sphere(ST_MakePoint(121.55223,38.86758),geom),ST_AsBinary(geom),ST_AsEWKT(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;selectname,ST_distance_sphere(ST_MakePoint(121.55223,38.86758),geom)fromdasha_pointwhereST_distance_sphere(ST_MakePoint(121.55223,38.86758),geom)<1500;selectname,ST_distance_sphere(ST_MakePoint(121.55223,38.86758),geom)fromdasha_pointwhereST_distance_sphere(ST_MakePoint(121.55223,38.86758),geom)<1500orderbyST_distance_sphere(ST_MakePoint(121.55223,38.86758),geom)limit1;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)limit1;SELECTnameFROMshengjie_regionwhereST_Within(ST_MakePoint(121.55223,38.86758),geom)SELECTnameFROMshijie_regionwhereST_Within(ST_MakePoint(121.55223,38.86758),geom)SELECTnameFROMxianjie_regionwhereST_Within(ST_MakePoint(121.55223,38.86758),geom)selectname,ST_distance_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)<3000orderbyST_distance_sphere(ST_MakePoint(121.55223,38.86758),geom)limit1;selectname,ST_distance_sphere(ST_MakePoint(121.55223,38.86758),geom),st_x(geom),st_y(geom)frombinguanjiudian_pointwhereST_distance_sphere(ST_MakePoint(121.55223,38.86758),geom)<3000orderbyST_distance_sphere(ST_MakePoint(121.55223,38.86758),geom)limit1;selectST_AsEWKT(ST_Difference(st_geomfromText('LINESTRING(11,23,34,31)'),st_geomfromText('LINESTRING(20,22,52,31)')))fromdual;selectST_AsEWKT(ST_Difference(st_geomfromText('POLYGON((11,23,34,31,11))'),st_geomfromText('POLYGON((20,22,52,13,20))')))fromdual;selectGeometryType(st_geomfromText('MULTILINESTRING((11,23,34,31,21,11),(12,23,45))'))fromdual;SELECTname,st_area(geom)asareafromshengjie_regionORDERBYareaDESCLIMIT1;SELECTst_Length2d(st_GeomFromText('MultiLineString((11,22,33),(44,55))'))fromdual;SELECTgid,name,st_astext(geom)ASLinestring,st_length(geom)FROMguodao_polylinewherename='东北路';SELECTname,st_astext(geom)FROMguodao_polylinewheregid='152415';SELECTgid,st_astext(geom)ASMULTIPOINTFROMguodao_polylineWHEREgid=152415;SELECTst_astext(geom)ASMLINESTRINGFROMguodao_polyline;SELECTnameFROMguodao_polylinewheregid='152415';SELECTST_AsEWKT(ST_Line_Interpolate_Point(the_line,0.5))FROM(SELECTST_GeomFromEWKT('LINESTRING(123,456,678)')asthe_line)Asfoo;SELECTST_AsText(ST_Line_Interpolate_Point(foo.the_line,ST_Line_Locate_Point(foo.the_line,ST_GeomFromText('POINT(43)'))))FROM(SELECTST_GeomFromText('LINESTRING(12,45,67)')Asthe_line)Asfoo;SELECTST_AsText(ST_Line_SubString(ST_GeomFromText('LINESTRING(2550,100125,150190)'),0.333,0.666));SELECTST_AsText(house_loc)Asas_text_house_loc,startstreet_num+CAST((endstreet_num-startstreet_num)*ST_Line_Locate_Point(street_line,house_loc)Asinteger)Asstreet_numFROM(SELECTST_GeomFromText('LINESTRING(12,34)')Asstreet_line,ST_MakePoint(x*1.01,y*1.03)Ashouse_loc,10Asstartstreet_num,20Asendstreet_numFROMgenerate_series(1,3)xCROSSJOINgenerate_series(2,4)Asy)AsfooWHEREST_DWithin(street_line,house_loc,0.2);SELECTST_AsText(the_geom)FROM(SELECTST_LocateAlong(ST_GeomFromText('MULTILINESTRINGM((123,342,943),(123,545))'),3)Asthe_geom)Asfoo;SELECTST_AsText(the_geom)FROM(SELECTST_LocateBetween(ST_GeomFromText('MULTILINESTRINGM((123,342,943),(123,545))'),1.5,3)Asthe_geom)Asfoo;SELECTST_AsEWKT((ST_Dump(the_geom)).geom)FROM(SELECTST_LocateBetweenElevations(ST_GeomFromEWKT('LINESTRING(126,45-1,789)'),6,9)Asthe_geom)AsfooSELECTST_InterpolatePoint('LINESTRINGM(000,10020)','POINT(55)');SELECTST_AsText(ST_AddMeasure(ST_GeomFromEWKT('MULTILINESTRINGM((104,204,404),(104,204,404))'),10,70))Asewelev;