本文着重介绍Point类型的查询操作，以下操作均为二维，不讨论三维数据。

创建点

      点的WKT表述如POINT(116.39088 39.90763)，在Postgis中创建一个点几个方式：

ST_Point、ST_MakePoint：

--用法：ST_Point(float x_lon, float y_lat);

--输出：0101000000452A8C2D04195D402A6F47382DF44340

SELECT ST_Point(116.39088,39.90763)

      上面虽然创建了点，但是SRID却是0（unknown），可以通过ST_SRID查看。下面方式设置坐标系为4326：

--用法：ST_Point(float x_lon, float y_lat);

--输出：0101000020E6100000452A8C2D04195D402A6F47382DF44340

SELECT ST_SetSRID(ST_Point(116.39088,39.90763),4326)

      坐标顺序是经度（或x）在前，纬度（或y）在后。ST_MakePoint和ST_Point一样，ST_MakePoint可以快速高效的创建一个点。

ST_GeometryFromText、ST_PointFromText：

--用法1：geometry ST_GeometryFromText(text WKT);

--用法2：geometry ST_GeometryFromText(text WKT, integer srid);

--输出：0101000020E6100000452A8C2D04195D402A6F47382DF44340

SELECT ST_GeomFromText('POINT(116.39088 39.90763)',4326);

      ST_GeometryFromText和ST_GeomFromText一样，根据WKT描述的对象返回一个具体的geometry对象，WKT可以是POLYGON、MULTIPOLYGON、LINESTRING等。在这里它是一个点。如果要指明创建一个点，可以用ST_PointFromText：

--用法1：geometry ST_PointFromText(text WKT);

--用法2：geometry ST_PointFromText(text WKT, integer srid);

--输出：0101000020E6100000452A8C2D04195D402A6F47382DF44340

SELECT ST_PointFromText('POINT(116.39088 39.90763)',4326);

      未指定srid时，创建的geomtry默认的srid是0。

ST_GeomFromGML、ST_GMLToSQL：

--geometry ST_GeomFromGML(text geomgml);

--geometry ST_GeomFromGML(text geomgml, integer srid);

--输出：0101000020E6100000452A8C2D04195D402A6F47382DF44340

SELECT ST_GeomFromGML('

    <gml:Point srsName="EPSG:4326">

    <gml:coordinates>116.39088,39.90763</gml:coordinates>

    </gml:Point>

');

      属性srsName会被读取。也可以在参数中指定srid：

SELECT ST_GeomFromGML('

    <gml:Point>

<gml:coordinates>116.39088,39.90763</gml:coordinates>

    </gml:Point>

',4326);

     ST_GeomFromGML参数geomgml只能是GML的geometry部分，不是整个GML文档。目前支持 GML 2.1.2、 3.1.1、 3.2.1等版本。ST_GMLToSQL是ST_GeomFromGML的别名，用法一样：

--输出：0101000020E6100000452A8C2D04195D402A6F47382DF44340

SELECT ST_GMLToSQL('

    <gml:Point>

<gml:coordinates>116.39088,39.90763</gml:coordinates>

    </gml:Point>

',4326);

ST_GeomFromGeoJSON：

--geometry ST_GeomFromGeoJSON(text geomjson);

--输出：0101000020E6100000452A8C2D04195D402A6F47382DF44340

SELECT ST_SetSRID(ST_GeomFromGeoJSON('{

    "type":"Point",

    "coordinates":[116.39088,39.90763]

}'),4326);

      与ST_GeomFromGML一致，参数geomjson只能是geometry描述。ST_GeomFromGeoJSON没有srid参数，而标准GeoJSON geometry对象中不含坐标系，所以需要ST_SetSRID用设置坐标系。 

ST_GeomFromKML：

--geometry ST_GeomFromKML(text geomkml);

--输出：0101000020E6100000452A8C2D04195D402A6F47382DF44340

SELECT ST_SetSRID(ST_GeomFromKML('

    <Point>

        <coordinates>116.39088,39.90763</coordinates>

    </Point>

'),4326);

      和ST_GeomFromGeoJSON一致，参数geomkml只能是geometry描述。ST_GeomFromGeoJSON没有srid参数，所以需要ST_SetSRID用设置坐标系。

      其他还有一些二进制、EWKT的读取类似，这里不做介绍。

输出

ST_X、ST_Y、ST_Z等：

--float ST_X(geometry a_point);

--输出：116.39088

SELECT ST_X(ST_GeomFromText('POINT(116.39088 39.90763)',4326));

--输出：Null

SELECT ST_Z(ST_GeomFromText('POINT(116.39088 39.90763)',4326));

      a_point是一个POINT对象，ST_X输出X坐标。

GeometryType：

--text GeometryType(geometry geomA);

--输出：POINT

SELECT GeometryType(ST_GeomFromText('POINT(116.39088 39.90763)',4326));

ST_GeometryType：

--text ST_GeometryType(geometry g1);

--输出：ST_Point

SELECT ST_GeometryType(ST_GeomFromText('POINT(116.39088 39.90763)',4326));

ST_FlipCoordinates交换X和Y坐标：

--geometry ST_FlipCoordinates(geometry geom);

--输出：POINT(39.90763 116.39088)

SELECT ST_AsText(ST_FlipCoordinates(ST_GeomFromText('POINT(116.39088 39.90763)',4326)))

--输出：POINT ZM (39.90763 116.39088 3 6)

SELECT ST_AsText(ST_FlipCoordinates(ST_GeomFromText('POINT(116.39088 39.90763 3 6)',4326)))

      除了POINT外，还支持其他的LINESTRING等。

ST_Transform坐标转换：

--4326转4490。4326与4490都是地理坐标系，坐标系差异很小，所以转出的该点差别小

--输出：POINT(116.39088 39.90763)

-- SELECT ST_AsText(ST_Transform(ST_GeomFromText('POINT(116.39088 39.90763)',4326),4490))

--4326转3857。投影

--输出：POINT(12956573.494581 4852528.38789462)

SELECT ST_AsText(ST_Transform(ST_GeomFromText('POINT(116.39088 39.90763)',4326),3857))

其他

--输出：{"type":"Point","coordinates":[116.39088,39.90763]}

SELECT ST_AsGeoJSON(ST_GeomFromText('POINT(116.39088 39.90763)',4326));

--输出：<Point><coordinates>116.39088,39.907629999999997</coordinates></Point>

SELECT ST_AsKML(ST_GeomFromText('POINT(116.39088 39.90763)',4326));

--输出：<gml:Point srsName="EPSG:4326"><gml:coordinates>116.39088,39.907629999999997</gml:coordinates></gml:Point>

SELECT ST_AsGML(ST_GeomFromText('POINT(116.39088 39.90763)',4326));

--输出：POINT(116.39088 39.90763)

SELECT ST_AsText(ST_GeomFromText('POINT(116.39088 39.90763)',4326));

--integer ST_CoordDim(geometry geomA);

--输出：2

SELECT ST_CoordDim(ST_GeomFromText('POINT(116.39088 39.90763)',4326));

以上就是一些关于POINT的WKT操作

///////////////////////////////////////////////////////////////////////////////////////////////

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;