所有道路的总长度是多少（以公里为单位）？先设置空间坐标系，然后利用投影坐标系转换
select UpdateGeometrySRID('road_table', 'osm_geom', 4326);
SELECT sum(ST_Length(ST_Transform(osm_geom,2436)))/1000 AS km_roads from road_table;
空间查询更有效
SELECT geom as result_out FROM node_table WHERE ST_DWithin(geom, 'POINT(116.3211279 39.984223)', 0.002);
操作查询结果
SELECT ST_DWithin(geom, 'SRID=0;POINT(116.3211279 39.984223)', 0.0015) as result_bool FROM node_table WHERE ST_DWithin(geom, 'SRID=0;POINT(116.3211279 39.984223)', 0.002);
多条件查询—空间查询
SELECT geom as result_bool FROM node_table WHERE ST_DWithin(geom, 'SRID=0;POINT(116.3211279 39.984223)', 0.002) and ST_DWithin(geom, ' SRID=0;POINT(116.3211279 39.984223)', 0.0015);
多条件查询--时间范围查询
SELECT geom FROM node_table where tstamp < '2021-03-15 21:45:46' and '2021-03-15 21:45:43' < tstamp;
多条件查询—时空查询
select
ST_DWithin(time_result.geom, 'SRID=0;POINT(116.3211279 39.984223)', 0.002) as result_bool
from (
select
node_id, name, geom
from node_table
WHERE node_table.tstamp < '2021-03-15 21:45:46' and '2021-03-15 21:45:43' < node_table.tstamp
) as time_result;
多条件查询—时空查询--最佳 给定经纬度+度数阈值（存储默时没社坐标系，即没有SRID）
select
node_id, name, geom
from node_table
WHERE node_table.tstamp < '2021-03-15 21:45:46' and '2021-03-15 21:45:43' < node_table.tstamp and ST_DWithin(node_table.geom, 'SRID=0;POINT(116.3211279 39.984223)', 0.0002)
多条件查询—时空查询--最佳 给定经纬度+距离阈值
select UpdateGeometrySRID('node_table', 'geom', 4326);
select
node_id, name, geom
from node_table
WHERE node_table.tstamp < '2021-03-15 21:45:46' and '2021-03-15 21:45:43' < node_table.tstamp
and ST_DWithin(ST_Transform(node_table.geom,2436), ST_Transform(ST_GeomFromEWKT('SRID=4326;POINT(116.3211279 39.984223)'),2436), 265.5);
用到了坐标转换ST_Transform
ST_Transform(ST_GeomFromEWKT('SRID=4326;POINT(116.3211279 39.984223)'),2436)
ST_Transform(geom,2436)
空间查询--给定经纬度+距离阈值
select
node_id, name, geom
from node_table
WHERE ST_DWithin(ST_Transform(node_table.geom,2436), ST_Transform(ST_GeomFromEWKT('SRID=4326;POINT(116.3211279 39.984223)'),2436), 365.5);
坐标系SRID1为：2249（投影坐标系）；坐标系SRID2为：4326（地理坐标系）。