Could someone check my Postgresql spatial queries

[Picia]

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

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

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

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

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"

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

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

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

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

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

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

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.

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

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.

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);

[zmbd]

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.