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

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;