[转]一些常用的postgis数据库的查询sql(经测试全部可用)

标签: | 发表时间:2013-09-28 00:01 | 作者:zeo112140
出处:http://blog.csdn.net/zeo112140
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)




SELECT name FROM shijie_region where ST_Within(ST_MakePoint(121.54, 38.91), geom)




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)






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)




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 from shengjie_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_region


SELECT ST_AsText(st_transform(st_setsrid(ST_Buffer(st_transform(st_setsrid(st_geomfromtext('" + wkt + "'), 4326), 2333), 10800), 2333), 4326)) FROM dual


SELECT ST_MakePoint(121.55223, 38.86758) from dual;


SELECT st_distance(ST_MakePoint(121.55223, 38.86758),geom) from gongyuan_point;


SELECT name FROM gongyuan_point where ST_Within(ST_MakePoint(121.54, 38.91), geom)




http://192.168.0.200:8082/geoserver/scj/wms?service=WMS&version=1.1.0&request=GetMap&layers=china&styles=&bbox=72.78242,17.67655,141.157437,55.433468&width=597&height=330&srs=EPSG:3415&format=application%2Fopenlayers




http://192.168.0.200:8082/geoserver/scj/wms?service=WMS&version=1.1.0&request=GetMap&layers=china&styles=&bbox=73.1794815063477,17.9404830932617,135.405303955078,53.7387809753418&width=573&height=330&srs=EPSG:4326&format=application%2Fopenlayers


SELECT ST_SetSRID(ST_MakePoint(121.55223, 38.86758),4326);


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 ;




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 ;




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));


select gid,POINT(geom) from gongyuan_point where st_distance(ST_MakePoint(121.55223, 38.86758),geom)< 0.02 ;


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));




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;


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;




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);




SELECT ST_Transform(ST_GeomFromText('POINT(121.55223 38.86758)',4326),26986) from dual




SELECT ST_Transform(point(t.geom,26986),5144) from gongyuan_point t




SELECT point(gongyuan_point.geom) from gongyuan_point




SELECT name, gid FROM gongyuan_point ORDER BY geom <-> st_setsrid(st_makepoint(121.55223,38.86758),4326) LIMIT 10;




select st_extent(geom) as wgs84 from gongyuan_point;




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 ;






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 ;






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 ;




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 ;




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 ;




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 ;




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 ;




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 ;




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 ;




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 ;




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;




SELECT name FROM shengjie_region where  ST_Within(ST_MakePoint(121.55223, 38.86758), geom)


SELECT name FROM shijie_region where  ST_Within(ST_MakePoint(121.55223, 38.86758), geom)


SELECT name FROM xianjie_region where  ST_Within(ST_MakePoint(121.55223, 38.86758), geom)




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 ;




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 ;




http://192.168.0.200:8083/sbs/router?appKey=00000003&timestamp=1380176473&method=msg.information.get&v=2.0&lng=121.55223&lat=38.86758






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;






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;




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;




SELECT name,st_area(geom) as area from shengjie_region ORDER BY area DESC LIMIT 1;


SELECT st_Length2d(st_GeomFromText('MultiLineString((1 1,2 2,3 3),(4 4,5 5))')) from dual;






SELECT gid,name ,st_astext(geom) AS Linestring,st_length(geom) FROM guodao_polyline where name='东北路';




SELECT name,
st_astext(geom) FROM guodao_polyline where gid='152415';




SELECT gid, st_astext (geom) 
AS MULTIPOINT
FROM guodao_polyline
WHERE gid = 152415; 






SELECT st_astext(geom) 
AS MLINESTRING
FROM guodao_polyline;


























SELECT name FROM guodao_polyline where gid='152415';




SELECT ST_AsEWKT(ST_Line_Interpolate_Point(the_line, 0.5))
FROM (SELECT ST_GeomFromEWKT('LINESTRING(1 2 3, 4 5 6, 6 7 8)') as the_line) As foo;




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;




SELECT ST_AsText(ST_Line_SubString(ST_GeomFromText('LINESTRING(25 50, 100 125, 150 190)'), 0.333, 0.666));






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);








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;






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;






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






SELECT ST_InterpolatePoint('LINESTRING M (0 0 0, 10 0 20)', 'POINT(5 5)');






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;



























































































作者:zeo112140 发表于2013-9-27 16:01:17 原文链接
阅读:13 评论:0 查看评论

相关 [postgis 数据库 sql] 推荐:

[转]一些常用的postgis数据库的查询sql(经测试全部可用)

- - 小鸥的博客
select name,st_distance(ST_MAKEPOINT(116.561, 40.276),geom) as distance from shengjie_region where name='天津市' or name ='辽宁省' or name='北京市'. SELECT ST_AsText(geom) as wkt FROM shengjie_region where name = '新疆维吾尔自治区'.

Google Cloud SQL 云端数据库开测

- xcv58 - 谷奥——探寻谷歌的奥秘
很多Google App Engine用户都想要一个简单而传统的数据驱动应用,于是今天Google宣布开放测试Google Cloud SQL. 这是一个接近传统数据库的云端服务,并可与App Engine整合:. 不需要管理员和维护,Google会帮你搞定一切. 高可靠性和可用性,你的数据会在多个数据中心之间同步,即便一个出问题也不会影响使用.

SQL Server 数据库巡检脚本

- - CSDN博客数据库推荐文章
select '现在没有阻塞和死锁信息' as message. select '引起数据库死锁的是: '+ CAST(@bl AS VARCHAR(10)) + '进程号,其执行的SQL语法如下'. select '进程号SPID:'+ CAST(@spid AS VARCHAR(10))+ '被' + '进程号SPID:'+ CAST(@bl AS VARCHAR(10)) +'阻塞,其当前进程执行的SQL语法如下'.

Vertica数据库sql操作备忘

- - 阿辉的空间
删除主键(Vertica数据库的主键值并不是唯一的):. 类别: Database  查看评论.

用SQL语言管理Oracle数据库

- - CSDN博客推荐文章
1,查看数据库的基本属性:. SELECT dbid 数据库编号,name 数据库名称,db_unique_name 全局名称,created 创建时间,log_mode 归档方式,open_mode 访问方式,platform_name 版本类型 FROM v$database;. 2,查看所有数据库对象的类别和大小:.

SQL监控:mysql及mssql数据库SQL执行过程监控审计

- - Seay's blog 网络安全博客
   最近生活有很大的一个变动,所以博客也搁置了很长一段时间没写,好像写博客已经成了习惯,搁置一段时间就有那么点危机感,心里总觉得不自在. 所以从今天起还是要继续拾起墨笔(键盘),继续好好维护这个博客,写出心里最真实的想法,写出平时接触到的一些人和事以及一些新的技术. 当然写博客也不是单纯的为了记录,也想通过博客来结交更多的朋友,今天在公司图书馆看到一句话大致说的是“在今天这个年代,已经很难等到三顾茅庐,诸葛亮也需要博客、微博和影响力”,在一年前就曾想过写一篇关于怎样通过博客来提高个人影响力的文章,我会尽快在这个月抽时间写出来,另外最近也看了几本书,过些时候给大家推荐.

Google推出云端数据库:Google Cloud SQL

- Johnny - 36氪
昨天Oracle推出了数据库即服务和Java平台即服务,现在处于开发者预览阶段,今天,Google在博客中宣布,推出自己的基于MySQL的云端数据库:Google Cloud SQL. 目前只支持Google App Engine. 下面是Google在博客中提到的一些功能:. 不需要维护和管理 – 我们帮你管理数据库.

MySQL 数据库性能优化之SQL优化

- - OurMySQL
注:这篇文章是以 MySQL 为背景,很多内容同时适用于其他关系型数据库,需要有一些索引知识为基础. IO永远是数据库最容易瓶颈的地方,这是由数据库的职责所决定的,大部分数据库操作中超过90%的时间都是 IO 操作所占用的,减少 IO 次数是. SQL 优化中需要第一优先考虑,当然,也是收效最明显的优化手段.

jdbc测试mysql数据库sql预解析(绑定变量)

- - CSDN博客推荐文章
jdbc测试mysql数据库sql预解析(绑定变量).         用习惯了oracle,学习mysql,想测试一下mysql绑定变量的效果. 以前看网上介绍大部份都说mysql没有sql共享池的概念,所以也不存在sql预解析或绑定变量的说法.         今天测试了一下(通过网络抓包和看服务器端sql日志的方法),发现mysql还是有sql预解析的实现.