Need Help Importing a SHP file to a db table.

[Tom Cocca]

I am trying to get shapefile data into a PostGIS enabled DB but I can not figure out how to get the geom column data into the table.

I have created a table with the following:

class CreateStates < ActiveRecord::Migration

  def change

    create_table :states do |t|

      t.column "geo_type", :string, :limit => 2

      t.column "ob_geo_id", :string, :limit => 16

      t.column "geo_id", :string, :limit => 14

      t.column "geo_name", :string, :limit => 75

      t.column "short_name", :string, :limit => 70

      t.column "state2", :string, :limit => 2

      t.column "county3", :string, :limit => 3

      t.column "county5", :string, :limit => 5

      t.column "latitude", :float

      t.column "longitude", :float

      t.column "aland", :bigint

      t.column "awater", :bigint

      t.column "area_mi", :float

      t.column "aland_mi", :float

      t.column "vintage", :string, :limit => 6

      t.multi_polygon :geom, :srid => 4326

      t.timestamps

    end

    change_table :states do |t|

      t.index :geom, :spatial => true

    end

  end

end

and I have a State model:

class State < ActiveRecord::Base

  set_rgeo_factory_for_column(:geom, RGeo::Geographic.spherical_factory(:srid => 4326))

end

Now when I am trying to read a shp file and import the records into the database with the following it never writing anything to the geom column:

RGeo::Shapefile::Reader.open('state_sample.shp') do |file|

  puts "File contains #{file.num_records} records."

  puts "File shape type: #{file.shape_type_code}"

  file.each do |record|

    puts "  Attributes: #{record.attributes.inspect}"

    state = State.where(:geo_id => record['GEO_ID']).first

    if state.blank?

      state = State.new

      state.geo_id = record['GEO_ID']

    end

    state.geo_type = record['GEO_TYPE']

    state.ob_geo_id = record['OB_GEO_ID']

    state.geo_name = record['GEO_NAME']

    state.short_name = record['SHORT_NAME']

    state.state2 = record['STATE2']

    state.county3 = record['COUNTY3']

    state.county5 = record['COUNTY5']

    state.latitude = record['LATITUDE']

    state.longitude = record['LONGITUDE']

    state.aland = record['ALAND']

    state.awater = record['AWATER']

    state.area_mi = record['AREA_MI']

    state.aland_mi = record['ALAND_MI']

    state.vintage = record['VINTAGE']

    state.geom = record.geometry

    state.save

  end

end

It writes all the other columns but nothing gets written to the geom column.

I know the shapefiles are SRID 4326.

What am I missing. Also, it is very slow (I'm assuming its the RGeo::Geographic.spherical_factory(:srid => 4326) doing some conversion on the geom and because its a state shape it has a lot of sides and thus is slow).  Basically I want some way to be able to continually update when I get new files so I don't want to just use shp2pgsql.

The eventual goal is to be able to query on lat/longs to find the states (or counties/cities/zip codes) that contain that point.  Also, I want to be able to put the shapes onto a google map.

Thanks,

~ Tom