一些常用的postgis数据库的查询sql（经测试全部可用）

现在的位置: 首页 > 综合 > 正文

一些常用的postgis数据库的查询sql（经测试全部可用）

2013年06月05日 ⁄ 综合 ⁄ 共 8570字 ⁄ 字号小中大 ⁄ 评论关闭

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;

【上篇】女生日记读后感400字
【下篇】七个受用一生的心理寓言

作者: mikado

该日志由 mikado 于11年前发表在综合分类下，最后更新于 2013年06月05日.
转载请注明: 一些常用的postgis数据库的查询sql（经测试全部可用） | 学步园 +复制链接

抱歉!评论已关闭.

学步园

一些常用的postgis数据库的查询sql（经测试全部可用）

作者: mikado

书签

最新文章New

本站推荐

返回首页