PostGIS and PostgreSql 功能說明

環域
select * from "GISpoint" where st_contains (buffer(st_geomfromtext('Point(2 2)'),1.0),"gisPoints");
select * from "GISpoint" where st_contains (buffer(st_geomfromtext('Point(2 2)'),1.1),"gisPoints");
select * from "GISpoint" where st_within "gisPoints“,buffer(st_geomfromtext('Point(2 2)'),1.1))

交集
select "CountyNameCht","TownNameCht","CountyNameEng","TownNameEng","CountyId","TownId"
from "CityTown" where st_intersects("geom",buffer(st_geomfromtext('Point({0} {1})'),{2})

吸附
select astext("geom"),line_interpolate_point("geom", line_locate_point("geom", PointFromText('Point(322655 2778900)')))
from "RoadSpeed"
order by Distance("geom",PointFromText('Point(322655 2778900)')) limit 1

坐標轉換
spatial_ref_sys
select * from "spatial_ref_sys"
select * from "spatial_ref_sys" where "srid"=4326 (WGS84)
select * from "spatial_ref_sys" where "srid"=3828 (TWD67 -121)
select * from "spatial_ref_sys" where "srid"=3826 (TWD97 -121)

select "Id", "TownId","TownCode","CountyNameCht", "TownNameCht","CountyNameEng", "TownNameEng", "CountyId",
st_transform(setsrid(geom,4326),3826) as geom
into "CityTown_67“ from "CityTown“
CONSTRAINT enforce_dims_geom CHECK (st_ndims(geom) = 2)

由空間資訊轉換坐標字串
select st_astext(geom) from \"BusPathLine\"

算距離
st_distance(geom,st_geomfromtext('Point(121.12341 24.23432)')