给我十条空间查询的中文语句与对应postgreSQL数据库语句SQL语句
时间: 2024-01-16 12:04:19 浏览: 48
好的,以下是十条空间查询的中文语句与对应的postgreSQL数据库SQL语句:
1. 查找指定区域内所有的设施:SELECT * FROM facilities WHERE ST_Intersects(geom,ST_GeomFromText('POLYGON((lon1 lat1,lon2 lat2,lon3 lat3,lon4 lat4,lon1 lat1))',4326));
2. 查找与指定点最近的5个商店:SELECT * FROM stores ORDER BY ST_Distance(geom,ST_GeomFromText('POINT(lon lat)',4326)) LIMIT 5;
3. 查找所有位于指定行政区域内的房屋:SELECT * FROM houses WHERE ST_Within(geom, (SELECT geom FROM areas WHERE name = '行政区域名称'));
4. 查找距离指定点不超过500米的所有社区服务设施:SELECT * FROM community_facilities WHERE ST_DWithin(geom,ST_GeomFromText('POINT(lon lat)',4326),500);
5. 查找所有距离指定路线不超过500米的公园:SELECT * FROM parks WHERE ST_DWithin(geom, (SELECT ST_Collect(geom) FROM roads WHERE road_name = '路线名称'),500);
6. 查找所有距离指定建筑物超过200米的古迹:SELECT * FROM historic_sites WHERE ST_Distance(geom,(SELECT geom FROM buildings WHERE building_name = '建筑物名称'))>200;
7. 查找所有位置在指定自然保护区内的动物:SELECT * FROM animals WHERE ST_Within(geom, (SELECT geom FROM nature_reserve WHERE name = '自然保护区名称'));
8. 查找所有距离指定城市中心不超过1000米的酒店:SELECT * FROM hotels WHERE ST_Distance(geom,(SELECT geom FROM city_centers WHERE city_name = '城市名称'))<1000;
9. 查找所有距离指定河流不超过200米的钓鱼点:SELECT * FROM fishing_spots WHERE ST_DWithin(geom, (SELECT geom FROM rivers WHERE river_name = '河流名称'),200);
10. 查找所有距离指定机场跑道不超过300米的航空公司办公室:SELECT * FROM airlines_offices WHERE ST_DWithin(geom, (SELECT ST_Collect(runway_geom) FROM airports WHERE airport_name = '机场名称'),300);
希望能够帮助到你。如果还有其他问题,可以随时问我。