title: Designed PostGIS Database...Points table and polygon tables...How to make more efficient?
body: <p>This is a conceptual question, but I should have asked it long ago on this forum.  </p>
  
  <p>I have a PostGIS database, and I have many tables in it.  I have researched some on the use of keys in databases, but I'm not sure how to incorporate keys in the case of the point data that is dynamic and increases with time.  </p>
  
  <p>I'm storing point data in one table, and this data grows each day.  It's about 10 million rows right now and will probably grow about 10 million rows each year or so.  There are lat, lon, time, and the_geom columns.</p>
  
  <p>I have several other tables, each representing different polygon groups (converted shapefiles to tables with shp2pgsql), like counties, states, etc.  </p>
  
  <p>I'm writing queries that relate the point data to the spatial tables to see if points are inside of the polygons, resulting in things like "55 points in X polygon in the past 24 hours", etc.</p>
  
  <p>The problem is, I don't have a key that relates the point table to the other tables.  I think this is probably inhibiting query efficiency, but I'm not sure.</p>
  
  <p>I know this question is fairly vague, and I'm happy to clarify anything, but I basically have a bunch of points in a table that I'm spatially comparing to other tables, and I'm trying to find the best way to design things.</p>
  
  <p>Thanks for any help!</p>