PostGis常用sql

最新推荐文章于 2024-03-05 10:53:22 发布
阳光__小好
最新推荐文章于 2024-03-05 10:53:22 发布
阅读量1.4k
点赞数 6
文章标签：数据库 python java mysql 大数据
本文链接：https://blog.csdn.net/qq_30665009/article/details/124487426
版权
select count(*) from nyc_census_blocks

SELECT * FROM spatial_ref_sys WHERE srid = 26918;

--查看纽约市所有社区的名字
select name from nyc_neighborhoods;
select * from nyc_neighborhoods;

--查看布鲁克林所有社区的名字
select name from nyc_neighborhoods where boroname = 'Brooklyn';

--布鲁克林所有社区的名字里各有多少个字母
select name,char_length(name) from nyc_neighborhoods where boroname = 'Brooklyn';

--布鲁克林所有社区名字的平均字母数和字母数的标准差是多少
SELECT avg(char_length(name)), stddev(char_length(name)) FROM nyc_neighborhoods WHERE boroname = 'Brooklyn';

--基于各个行政区进行分组，纽约市各个行政区的所有社区名字的平均字母数是多少
SELECT boroname, avg(char_length(name)), stddev(char_length(name)) FROM nyc_neighborhoods GROUP BY boroname;



select *,ST_AsText(geom) from nyc_census_blocks;

--纽约市的总人口是多少？
select sum(popn_total) population from nyc_census_blocks;

--布朗克斯(Bronx)行政区的总人口是多少
select sum(popn_total) from nyc_census_blocks where boroname = 'The Bronx';

--对每个行政区来说，白人占该行政区总人口的百分比是多少？
select boroname,100 * sum(popn_white) / sum(popn_total) as white_pct from nyc_census_blocks group by boroname;

--创建表geometries
CREATE TABLE geometries (name varchar, geom geometry);

--插入数据
INSERT INTO geometries VALUES
  ('Point', 'POINT(0 0)'),
  ('Linestring', 'LINESTRING(0 0, 1 1, 2 1, 2 2)'),
  ('Polygon', 'POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'),
  ('PolygonWithHole', 'POLYGON((0 0, 10 0, 10 10, 0 10, 0 0),(1 1, 1 2, 2 2, 2 1, 1 1))'),
  ('Collection', 'GEOMETRYCOLLECTION(POINT(2 0),POLYGON((0 0, 1 0, 1 1, 0 1, 0 0)))');
--查询，geom字段以字符号串形式显示
SELECT name, ST_AsText(geom) FROM geometries;
select * from geometries;

--查询数据库中所有空间数据表的描述信息
SELECT * FROM geometry_columns;


--nyc数据库的表没有指定26918的srid，那该怎么办呢？通过更新表很容易修复：
SELECT UpdateGeometrySRID('nyc_neighborhoods','geom',26918);

--查询几何图形类型、维数、空间参考标识码
select name,ST_GeometryType(geom),ST_NDims(geom),ST_SRID(geom) from geometries;

-- 查询一个点
SELECT ST_AsText(geom) FROM geometries WHERE name = 'Point';

--读取一个点图形的坐标值
select ST_X(geom),ST_Y(geom) from geometries where name = 'Point';

--纽约市地铁站(nyc_subway_stations)表是一个以点表示的数据集。
--以下SQL查询将返回一个点图形数据(在ST_AsText列中)
select name,ST_AsText(geom) from nyc_subway_stations limit 3; 

--查询将返回一个线串图形的信息(在ST_AsText列中)
select st_asText(geom) from geometries where name = 'Linestring';

--线串的长度
select st_length(geom) from geometries where name = 'Linestring';

--查询将返回两个多边形图形的信息(在ST_AsText列中)
select st_asText(geom) from geometries where name like 'Polygon%';

--计算多边形的面积
--请注意，带孔的多边形的面积是多边形外环的面积(10 x 10正方形)减去孔的面积(1 x 1正方形)
select name,ST_Area(geom) from geometries where name like 'Polygon%';

--返回多边形中环的数量(通常为1个，其他是孔)
select name,ST_NRings(geom) from geometries where name like 'Polygon%';

--查询几何图形集合
select name,ST_asText(geom) from geometries where name = 'Collection';

--几何图形转换为geoJson格式
select name,ST_asGeoJson(geom) from geometries

--以下SQL查询展示了一个WKB表示形式的示例(将二进制输出转换为ASCII格式以进行打印时，需要调用encode())
SELECT encode(ST_AsBinary(ST_GeometryFromText('LINESTRING(0 0,1 0)')),'hex');

--由于WKT和WKB是在SFSQL规范中定义的，因此它们不能处理3维或4维的几何图形。对于这些情况，PostGIS定义了Extended Well Known Text(EWKT)和Extended Well Known Binary(EWKB)格式以用于处理3维或4维的几何图形。
--它们提供了与WKT和WKB相同的格式化功能，并且是在增加了维度的情况下。
--以下是WKT中三维(3D)线串示例
SELECT ST_AsText(ST_GeometryFromText('LINESTRING(0 0 0,1 0 0,1 1 2)'));

--下面是一个使用GML输入和输出JSON的示例
SELECT ST_AsGeoJSON(ST_GeomFromGML('<gml:Point><gml:coordinates>1,1</gml:coordinates></gml:Point>'));

--将double类型转换为文本字符串类型
SELECT 0.9::text;

--以下SQL语句将一个WKT字符串转换成一个几何图形(geometry)
SELECT 'POINT(0 0)'::geometry;

--关于使用类型转换语法创建几何图形，需要注意一点：除非指定SRID，否则将得到一个包含未知SRID的几何图形。
--可以使用EWKT形式指定SRID，该形式在前面包含一个SRID：
SELECT 'SRID=4326;POINT(0 0)'::geometry;


select * from nyc_neighborhoods
select * from nyc_census_blocks

--'West Village'社区(neighborhood)的面积是多少？
--注意:面积以平方米为单位。要得到一个以公顷为单位的面积，需要再对其除以10000；要得到一个以英亩为单位的面积，需要对其除以4047
select name,st_area(geom) from nyc_neighborhoods where name = 'West Village';

--曼哈顿(Manhattan)行政区的面积是多少英亩？(提示：nyc_census_blocks和nyc_neighborhoods中都有boroname - rorough name - 行政区名)
select Sum(st_area(geom)) / 4047 as manhattan_area from nyc_neighborhoods where boroname = 'Manhattan';


--将MultiPolygon几何图形转换为简单的多边形，因此，我们使用ST_GeometryN()从每个集合中提取第一个多边形。
select gid,st_asText(ST_GeometryN(geom,1)),st_asText(geom) from nyc_census_blocks;
--纽约市(New York City)有多少个人口普查块(census blocks)多边形里有孔洞？
SELECT count(*) FROM nyc_census_blocks WHERE ST_NumInteriorRings(ST_GeometryN(geom,1)) > 0;

select * from nyc_streets
--id是18813等街道的长度(米)
select id,name,st_length(geom) from nyc_streets where id = 18813;
--纽约市(New York)的街道总长度(公里)是多少？(提示：空间数据的测量单位是米，每公里有1000米)
select Sum(ST_Length(geom)) / 1000 from nyc_streets 

--'Columbus Cir'(哥伦布圆环——纽约曼哈顿区的一个地标)有多长？
select name,st_length(geom) from nyc_streets where name = 'Columbus Cir';

--West Village社区边界的JSON表示是怎样的？
select st_asGeoJson(geom) from nyc_neighborhoods where name = 'West Village';

--West Village社区多多边形(MultiPolygon)中有多少个多边形？
select ST_NumGeometries(geom) from nyc_neighborhoods where name = 'West Village';

--按类型(type)列出纽约市街道长度是多少？
select type,SUM(ST_Length(geom)) as length from nyc_streets group by type order by length desc;

--以字符串形式查询空间字段数据
select st_asText('0101000020266900000EEBD4CF27CF2141BC17D69516315141')
--nyc_subway_stations表中检索点数据，我们只选"Broad St"的条目
SELECT name, geom, ST_AsText(geom) FROM nyc_subway_stations WHERE name = 'Broad St';
--将几何图形表示数据插入ST_Equals()进行测试：
SELECT id,name,st_asText(geom) FROM nyc_subway_stations WHERE ST_Equals(geom, '0101000020266900000EEBD4CF27CF2141BC17D69516315141');


--让我们以宽街地铁站(Broad Street subway station)为例，使用ST_Intersects()函数确定其所在社区：
SELECT name, ST_AsText(geom) FROM nyc_subway_stations WHERE name = 'Broad St';
select name, boroname from nyc_neighborhoods where ST_Intersects(geom,ST_GeomFromText('POINT(583571.905921312 4506714.34119218)',26918))

--ST_Distance(geometry A, geometry B)计算两个几何图形之间的最短距离，并将其作为浮点数返回。这对于实际报告几何图形之间的距离非常有用。
SELECT ST_Distance(ST_GeometryFromText('POINT(0 5)'),ST_GeometryFromText('LINESTRING(-2 2, 2 2)'));

--使用我们的宽街地铁站(Broad Street subway station)，我们可以找到地铁站附近(10米内)的街道
select name from nyc_streets where ST_DWithin(geom,ST_GeomFromText('POINT(583571.905921312 4506714.34119218)',26918),10)

--名为"Atlantic Commons"的街道的geometry值是什么？
select st_asText(geom) from nyc_streets where name = 'Atlantic Commons';

--Atlantic Commons(大西洋公地)位于哪个社区(neighborhood)和行政区(borough)？
--注意：嘿，为什么要将"MULTILINESTRING"变成"LINESTRING"呢？因为在空间上，它们描述的是相同的形状。
--更重要的是，我们还对坐标进行了四舍五入，以使它们更易于阅读，这实际上改变了结果：我们现在不能使用ST_Touches()方法来找出哪些道路连接Atlantic Commons，因为坐标不再与原来的坐标完全相同。
select name, boroname from nyc_neighborhoods where ST_Intersects(geom,ST_GeomFromText('LINESTRING(586781.701577724 4504202.15314339,586863.51964484 4504215.9881701)',26918))

--Atlantic Commons与哪些街道相连？
select name from nyc_streets where ST_DWithin(geom,ST_GeomFromText('LINESTRING(586781.701577724 4504202.15314339,586863.51964484 4504215.9881701)',26918),0.1);

--大约有多少人住在Atlantic Commons上(距离Atlantic Commons50米以内)？
select sum(popn_total) from nyc_census_blocks where ST_DWithin(geom,ST_GeomFromText('LINESTRING(586781.701577724 4504202.15314339,586863.51964484 4504215.9881701)',26918),50);


--PostGIS教程十一：空间索引

--删除索引
drop index nyc_census_blocks_geom_idx;

--查找宽街站(Broad Street)范围内的块ID
SELECT blocks.blkid FROM nyc_census_blocks blocks JOIN nyc_subway_stations subways ON ST_Contains(blocks.geom, subways.geom) WHERE subways.name = 'Broad St';

--添加空间索引
--注意：USING GIST子句告诉PostgreSQL在构建索引时使用generic index structure(GIST-通用索引结构)。
--创建索引时，如果收到类似错误：ERROR:index row requires 11340 bytes，maximum size is 8911，则可能是因为没有添加USING GIST子句。
CREATE INDEX nyc_census_blocks_geom_idx ON nyc_census_blocks USING GIST (geom);

--对"West Village"社区人口的纯空间索引查询与更精确的查询进行比较。使用&&操作符的纯索引查询如下所示：
--要使用索引执行边界框搜索(即纯索引查询-Index only Query-没有过滤器)，需要使用"&&"运算符。对于几何图形，&&运算符表示"边界框重叠或接触"(纯索引查询)，就像对于数字，"="运算符表示"值相同"。
SELECT Sum(popn_total) FROM nyc_neighborhoods neighborhoods JOIN nyc_census_blocks blocks ON neighborhoods.geom && blocks.geom WHERE neighborhoods.name = 'West Village';
--现在，让我们使用更精确的ST_Intersects函数执行相同的查询：
--结果数量低得多！第一个查询汇总与社区(neighborhood)关于边界框相交的每个人口统计块(census block)；第二个查询仅汇总了与该社区几何图形本身相交的人口统计块。
SELECT Sum(popn_total) FROM nyc_neighborhoods neighborhoods JOIN nyc_census_blocks blocks ON ST_Intersects(neighborhoods.geom,blocks.geom) WHERE neighborhoods.name = 'West Village';

--ANALYZE命令要求PostgreSQL遍历该表并更新用于查询操作而估算的内部统计信息。
ANALYZE nyc_census_blocks;

--根据需要，可以单独执行清理和分析。发出VACUUM命令不会更新数据库统计信息；同样，执行ANALYZE命令也不会清理未使用的表空间。这两个命令都可以针对整个数据库、单个表或单个列运行。
VACUUM ANALYZE nyc_census_blocks;


--*************************************************************PostGIS教程十二：投影数据*************************************************************
--使用ST_SRID(geometry)函数确认数据的SRID：
select ST_SRID(geom) from nyc_streets limit 1;

-- “26918"的定义是什么？正如我们在加载纽约数据那一部分中看到的，该定义包含在spatial_ref_sys表中。事实上，有两个定义。"well-known text"(WKT)定义在srtext列中，"proj.4"格式定义在proj4text列。
select * from spatial_ref_sys where srid = 26918;

-- 实际上，对于内部PostGIS投影的计算，依据的是proj4text列的内容。以下是26918投影对应的proj4text列的内容
-- 实际上，srtext和proj4text列都很重要：srtext列由GeoServer、uDig和FME等外部程序使用；proj4text列由内部程序使用。
SELECT proj4text FROM spatial_ref_sys WHERE srid = 26918;

--综合起来，坐标和SRID(严谨的说应该是空间参考系统)一起定义了地球上的一个位置。没有SRID，坐标只是一个抽象而没有实际意义的概念。
--“笛卡尔”坐标平面被定义为放置在地球表面的“平面”坐标系。由于PostGIS函数在这样的坐标系统上工作，因此关于两个几何图形的比较的操作都要基于同一SRID。
--如果输入具有不同SRID的几何图形，则会得到错误
--注意：空间索引是基于存储的几何图形的SRID构建的。如果在不同的SRID中进行比较，则通常不使用空间索引。最佳做法是为数据库中的所有表选择一个SRID。仅在向外部程序读取或写入数据时使用转换函数将数据转换为基于指定SRID的数据。
SELECT ST_Equals(ST_GeomFromText('POINT(0 0)', 4326),ST_GeomFromText('POINT(0 0)', 26918));

--将"Broad St(宽街)"地铁站的坐标转换为地理坐标：
select ST_AsText(st_transform(geom,4326)),St_asText(geom) from nyc_subway_stations where name = 'Broad St';

--若要查看表的SRID，请查询数据库的geometry_columns视图表：
SELECT f_table_name AS name, srid FROM geometry_columns;


--然而，如果你知道坐标的SRID是什么，则可以使用ST_SetSRID()对几何图形进行SRID设置。
--然后，你将能把几何图形的现有坐标系统转换为其他坐标系统
SELECT ST_AsText(
    (ST_SetSRID(geom,26918),4326))FROM geometries;

--"基于UTM zone 18投影坐标系统的测量，纽约(New York)所有街道的长度是多少？"
SELECT Sum(ST_Length(geom)) FROM nyc_streets;

--"SRID 2831的WKT定义是什么？"
select srtext from spatial_ref_sys where srid = 2831;

--"基于SRID 2831坐标系统，计算纽约市所有街道的长度是多少？"
select sum(st_length(st_Transform(geom,2831))) from nyc_streets;
--注意：UTM 18与SRID 2831(the State Plane Long Island projection - 国家平面长岛投影)测量的差值为(10421993 - 10418904)/ 10418904 = 0.02%。
--利用地理法在地球球体上计算出的街道总长度为10421999，也就是说基于SRID 2831计算出来的结果和真实结果更接近。
--这并不奇怪，因为SRID 2831投影坐标系是精确地校准一个很小的区域(纽约市)，而UTM 18必须为一个大的区域提供合理的结果。

--" 'Broad St' 地铁站点的KML表示是什么？"
--嘿！结果坐标是地理坐标，而不是投影坐标，然而我们并没有调用ST_Transform()，为什么？
--因为KML标准规定所有坐标都必须是地理坐标(实际上是EPSG: 4326)，所以ST_AsKML()函数会自动进行坐标转换。
SELECT ST_AsKML(geom) FROM nyc_subway_stations WHERE name = 'Broad St';


--************************************************************PostGIS教程十三：地理************************************************************
--洛杉矶(Los Angeles)和巴黎(Paris)的坐标：
--Los Angeles: POINT(-118.4079 33.9434)
--Paris: POINT(2.3490 48.8533)
--使用标准的PostGIS笛卡尔平面坐标系空间函数ST_Distance(geometry, geometry)计算洛杉矶和巴黎之间的距离。请注意，SRID 4326声明了地理空间参考系统。
select ST_Distance(st_geomFromText('POINT(-118.4079 33.9434)',4326),st_geomFromText('POINT(2.3490 48.8533)',4326)) 
--啊哈！121！但那是什么意思？
--空间参考4326的单位是度，所以我们的答案是121度。但是，这表示什么呢？
--在地球球体上，1度对应的地球实际距离的大小是变化的。当远离赤道时，它会变得更小，当越接近两极时，地球上的经线相互之间越来越接近。因此，121度的距离并不意味着什么，这是一个没有意义的数字。
--为了计算出真实的距离，我们不能把地理坐标近似的看成笛卡尔平面坐标，而应该把它们看成是球坐标。我们必须把点之间的距离作为球面上的真实路径来测量——大圆的一部分。

-- 关于上面的测量应该使用geography而不是geometry类型，让我们再次尝试测量洛杉矶和巴黎之间的距离，我们将使用ST_GeographyFromText(text)函数，而不是ST_GeometryFromText(text)。
select ST_Distance(ST_GeographyFromText('POINT(-118.4079 33.9434)'),ST_GeographyFromText('POINT(2.3490 48.8533)')) / 1000

--当提出这样一个问题时，支持非点的几何图形的需求变得非常明显："从洛杉矶到巴黎的航班路线距离冰岛有多远?"
--在笛卡尔平面坐标系统上使用地理坐标(紫色线)产生了一个非常错误的答案！使用大圆路线(红线)则能得出正确的答案。
--如果我们将LAX-CDG航班路线转换成一条线串，并利用geography计算其到冰岛某个点的距离，我们可以得到正确的答案(以米为单位)。
select ST_Distance(ST_GeographyFromText('LineString(-118.4079 33.9434, 2.5559 49.0083)'),ST_GeographyFromText('POINT(-22.6056 63.9850)'))

-- 笛卡尔平面坐标系统处理地理坐标的方法是完全分解跨越国际日期变更线的要素。从洛杉矶到东京的最短大圆路线穿越太平洋，而最短的笛卡尔平面路线则穿越大西洋和印度洋。
SELECT ST_Distance(
    ST_GeometryFromText('Point(-118.4079 33.9434)'),  -- LAX
    ST_GeometryFromText('Point(139.733 35.567)'))     -- NRT (Tokyo/Narita)
    AS geometry_distance,
ST_Distance(
    ST_GeographyFromText('Point(-118.4079 33.9434)'), -- LAX
    ST_GeographyFromText('Point(139.733 35.567)'))    -- NRT (Tokyo/Narita)
    AS geography_distance;
    
--为了将geometry数据加载到geography表中，首先需要将geometry投影到EPSG:4326(经度-longitude/纬度-latitude)，然后再将其转换为geography。
--ST_Transform(geometry, srid)函数能将坐标转换为地理坐标，Geography(geometry)函数能将geometry转换为geography。
--创建新表nyc_subway_stations_geog，并转换数据。
create table nyc_subway_stations_geog as select Geography(ST_Transform(geom,4326)) as geog,name,routes from nyc_subway_stations;

--在geography表上构建空间索引与在geometry表上构建空间索引完全相同：
CREATE INDEX nyc_subway_stations_geog_gix ON nyc_subway_stations_geog USING GIST (geog);


-- 二、创建一个Geography表
--用于创建含有geography列的新表的SQL与用于创建geography表的SQL非常相似。但是，geography包含在表创建时直接指定表类型的功能。例如：
CREATE TABLE airports (
  code VARCHAR(3),
  geog GEOGRAPHY(Point)
);
--插入数据
INSERT INTO airports VALUES ('LAX', 'POINT(-118.4079 33.9434)');
INSERT INTO airports VALUES ('CDG', 'POINT(2.5559 49.0083)');
INSERT INTO airports VALUES ('KEF', 'POINT(-22.6056 63.9850)');

--在表定义中，GEOGRAPHY(Point)将airport数据类型指定为点。新的geography字段不会在geometry_columns视图中注册，相反，它们是在名为geography_columns视图中注册的。
SELECT * FROM geography_columns;

--三、转换为Geometry
--虽然geography类型的空间函数已经可以处理许多问题，但有时你可能需要访问仅由geometry类型支持的其他空间函数。幸运的是，你可以将对象从geography转换为geometry。
--PostgreSQL的类型转换语法是将::typename附加到希望转换的值的末尾。因此，2::text将数字2转换为文本字符串"2"；'POINT(0 0)' :: geometry将点的文本表示形式转换为geometry点。
--ST_X(point)函数仅支持geometry类型，那我们怎样才能从我们的geograph类型数据中读取X坐标呢？
select ST_X(geog::geometry),ST_Y(geog::geometry) from airports;
--通过将::geometry附加到geography值后面，可以将对象转换为SRID为4326的geometry。现在，我们就可以使用任何的geometry函数了。
--但是，请记住-现在我们的对象是geometry，坐标将被解释为笛卡尔平面坐标，而不是球体坐标。


--************************************************************PostGIS教程十四：几何图形创建函数************************************************************
--国公园管理局(US Park Service)想要在自由岛(Liberty Island)周围建立一个海洋交通区，他们可能会在该岛周围建造一个500米的缓冲多边形。
--自由岛是nyc_census_blocks表中的一个单独的人口普查块，因此我们可以轻松地提取和建立对应的缓冲区。
CREATE TABLE liberty_island_zone AS
select ST_Buffer(geom,500)::geometry(Polygon,26918) from nyc_census_blocks where blkid = '360610001001001';
--ST_Buffer函数也接受负的距离值，从而在输入的多边形内构建内接多边形。而对于线串和点，只会返回空值。

--ST_Intersection(geometry A, geometry B)函数返回两个参数共有的空间区域(或直线，或点)。如果参数不相交，该函数将返回一个空几何图形。
SELECT ST_Intersection(ST_Buffer('POINT(0 0)', 2),ST_Buffer('POINT(3 0)', 2));
select st_asText(ST_Intersection(ST_Buffer('POINT(0 0)', 2),ST_Buffer('POINT(3 0)', 2)));

-- 接受两个几何图形参数并返回合并的并集。例如，将上面示例中的ST_Intersection()函数替换为ST_Union()函数后，结果如下：
select ST_Union(ST_Buffer('POINT(0 0)',2),ST_Buffer('POINT(3 0)',2));
select ST_asText(ST_Union(ST_Buffer('POINT(0 0)',2),ST_Buffer('POINT(3 0)',2)));


--我们的nyc_census_blocks就是ST_Union的一个示例，人口普查地理是精心构建的，这样就可以从较小的地理区域建立起较大的地理区域。
--因为，我们可以通过合并构成每个区域的块来创建人口普查区域地图，或者我们可以通过合并每个县内的块来创建县地图。
--要执行合并，请注意，唯一的键blkid实际上包含了有关较高级别的地理区划的信息。以下是我们之前使用的自由岛的部分键：
-- 所以我们可以通过分组合并blkid键前5个数字相同的所有几何图形来创建县地图。要有耐心，这个计算代价比较大，可能需要一到两分钟。
--通过合并人口普查块创建一个nyc_census_counties表
CREATE TABLE nyc_census_counties AS
SELECT ST_Union(geom)::Geometry(MultiPolygon,26918) AS geom,SubStr(blkid,1,5) AS countyid FROM nyc_census_blocks GROUP BY countyid;

--面积测试可以确认我们的联合操作没有丢失任何几何图形。首先，我们计算每个人口普查区块(census block)的面积，并将这些区域按人口普查县(census county)id进行分组。
SELECT SubStr(blkid,1,5) AS countyid, Sum(ST_Area(geom)) AS area
FROM nyc_census_blocks
GROUP BY countyid;

--然后我们从nyc_census_counties表中计算出每个新生成的县多边形的面积:
select countyid,st_area(geom) as area from nyc_census_counties


--************************************************************PostGIS教程十五：更多的空间连接************************************************************
--创建人口普查区域表，根据blkid的前11个字符进行汇总分组
create table nyc_census_tract_geoms as
select ST_Union(geom) as geom, SubStr(blkid,1,11) as tractid from nyc_census_blocks group by tractid;

--创建索引
create index nyc_census_tract_geoms_tractid_idx on nyc_census_tract_geoms(tractid);

--使用标准属性连接将普查区域(census tract)几何图形表和普查区域属性表连接起来：
create table nyc_census_tracts as 
select a.*,g.geom from nyc_census_tract_geoms g join nyc_census_sociodata a on g.tractid = a.tractid;

--创建geom索引
create index nyc_census_tract_gidx on nyc_census_tracts using gist(geom);

--回答一个有趣的问题！"列出纽约拥有研究生学位的人所占比例排名前十的社区"。
SELECT
    n.name,n.boroname,100.00 * sum( t.edu_graduate_dipl ) / sum( t.edu_total ) AS graduate_pct 
FROM
    nyc_neighborhoods n
    JOIN nyc_census_tracts t ON ST_Intersects ( n.geom, t.geom ) 
WHERE
    t.edu_total > 0 
GROUP BY
    n.NAME,
    n.boroname 
ORDER BY
    graduate_pct DESC
limit 10;

--为了避免这种重复计算，有两种方法：
--简单的方法是确保每个区域只落在一个社区(使用ST_Centroid(geometry))
--复杂的方法是在两个社区的边界处将相交的人口普查区域(census tracts)分割(使用ST_Intersection(geometry, geometry))
--以下是在我们上面的研究生教育查询中使用简单方法避免人口普查区域重复计算的示例：
SELECT
  100.0 * Sum(t.edu_graduate_dipl) / Sum(t.edu_total) AS graduate_pct,
  n.name, n.boroname
FROM nyc_neighborhoods n
JOIN nyc_census_tracts t
ON ST_Contains(n.geom, ST_Centroid(t.geom))
WHERE t.edu_total > 0
GROUP BY n.name, n.boroname
ORDER BY graduate_pct DESC
LIMIT 10;

--纽约市的总人口：
select sum(popn_total) from nyc_census_blocks;

--纽约市距离地铁站周围500米范围内的人口：
--查询结果比纽约市的总人口还要多！显然，我们的简单SQL语句正在产生一个很大的重复计算的错误。你可以在地铁的缓冲区图片上看到这个问题。
select sum(b.popn_total) from nyc_subway_stations s join nyc_census_blocks b on ST_DWithin(s.geom,b.geom,500);

--解决方案是在将不同的人口普查块数据传递到查询操作之前，确保只有不同的普查数据块。我们可以通过将查询分解为查找不同普查块的子查询来实现这一点：
WITH distinct_blocks AS (
  SELECT DISTINCT ON (blkid) popn_total
  FROM nyc_census_blocks census
  JOIN nyc_subway_stations subway
  ON ST_DWithin(census.geom, subway.geom, 500)
)
SELECT Sum(popn_total) FROM distinct_blocks;


--************************************************************PostGIS教程十六：几何图形的有效性************************************************************
--让我们看看PostGIS数据库认为多边形的面积是多少：
SELECT ST_Area(ST_GeometryFromText('POLYGON((0 0, 0 1, 1 1, 2 1, 2 2, 1 2, 1 1, 1 0, 0 0))'));
-- 这里发生了什么？计算面积的算法假设环不自相交。程序始终计算位于边界线的一侧的区域的面积。
--然而，在我们的(表现不佳)的数字8中，对于一个叶部分，图形区域位于边界线的右侧，而对于另一个叶部分，图形区域在边界线的左侧。
--这将导致为每个叶部分计算的面积互相抵消(一个为1，另一个为-1)，因此结果为"0面积"。

--判断多边形是否无效
SELECT ST_IsValid(ST_GeometryFromText(
 'POLYGON((0 0, 0 1, 1 1, 2 1, 2 2, 1 2, 1 1, 1 0, 0 0))'
));

--现在我们知道这个图形是无效的，但是我们不知道为什么无效。我们可以使用ST_IsValidReason(geometry)函数来查找无效的原因：
SELECT ST_IsValidReason(ST_GeometryFromText('POLYGON((0 0, 0 1, 1 1, 2 1, 2 2, 1 2, 1 1, 1 0, 0 0))'));

--找到所有无效的多边形和他们的问题
SELECT gid,name, boroname, ST_IsValidReason(geom) FROM nyc_neighborhoods WHERE NOT ST_IsValid(geom);

--ST_MakeValid成功地将几何图形"8"转换为表示相同面积的multi-polygon。
SELECT ST_AsText(ST_MakeValid(
         'POLYGON((0 0, 0 1, 1 1, 2 1, 2 2, 1 2, 1 1, 1 0, 0 0))'
       ));

--************************************************************PostGIS教程十七：相等************************************************************
--创建表
CREATE TABLE polygons (id integer, name varchar, poly geometry);

--插入测试数据
INSERT INTO polygons VALUES
  (1, 'Polygon 1', 'POLYGON((-1 1.732,1 1.732,2 0,1 -1.732,
      -1 -1.732,-2 0,-1 1.732))'),
  (2, 'Polygon 2', 'POLYGON((-1 1.732,-2 0,-1 -1.732,1 -1.732,
      2 0,1 1.732,-1 1.732))'),
  (3, 'Polygon 3', 'POLYGON((1 -1.732,2 0,1 1.732,-1 1.732,
      -2 0,-1 -1.732,1 -1.732))'),
  (4, 'Polygon 4', 'POLYGON((-1 1.732,0 1.732, 1 1.732,1.5 0.866,
      2 0,1.5 -0.866,1 -1.732,0 -1.732,-1 -1.732,-1.5 -0.866,
      -2 0,-1.5 0.866,-1 1.732))'),
  (5, 'Polygon 5', 'POLYGON((-2 -1.732,2 -1.732,2 1.732,
      -2 1.732,-2 -1.732))');

--精确相等是通过按顺序逐个比较两个几何图形的顶点来确定的，以确保它们在位置上是相同的。下面的例子说明了这种方法的有效性是如何受到限制的。
SELECT
    a.id,
    a.NAME,
    b.NAME,
CASE
    WHEN ST_OrderingEquals ( a.poly, b.poly ) THEN
    'Exactly Equal' ELSE 'Not Exactly Equal' 
END 
FROM
    polygons AS a,
polygons AS b;

--正如我们在上面看到的，精确的相等并没有考虑到几何图形的空间性质。有一个名为ST_Equals的函数，可用于测试几何图形的空间相等性或等价性。
SELECT a.name, b.name, CASE WHEN ST_Equals(a.poly, b.poly)
    THEN 'Spatially Equal' ELSE 'Not Equal' end
  FROM polygons as a, polygons as b;

--在最坏的情况下，需要精确相等来比较几何图形中的每个顶点以确定相等。这可能会比较慢，并且可能不适合数量大的几何图形。
--为了更快地进行比较，提供了等边界运算符 ' = ' 。这仅在边界框(矩形)上操作，确保几何图形占用相同的二维范围，但不一定占用相同的空间。
SELECT a.name, b.name, CASE WHEN a.poly = b.poly
    THEN 'Equal Bounds' ELSE 'Non-equal Bounds' end
FROM polygons as a, polygons as b;


--************************************************************PostGIS教程十八：线性参考************************************************************
--这是在一条直线中间定位一个点的简单例子
SELECT ST_LineLocatePoint('LINESTRING(0 0, 2 2)', 'POINT(1 1)');

--如果这个点不在直线上呢?它投射到最近的点
--即做(0, 2)点到线串(0 0, 2 2)的垂线，使用对应的垂足点来求比例
SELECT ST_LineLocatePoint('LINESTRING(0 0, 2 2)', 'POINT(0 2)');


-- 下面所有的SQL都是用来创建新的事件表的
CREATE TABLE nyc_subway_station_events AS
-- 我们首先需要找到一组可能最接近的候选者
-- streets, 按id和distance排列...
WITH ordered_nearest AS (
SELECT
  ST_GeometryN(streets.geom,1) AS streets_geom,
  streets.gid AS streets_gid,
  subways.geom AS subways_geom,
  subways.gid AS subways_gid,
  ST_Distance(streets.geom, subways.geom) AS distance
FROM nyc_streets streets
  JOIN nyc_subway_stations subways
  ON ST_DWithin(streets.geom, subways.geom, 200)
ORDER BY subways_gid, distance ASC
)
-- 我们使用'distinct on'PostgreSQL功能为每各唯一的街道gid获取第一条街道(最近的街道)。
-- 然后，我们可以将这条街道信息置入ST_LinLocatePoint函数，使其沿着它的候选地铁站来计算
SELECT
  DISTINCT ON (subways_gid)
  subways_gid,
  streets_gid,
  ST_LineLocatePoint(streets_geom, subways_geom) AS measure,
  distance
FROM ordered_nearest;
 
-- 主码对于可视化软件很有用
ALTER TABLE nyc_subway_station_events ADD PRIMARY KEY (subways_gid);

--一旦我们有了一个事件表，将其转换回一个空间视图是很有趣的，这样我们就可以将事件相对于派生出它们的原始点进行可视化。
--要从线性参考比例值得到对应点，我们使用ST_LineInterpolatePoint函数，下面是关于我们前面的简单例子的逆过程：
--这是在一条直线中间定位一个点的简单例子
SELECT ST_AsText(ST_LineInterpolatePoint('LINESTRING(0 0, 2 2)', 0.5));


-- New view that turns events back into spatial objects
CREATE OR REPLACE VIEW nyc_subway_stations_lrs AS
SELECT
  events.subways_gid,
  ST_LineInterpolatePoint(ST_GeometryN(streets.geom, 1), events.measure)AS geom,
  events.streets_gid
FROM nyc_subway_station_events events
JOIN nyc_streets streets
ON (streets.gid = events.streets_gid);







select * from fc_offset;
--查询几何图形 空间参考标识码
select ST_SRID(geog) from fc_offset;

--106.45747169328875,29.50868037260556
--判断多边形是否无效
SELECT ST_IsValid(ST_GeometryFromText(
 'POLYGON((0 0, 0 1, 1 1, 2 1, 2 2, 1 2, 1 1, 1 0, 0 0))'
));

select ST_IsValid(geog) from fc_offset where gid = 1
select geog from fc_offset where gid = 1
select * from nyc_census_blocks;

select Geography(ST_Transform(geom,4326)) as geog,name,routes from nyc_subway_stations;

--地理数据转 text数据
select gid,ST_AsText(geog) from fc_offset;

select ST_GeographyFromText('MULTIPOLYGON(((106.457148122634 29.5082794202734,106.457608293331 29.5085698478909,106.457690290579 29.5084674746512,106.457228723494 29.5081807624807,106.457148122634 29.5082794202734)))')

--geog数据转GeoJson数据
select ST_AsGeoJson(geog) from fc_offset where gid = 1;

--106.45747169328875,29.50868037260556
--测试距离
select ST_Distance(ST_GeographyFromText('MULTIPOLYGON(((106.457148122634 29.5082794202734,106.457608293331 29.5085698478909,106.457690290579 29.5084674746512,106.457228723494 29.5081807624807,106.457148122634 29.5082794202734)))'),
                   ST_GeographyFromText('POINT(106.45747169328875 29.50868037260556)'))

--点是否在这个几何之内
select ST_DWithin(geog,ST_GeographyFromText('POINT(106.45747169328875 29.50868037260556)'), 10) from fc_offset; 


select * from (select *,ST_DWithin(ST_GeographyFromText('POINT(106.45747169328875 29.50868037260556)'),geog, 1) as mygeog from fc_offset ) as tem where tem.mygeog is true;


--****************************************************************************练习*****************************************************
SELECT ST_AsEWKT(ST_LineInterpolatePoint(foo.the_line, 2/5))
    FROM (SELECT ST_GeomFromEWKT('LINESTRING(25 50,100 125,150 190)') as the_line) As foo;

SELECT ST_LineInterpolatePoint(foo.the_line, 2/5)
    FROM (SELECT ST_GeomFromEWKT('LINESTRING(25 50,100 125,150 190)') as the_line) As foo;
    
SELECT ST_GeomFromEWKT('LINESTRING(-1040142.1845233463 4907939.55236595,-1040218.7010890653 4907952.22774352,-1040277.0026652259 4907954.396334826)')

SELECT ST_GeomFromEWKT('LINESTRING(101.96572896316258 38.24841049254045,101.96651842698866 38.24802356506224,101.96745710305008 38.247721410173256,101.96906494126048 38.2471815507374,101.96924226105543 38.24688287445684, 101.9692922311656 38.24662037217719,101.9684517716621 38.24510700896156, 101.9684517716621 38.24510700896156,101.96819399954808 38.24505005141092,101.9661793987408 38.245760494193334,101.96628009602408 38.24626818215956)')


SELECT ST_AsEWKT(ST_LineInterpolatePoint(the_line, 0.20))
    FROM (SELECT ST_GeomFromEWKT('LINESTRING(25 50, 100 125, 150 190)') as the_line) As foo;
   

--****************************************************************************多边形练习*****************************************************
--查询当前点是否在多边形内
select ST_DWithin(geog,ST_GeographyFromText('POINT(106.45747169328875 29.50868037260556)'), 10) from fc_offset; 

--geog数据转GeoJson数据
select ST_AsGeoJson(geog) from fc_offset where gid = 1;

{"type":"MultiPolygon","coordinates":[[[[106.457148123,29.50827942],[106.457608293,29.508569848],[106.457690291,29.508467475],[106.457228723,29.508180762],[106.457148123,29.50827942]]]]}

--查询数据库中所有空间数据表的描述信息
SELECT * FROM geometry_columns;

--创建一个表，可以同时保存Polygon和MultiPolygon的字段
CREATE table geometry_table(
  gid serial primary key,
  geom geometry(geometry,4326)
);

--插入多边形数据
INSERT INTO geometry_table(geom) VALUES ('SRID=4326;MULTIPOLYGON(((0 0, 1 0, 1 1, 0 1, 0 0)))');

--查看刚刚添加的要素
SELECT st_asgeojson(geom) AS geom FROM geometry_table;
select ST_AsText(geom) as geom from geometry_table;
参考：https://blog.csdn.net/xujingzhong0077/article/details/90168109