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文件，openStep4：点击即可导入数据Step5：假如数据中字段存在汉字话，能够点击进行编码设置2、SHP导入POSTGIS数据库方法1：经过命令行直接导出shppgsql2shp -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：经过命令行直接导出shpStep1：在开始菜单中，运行 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)=trueSelect 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_GeomFromT。