spatial_queries.sql

-- Locations
create table locations (
  id int not null auto_increment primary key,
  feature_id int not null,
  state_id int not null,
  place_id int not null,
  type varchar(255) not null,
  name varchar(255) not null,
  county_name varchar(255),
  latlng point NOT NULL,
  SPATIAL INDEX(latlng)
) ENGINE=MYISAM;

insert into locations(feature_id, state_id, place_id, type, name, county_name, latlng)
select
  feature_id,
  s.id,
  FIPS_place_cd,
  feat_class,
  srt_name,
  county_name,
  POINT(Primary_lat, Primary_lon)
from fips55 as f
inner join states as s on f.FIPS_st_cd = s.fip_id;

select * from locations
where
  MBRContains(
    LineString(
      POINT(
        38.4087993 + 15 / 111.1, 
        -121.3716178 + 15 / (111.1 / COS(RADIANS(38.4087993)))
      ),
      POINT(
        38.4087993 - 15 / 111.1, 
        -121.3716178 - 15 / (111.1 / COS(RADIANS(38.4087993)))
      )
    ),
    latlng
  );