423,851 Members | 1,072 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,851 IT Pros & Developers. It's quick & easy.

Could someone check my Postgresql spatial queries

P: 1
Could someone check my Postgresql queries? I wrote the explanation first and then the query.

# 1 list all sushi bars within borders of Cracow city
Expand|Select|Wrap|Line Numbers
  1. select name from planet_osm_point where amenity='restaurant' and name ilike '%sushi%' and ST_Within(way, (select multipolygon from krakow limit 1));
# 2 calculate the total length of aphalt roads in Cracow
Expand|Select|Wrap|Line Numbers
  1. select sum(st_length(way)) from planet_osm_roads where surface='asphalt' and ST_Within(way, (select multipolygon from krakow limit 1));
#3 count all carpet beaters in Cracow
Expand|Select|Wrap|Line Numbers
  1. select count(*) from planet_osm_point  where amenity='beater' and ST_Within(way, (select multipolygon from krakow limit 1));
#4 find distance between Wydział Matematyki i Informatyki UJ and the restaurant Miód i Malina
Expand|Select|Wrap|Line Numbers
  1. select ST_distance((select way from planet_osm_point where amenity = 'university' and name ilike '%Wydział Matematyki i Informatyki%'), (select way from planet_osm_point where amenity = 'restaurant' and name ilike '%malina%'));
# find how many amenities are in the radius of 10 km from the restaurant "miód i malina"
Expand|Select|Wrap|Line Numbers
  1. select amenity, count(*) from planet_osm_point where ST_distance((select way from planet_osm_point where name ilike '%malina%' and amenity = 'restaurant'), way) <= 10000 group by amenity order by 2 desc;
#find longest primary roads in Kraków
Expand|Select|Wrap|Line Numbers
  1. select name, sum(ST_length(way)) from planet_osm_roads where highway = 'primary' and name is not null and name not ilike '%powiat%' and name not ilike '%województwo%' and name not ilike '%subregion%' and name not ilike '%Kraków%' and ST_within(way, (select multipolygon from krakow limit 1))  group by name order by 2 desc limit 10;
#find longest only pedestrian road in Kraków
Expand|Select|Wrap|Line Numbers
  1. select name, sum(ST_length(way)) from planet_osm_roads where highway = 'pedestrian' and name is not null and name not ilike '%powiat%' and name not ilike '%województwo%' and name not ilike '%subregion%' and name not ilike '%Kraków%' and ST_within(way, (select multipolygon from krakow limit 1))  group by name order by 2 desc limit 1;
#select amenities which are at most 10 km from restaurant Miód i Malina and at most 5 km from "Szara Ges", group them by their type and count them
Expand|Select|Wrap|Line Numbers
  1. select amenity, count(*) from planet_osm_point where (ST_distance((select way from planet_osm_point where name ilike '%malina%' and amenity = 'restaurant'), way) <= 10000 and st_distance((select way from planet_osm_point where name ilike '%szara ges%' and amenity = 'restaurant'), way) <= 5000) group by amenity order by 2 desc;
#select theatres which are not further than 50 km from Sukiennice (Rynek Główny) but they lie outside Krakow and give distance
Expand|Select|Wrap|Line Numbers
  1. select name, ST_distance(way, (select way from planet_osm_point where name ilike '%muzeum sukiennice%')) from planet_osm_point where amenity = 'theatre' and st_distance(way, (select way from planet_osm_point where name ilike '%muzeum sukiennice%')) <500000 and not ST_Within(way, (select multipolygon from krakow limit 1));
#find all the castles from the map and their distance from the Krakow city center
Expand|Select|Wrap|Line Numbers
  1. select a.name, ST_distance(ST_setSRID(a.way, 4326), ST_GeomFromText('POINT(19.938333 50.061389)', 4326)) from planet_osm_polygon as a where historic ilike '%castle%'order by 1;
#calculate areas of different lakes that can be found in the map
Expand|Select|Wrap|Line Numbers
  1. select name, sum(st_area(way)) from planet_osm_polygon where water = 'lake'and name is not null group by name  order by 1;
#calculate the area of different lakes within Cracow boundaries.
Expand|Select|Wrap|Line Numbers
  1. select name, sum(st_area(way)) from planet_osm_polygon where water = 'lake'and name is not null and ST_Within(way, (select multipolygon from krakow limit 1))  group by name  order by 1;
#calculate total area of forests within Cracow boundaries
Expand|Select|Wrap|Line Numbers
  1. select sum(st_area(way)) from planet_osm_polygon where (wood ilike '%coniferous%') or (wood ilike 'mixed') or (wood ilike '%evergreen%') or (wood ilike '%deciduous%')  and ST_Within(way, (select multipolygon from krakow limit 1));
# Number of intersections between asphalt roads which are shorter than 100m.
Expand|Select|Wrap|Line Numbers
  1. select count(*) from planet_osm_roads as a, planet_osm_roads as b where a.surface = 'asphalt' and b.surface='asphalt' and a.osm_id != b.osm_id and st_length(b.way) < 100 and st_length(a.way) < 100 and st_crosses(a.way,b.way);
Aug 2 '18 #1
Share this Question
Share on Google+
1 Reply


zmbd
Expert Mod 5K+
P: 5,285
Picia,
These read like homework questions and as such will not receive a lot of response - no one here will typically re-write a homework answer.

If you have a particular problem with one of these, given that you have shown your work, you may receive some direction if you can tell us what the issue is; however, we will not do your work for you - it's not fair to you and it's not fair to your fellow students.

Finally, ALL sql, script, VBA, Code, etc... must be formatted using either the [CODE/] format tool or manually enclosed between [code] [/code] tags.
Aug 6 '18 #2

Post your reply

Sign in to post your reply or Sign up for a free account.