PostGis学习笔记

– 文本方式查看几何数据
SELECT ST_AsText(geom)FROM nyc_streets
WHERE name =‘Avenue O’;

– 计算紧邻的街区
SELECT name,ST_GeometryType(geom)
FROM nyc_streets
WHERE ST_DWithin(
geom,ST_GeomFromText(‘LINESTRING(586782 4504202,586864 4504216)’,26918),0.1);

– 计算各个社区的面积
SELECT name, ST_Area(geom), ST_GeometryType(geom) FROM nyc_neighborhoods
WHERE boroname =‘Manhattan’;

– 计算曼哈顿的面积 单位平方米
SELECT Sum(ST_Area(geom))FROM nyc_neighborhoods WHERE boroname =‘Manhattan’;

– 最西面的地铁站的信息
SELECT ST_X(geom), name
,ST_GeometryType(geom)
FROM nyc_subway_stations
ORDER BY ST_X(geom)
LIMIT 1;

– 街道和哪些社区有交集

SELECT name, boroname
FROM nyc_neighborhoods WHERE ST_Intersects(
geom,
ST_GeomFromText(‘LINESTRING(586782 4504202,586864 4504216)’,26918));

– 街道附近的人口总数
SELECT popn_total
FROM nyc_census_blocks
WHERE ST_DWithin(
geom,
ST_GeomFromText(‘LINESTRING(586782 4504202,586864 4504216)’,26918),
50);

– 查询社区内的地铁站
SELECT s.name,s.routes FROM nyc_subway_stations As s JOIN nyc_neighborhoods As n ON ST_Contains(n.geom,s.geom)
WHERE n.name =‘Little Italy’

– 哪个社区的人口密度最高
SELECT
n.name,
Sum(c.popn_total)/(ST_Area(n.geom)/1000000.0) As popn_per_sqkm FROM nyc_census_blocks As c
JOIN nyc_neighborhoods As n
ON ST_Intersects(c.geom,n.geom)
GROUP BY n.name,n.geom
ORDER BY popn_per_sqkm DESC LIMIT 5;

–两点之间的直线距离
SELECT ST_Distance(
(select geom from nyc_subway_stations where id=6),
(select geom from nyc_subway_stations where id =7)
);