Spatial Query Tutorial

  • Find all objects with 5 unit distance of POINT(-128 49.948) (Use both table)
  SELECT id FROM postgis_your_netid  
  WHERE ST_DWithin(geometry, GeomFromText('POINT(-128 49.948)',4326), 5.0);
  SELECT state_name FROM postgis_states_your_netid  
  WHERE ST_DWithin(the_geom, GeomFromText('POINT(-128 49.948)',4326), 5.0);
  • Sort the states by Area (Use postgis_states_your_netid table)
  select state_name, ST_Area(the_geom) as area from postgis_states_your_netid ORDER BY area desc;
  • Find centroid of all states (Use postgis_states_your_netid table)
  select state_name, ST_AsText(ST_Centroid(the_geom)) from postgis_states_your_netid;
  • Find all the states that border Illinois (Use postgis_states_your_netid table)
  SELECT states.state_name FROM 
  postgis_states_your_netid AS states, postgis_states_your_netid AS il 
  WHERE il.state_name like 'Illinois' AND ST_Touches(il.the_geom,states.the_geom);
  • Try other states as well.
  • Try coming up with your own spatial queries
    • Examples of more complex queries can be found here

References